We are receiving XML files in our Snowflake-Database. There is a tag that can occur multiple times or no times. We would like to extract this tag as XML (or better convert to JSON) as a Variant while passing the rest into a tabular format.

Problem: I am not able to find out, how to select all tags of a type, instead of just one.

XML Example:

<container>
   <contentOne>Here is a 1</contentOne>
   <contentTwo>Here is a 2</contentOne>
   <contentDetail>D1</contentDetail>
   <contentDetail>D2</contentDetail>
   <contentDetail>D3</contentDetail>   
</container>

I would like the following row in my table:

How would you approach this issue?

CodePudding user response：

Can you try this one?

with xmldata as (
select 1 record_no, parse_xml( '<container>
   <contentOne>Here is a 1</contentOne>
   <contentTwo>Here is a 2</contentTwo>
   <contentDetail>D1</contentDetail>
   <contentDetail>D2</contentDetail>
   <contentDetail>D3</contentDetail>   
</container>') xml
    )
select XMLGET( xml,'contentOne'):"$"::STRING contentOne,
XMLGET( xml,'contentTwo'):"$"::STRING contentTwo,
LISTAGG( details.value::STRING, '' ) contentDetails
from xmldata,
lateral FLATTEN( xml:"$" ) details 
WHERE GET( details.value, '@' ) = 'contentDetail'
GROUP BY record_no, 1, 2;

 ------------- ------------- ----------------------------------------------------------------------------------------------------- 
| CONTENTONE  | CONTENTTWO  |                                           CONTENTDETAILS                                            |
 ------------- ------------- ----------------------------------------------------------------------------------------------------- 
| Here is a 1 | Here is a 2 | <contentDetail>D1</contentDetail><contentDetail>D2</contentDetail><contentDetail>D3</contentDetail> |
 ------------- ------------- -----------------------------------------------------------------------------------------------------