Parsing of XML with recursive hierarchy in Snowflake

Question

I am trying to parse the below XML that has recursive hierarchal structure. I am able to loop through only once, the second survey data never gets populated. Also, I am getting NULL values in the columns



    1
    ABC
    
      
        Do you drink?
        Yes
      
    
    
      
        Do you Smoke?
        Yes
      
    


    2
    DEF
    
      
        Do you drink?
        No

Query used:

SELECT 
GET(XMLGET(XMLGET(TEST_XML_1, 'SURVEYDATA'),'SURVEY_ID'), '$') AS SURVEY_ID,
GET(XMLGET(D.VALUE, 'QUESTION'), '$') AS QUESTION,
GET(XMLGET(D.VALUE, 'ANSWER'), '$') AS ANSWER
FROM DATA,
LATERAL FLATTEN (GET(XMLGET(TEST_XML_1, 'SURVEYDATA', 0), '$'))D;

The output that I am getting is:

SURVEY_ID	QUESTION	ANSWER
1	NULL	NULL
1	NULL	NULL
1	NULL	NULL
1	NULL	NULL

The output that I am expecting is:

SURVEY_ID	QUESTION	ANSWER
1	Do you drink?	Yes
1	Do you smoke?	Yes
2	Do you drink?	No

user_43992 · Answer

So seems you want to now loop across the object in DATA_EXPORT you need to get the object of that, which GET(xml, '$') will give you, thus the following will give you the two row of SURVEYDATA

SELECT q.*
FROM TEST_XML,
  LATERAL FLATTEN(GET(src_xml, '$')) q;

given you will want the survey_id and cleint_id lets pull those plus the nested comments out now, so we can see we are getting the data we want:

SELECT 
    get(XMLGET(q.value, 'SURVEY_ID'), '$') as survey_id
    ,get(XMLGET(q.value, 'CLIENT_ID'), '$') as client_id
    ,XMLGET(q.value, 'COMMENTS') as comments
FROM TEST_XML,
  LATERAL FLATTEN(GET(src_xml, '$')) q;

but we notice this only has one comment, so will need to loop not across the coments, but actually across the objects of SURVEYDATA but only keep the comments:

SELECT 
    get(XMLGET(q.value, 'SURVEY_ID'), '$') as survey_id
    ,get(XMLGET(q.value, 'CLIENT_ID'), '$') as client_id
    ,XMLGET(q.value, 'COMMENTS') as comments
    ,get(q.value, '$')
    ,c.*
FROM TEST_XML,
  LATERAL FLATTEN(GET(src_xml, '$')) q,
  LATERAL FLATTEN(get(q.value, '$')) c
WHERE get(c.value, '@')='COMMENTS'

now we can unpack the comments values we want:

SELECT 
    get(XMLGET(q.value, 'SURVEY_ID'), '$') as survey_id
    ,get(XMLGET(q.value, 'CLIENT_ID'), '$') as client_id
    ,c.value
    ,XMLGET(c.value, 'RESPONSE') as resp
    ,get(XMLGET(resp, 'QUESTION'), '$') as question
    ,get(XMLGET(resp, 'ANSWER'), '$' ) as answer
FROM TEST_XML,
  LATERAL FLATTEN(GET(src_xml, '$')) q,
  LATERAL FLATTEN(get(q.value, '$')) c
WHERE get(c.value, '@')='COMMENTS'

so now we can see we have all the values we want we can compress the SQL up a little so it doesn't have the intermediate values we used to help us work through the problem.

giving the final SQL, include data in a CTE to help test with:

with TEST_XML as (
  select parse_xml('
  
    1
     ABC
     
       
         Do you drink?
         Yes
       
     
     
       
         Do you Smoke?
         Yes
       
     
   
   
     2
     DEF
     
       
         Do you drink?
         No
       
     
   
 ') as SRC_XML
  )
SELECT 
    get(XMLGET(q.value, 'SURVEY_ID'), '$') as survey_id
    ,get(XMLGET(q.value, 'CLIENT_ID'), '$') as client_id
    ,get(XMLGET(XMLGET(c.value, 'RESPONSE'), 'QUESTION'), '$') as question
    ,get(XMLGET(XMLGET(c.value, 'RESPONSE'), 'ANSWER'), '$' ) as answer
FROM TEST_XML,
  LATERAL FLATTEN(GET(src_xml, '$')) q,
  LATERAL FLATTEN(get(q.value, '$')) c
WHERE get(c.value, '@')='COMMENTS'

which gives the results:

SURVEY_ID   CLIENT_ID   QUESTION        ANSWER
1           "ABC"       "Do you drink?" "Yes"
1           "ABC"       "Do you Smoke?" "Yes"
2           "DEF"       "Do you drink?" "No"

Parsing of XML with recursive hierarchy in Snowflake

1 Answers