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
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
CREATE 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
select
xmlagg(o_orderkey order by o_totalprice desc) (varchar(10000))
from orders
where o_totalprice > 5;
Snowflake
SELECT
LEFT(TO_VARCHAR(LISTAGG ( o_orderkey, ' ')
WITHIN GROUP(
order by o_totalprice DESC NULLS LAST)), 10000)
from
orders
where o_totalprice > 5;
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.
Last updated