Is there an equivalent of SQL "FOR XML" in Snowflake?

Question

I have a SQL query that combines multiple results from a table into a single row, ordered list result set.

TableA

Col1
ABC
DEF

select * from TableA for xml raw(''), root('ol'), elements, type

Output:

<ol><li>ABC</li><li>DEF</li></ol>

Would like to achieve the same result in Snowflake

Answer 1

There's no built-in XML constructor in Snowflake, but for simple XML formats you can use listagg and concatenation to produce the XML:

create or replace temp table T1(COL1 string);
insert into T1 (COL1) values ('ABC'), ('DEF');

select '<ol><li>' || listagg(COL1, '</li><li>')   || '</li></ol>' from T1;