0

Below is the teradata code i want to convert to snowflake

    select XMLAGG (XMLELEMENT (n, col1
    || CHR (10))
   ORDER BY num
  ).EXTRACT ('//text()').getClobVal () AS abc

tried as below but not working. am new to both oracle and snowflake please help me with this

extract(listagg (parse_xml('<n> col1||  CHR (10) <n>')),'//text()')
Kyle
  • 63
  • 7
  • Can you share the relevant snippet(s) of the XML document and the intended output? – Greg Pavlik Dec 09 '22 at 06:38
  • @GregPavlik, Im not quite sure. i just want alternative for xmlagg,xmlelement and getclobval in snowflake – Kyle Dec 09 '22 at 06:58
  • There are a few XML functions, parse_xml will convert a string to a variant in XML. What you probably will need to use is xmlget: https://docs.snowflake.com/en/sql-reference/functions/xmlget.html This shows how to use it on hierarchical XML: https://community.snowflake.com/s/article/HOW-TO-QUERY-NESTED-XML-DATA-IN-SNOWFLAKE – Greg Pavlik Dec 09 '22 at 07:10
  • @Kyle instead of sharing the Teradata code, it would be better to share a sample XML and the expected result. – Gokhan Atil Dec 09 '22 at 08:10

1 Answers1

1

Almost certainly your Teradata code is simply a polyfill (aka a work around) for the historic lack of LISTAGG in Teradata.

Likely you simply need this code

SELECT
  LISTAGG(col1,'\n') WITHIN GROUP (ORDER BY num)
Paul Vernon
  • 3,818
  • 1
  • 10
  • 23
  • If you have a lot of this kind of code to migrate, it is worth considering using tooling to help automate SQL code conversions https://medium.com/@paulgvernon/why-is-sql-so-incompatible-edae60831a3 – Paul Vernon Dec 09 '22 at 10:32
  • LISTAGG( to_xml(object_construct('',load_name )) ,'\n') this is providing the output but extract and getclobval is not applied. any idea on how to get that functionality in snowflake @paulvernon – Kyle Dec 09 '22 at 15:40
  • I'm pretty sure you don't need whatever extract and getclobval are doing because they are all part of the polyfill in Teradata - they are all replaced by the simple LISTAGG in Snowflake. – Paul Vernon Dec 09 '22 at 15:49