XMLAGG

Translation specification for transforming the XMLAGG function to Snowflake LISTAGG

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Description

Construct an XML value by performing an aggregation of multiple rows. For more information check XMLAGG.

XMLAGG (
  XML_value_expr
  [ ORDER BY order_by_spec [,...] ]
  [ RETURNING { CONTENT | SEQUENCE } ]
)

order_by_spec := sort_key [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

Sample Source Patterns

Setup data

Teradata

Query

IN -> Teradata_01.sql

create table orders (
	o_orderkey int, 
	o_totalprice float);

insert into orders values (1,500000);
insert into orders values (2,100000);
insert into orders values (3,600000);
insert into orders values (4,700000);

Snowflake

Query

OUT -> Teradata_01.sql

CREATE OR REPLACE TABLE orders (
	o_orderkey int,
	o_totalprice float)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"teradata"}}'
;

INSERT INTO orders
VALUES (1,500000);

INSERT INTO orders
VALUES (2,100000);

INSERT INTO orders
VALUES (3,600000);

INSERT INTO orders
VALUES (4,700000);

XMLAGG transformation

Teradata

Query

IN -> Teradata_02.sql

select 
    xmlagg(o_orderkey order by o_totalprice desc) (varchar(10000))
from orders
where o_totalprice > 5;

Result

COLUMN1 |
--------+
4 3 1 2 |

Snowflake

Query

OUT -> Teradata_02.sql

SELECT
    LEFT(TO_VARCHAR(LISTAGG ( o_orderkey, ' ')
    WITHIN GROUP(
 order by o_totalprice DESC NULLS LAST)), 10000)
    from
    orders
    where o_totalprice > 5;

Result

COLUMN1 |
--------+
4 3 1 2 |

Known Issues

1. The RETURNING clause is currently not supported.

The user will be warned that the translation of the returning clause will be added in the future.

Related EWIs

No related EWIs.