HOW TO QUERY NESTED XML DATA IN SNOWFLAKE separated by commas-CodePudding

I want to remove all the tags and replace them with commas. I attempted to write some of the queries but had no success. Below is the sample data I shared and the output I need.

Here is the sample data :-

<TAGS>
  <TAG>
    <TAG_ID>19</TAG_ID>
    <NAME>Ct</NAME>
  </TAG>
  <TAG>
    <TAG_ID>104</TAG_ID>
    <NAME>Ntt</NAME>
  </TAG>
</TAGS>

Needed Output

19,Ct,104,Ntt

CodePudding user response：

Can you try this one?

select LISTAGG( f.VALUE,',')
from values 
('<TAGS>
  <TAG>
    <TAG_ID>19</TAG_ID>
    <NAME>Ct</NAME>
  </TAG>
  <TAG>
    <TAG_ID>104</TAG_ID>
    <NAME>Ntt</NAME>
  </TAG>
</TAGS>') tmp(mydata),
lateral flatten( parse_xml(mydata):"$",  RECURSIVE => TRUE ) f
WHERE f.KEY = '$' and NOT IS_ARRAY(f.VALUE);