XMLAGG

Translation specification for transforming the XMLAGG function to Snowflake LISTAGG

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.