CODEWITHSUNDEEP
xmlsnowflake-cloud-data-platformsnowflake-schema
vvazza
vvazza

Reputation: 397

Querying XML that has nested tags through Snowflake UI

I am trying to query on a XML that contains nested tag using lateral flatten function in Snowflake. Am unable to traverse and get the results in desired format.

The table EMP_XML has a variant column SRC_XML that contains the below XML value -

<DATALOAD>
<HEADER>
    <RECDATE>
        <START>2021-03-02</START>
        <END>2021-03-02</END>
    </RECDATE>
    <EMP_DETAILS>
        <EMPLOYEE>
            <EMP_ID>123</EMP_ID>
            <EMP_NAME>ABC</EMP_NAME>
            <EMP_ADDR>DETROIT</EMP_ADDR>
        </EMPLOYEE>
        <EMPLOYEE>
            <EMP_ID>456</EMP_ID>
            <EMP_NAME>XYZ</EMP_NAME>
            <EMP_ADDR>RICHMOND</EMP_ADDR>
        </EMPLOYEE>
    </EMP_DETAILS>  
</HEADER>

When the below query was executed, no results are being fetched.

 select
       XMLGET( SRC_XML, '@EMP_ID' ):"$"::string AS EMP_ID
     , XMLGET( SRC_XML, '@EMP_NAME' ):"$"::string AS EMP_NAME
     , XMLGET( emp.value, '@EMP_ADDR' ):"$"::string as EMP_ADDR
from
    EMP_XML
    ,  lateral FLATTEN(EMP_XML.SRC_XML:"$") emp
  where emp.value like '<employee>%'

Could you please help me how to display the child tag values?

Upvotes: 1

Views: 251

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

Creating sample data:

CREATE OR REPLACE TABLE EMP_XML AS
SELECT
'<DATALOAD>
<HEADER>
    <RECDATE>
        <START>2021-03-02</START>
        <END>2021-03-02</END>
    </RECDATE>
    <EMP_DETAILS>
        <EMPLOYEE>
            <EMP_ID>123</EMP_ID>
            <EMP_NAME>ABC</EMP_NAME>
            <EMP_ADDR>DETROIT</EMP_ADDR>
        </EMPLOYEE>
        <EMPLOYEE>
            <EMP_ID>456</EMP_ID>
            <EMP_NAME>XYZ</EMP_NAME>
            <EMP_ADDR>RICHMOND</EMP_ADDR>
        </EMPLOYEE>
    </EMP_DETAILS>  
</HEADER>
</DATALOAD>'::VARIANT AS SRC_XML;

Query(key point here is PARSE_XML):

SELECT 
      XMLGET(empd.value, 'EMP_ID'):"$"::STRING   AS EMP_ID
     ,XMLGET(empd.value, 'EMP_NAME'):"$"::STRING AS EMP_NAME
     ,XMLGET(empd.value, 'EMP_ADDR'):"$"::STRING AS EMP_ADDR
FROM EMP_XML e
, LATERAL (SELECT PARSE_XML(e.SRC_XML) src_xml) p
, LATERAL FLATTEN(GET(p.SRC_XML, '$'):"$") emp
, LATERAL FLATTEN(emp.value:"$") empd
WHERE empd.value LIKE '<EMPLOYEE>%';

Output:

+-----+--------+----------+----------+
| Row | EMP_ID | EMP_NAME | EMP_ADDR |
+-----+--------+----------+----------+
|   1 |    123 | ABC      | DETROIT  |
|   2 |    456 | XYZ      | RICHMOND |
+-----+--------+----------+----------+

Upvotes: 2

Related Questions