Snowflake: Extracting multiple XML tags of same type "together" into one cell (as XML-Vari-CodePudding

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:

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

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> |
 ------------- ------------- -----------------------------------------------------------------------------------------------------