CODEWITHSUNDEEP
xml-parsingsnowflake-cloud-data-platform
gayathri
gayathri

Reputation: 43

XMLGET() to get multiple inner tag values

Below is the XML data present in column xmldata.

<Inquiry>
  <MemberCode>56</MemberCode>
  <MemberName>COMP</MemberName>
  <Date>16</Date>
</Inquiry>

How to use XMLGET to extract only values from inner tag

SELECT TOP 100
    KEY,
    GET(b.VALUE,'@')::STRING as name, --returns Inquiry
    b.VALUE:"$"[1]."$"[1],--returns null
    GET(XMLGET(b.VALUE, GET(b.VALUE,'@')::STRING), '$') as value1,--returns null
    -- XMLGET(VALUE,GET(b.VALUE,'@')::STRING):"$" as value,--returns null
    b.value as children,-- returns full xmldata
    b.this
    
FROM
    (SELECT  
        KEY,
        VALUE
        ,this
    from
         FULL_XML,
        LATERAL FLATTEN(XMLGET(XMLGET(XML_DATA,'Report'),'Inquiries'):"$") )b ;

expected output for value column is:

56COMP16

Upvotes: 0

Views: 29

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

If your object is a single level thing, then to access the members of that thing, you can just name them, cast to strings, and then concat them:

with data_in_cte as (
    select parse_xml('<Inquiry>
  <MemberCode>56</MemberCode>
  <MemberName>COMP</MemberName>
  <Date>16</Date>
</Inquiry>') as XML_DATA
)
select
    get(xmlget(xml_data,'MemberCode'),'$')::text as a,
    get(xmlget(xml_data,'MemberName'),'$')::text as b,
    get(xmlget(xml_data,'Date'),'$')::text as  c,
    a || b || c as result
from data_in_cte

enter image description here

If fields can be missing, concatenating them with given all null so NVL can be used if this case is in play:

with data_in_cte as (
    select parse_xml($1) as XML_DATA
    from values
    ('<Inquiry><MemberCode>56</MemberCode><MemberName>COMP</MemberName><Date>16</Date></Inquiry>'),
    ('<Inquiry><MemberCode>42</MemberCode><MemberName>NOCOMP</MemberName></Inquiry>')
)
select
    get(xmlget(xml_data,'MemberCode'),'$')::text as a,
    get(xmlget(xml_data,'MemberName'),'$')::text as b,
    get(xmlget(xml_data,'Date'),'$')::text as  c,
    a || b || c as result,
    nvl(a,'') || nvl(b,'') || nvl(c,'') as safe_result
from data_in_cte

enter image description here

and then if you have a array of Inquiry you would want to flatten that.

Upvotes: 1

Related Questions