CODEWITHSUNDEEP
xmlsnowflake-cloud-data-platform
vvazza
vvazza

Reputation: 397

Extracting XML data in Snowflake

I have a table TEST_XML that has a variant column SRC_XML that contains the following XML. When I try to extract QUESTION_TEXT and ANSWER_TEXT, am getting NULL as result, even though the XML has values in corresponding fields

<DATA_EXPORT>
  <HEADER>
    <ENTRY_DATE>
      <START>2021-04-02</START>
      <END>2021-04-02</END>
    </ENTRY_DATE>
    <QUESTION_COUNT>225</QUESTION_COUNT>
    <SURVEY_QUESTIONS>
      <QUESTION>
        <NUMBER>1</NUMBER>
        <QUESTION_TEXT>DO YOU TRAVEL?</QUESTION_TEXT>
        <ANSWER_TEXT>NO</ANSWER_TEXT>
      </QUESTION>
      <QUESTION>
        <NUMBER>2</NUMBER>
        <QUESTION_TEXT>HOW MANY LANGUAGES DO YOU SPEAK?</QUESTION_TEXT>
        <ANSWER_TEXT>6</ANSWER_TEXT>
      </QUESTION>
    </SURVEY_QUESTIONS>
  </HEADER>
</DATA_EXPORT>

The query that I used for extracting the data -

SELECT 
  XMLGET(D.VALUE, 'QUESTION') AS Q
, GET(XMLGET(Q, 'NUMBER'), '$') AS QUESTION_NUMBER
, GET(XMLGET(Q, 'QUESTION_TEXT'), '$') AS QUESTION
, GET(XMLGET(Q, 'ANSWER_TEXT'), '$') AS ANSWER
FROM TEST_XML,
LATERAL FLATTEN(GET(XMLGET(XMLGET(SRC_XML, 'HEADER', 0),'SURVEY_QUESTIONS'), '$'))D;

The output that I am expecting is

QUESTION_NUMBER QUESTION ANSWER
1 DO YOU TRAVEL? NO
2 HOW MANY LANGUAGUES DO YOU SPEAK? 6

What should i change in my query? Please advise.

Upvotes: 1

Views: 1124

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25938

so getting your SQL that you put into the FLATTEN and pull those apart we see

  SELECT 
    XMLGET(SRC_XML, 'HEADER', 0) as h
    ,XMLGET(h,'SURVEY_QUESTIONS') as sq
   ,GET(sq, '$') as parts
   ,d.*
  FROM TEST_XML,
  LATERAL FLATTEN(GET(XMLGET(XMLGET(SRC_XML, 'HEADER', 0),'SURVEY_QUESTIONS'), '$'))D;

that your h sq and parts are all correct. But your questions which you called q is what the actual block is, so you don't need to pull that apart.

thus what you wanted to use was:

SELECT 
    GET(XMLGET(D.VALUE, 'NUMBER'), '$') AS QUESTION_NUMBER
    , GET(XMLGET(D.VALUE, 'QUESTION_TEXT'), '$') AS QUESTION
    , GET(XMLGET(D.VALUE, 'ANSWER_TEXT'), '$') AS ANSWER
FROM TEST_XML,
LATERAL FLATTEN(GET(XMLGET(XMLGET(SRC_XML, 'HEADER', 0),'SURVEY_QUESTIONS'), '$'))D;

which gives:

QUESTION_NUMBER QUESTION                      ANSWER
1        "DO YOU TRAVEL?"                     "NO"
2        "HOW MANY LANGUAGES DO YOU SPEAK?"    6

Upvotes: 1

Related Questions