CODEWITHSUNDEEP
sqlxmlxml-parsingsnowflake-cloud-data-platform
Raj
Raj

Reputation: 623

How to convert the column type as XML and extract the value on Snowflake?

I have an XML column as a varchar type in a table. I would like to convert it to XML column type and extract the value of Transfer.

This is what my each row value would look like.

<PropBag>
<Prop Name="Sub" Value="0"/>
<Prop Name="Adjustments 2" Value="0"/>
<Prop Name="Adjustments 3" Value="0"/>
<Prop Name="car" Value="1"/>
<Prop Name="Answers" Value="3"/>
<Prop Name="Transfer" Value="No"/>
<Prop Name="Applied" Value="No"/>
</PropBag>

I have tried converting to to_variant type and applied XMLGET function but still no luck.

select XMLGET(to_variant (column),'Prop Bag', 0 ), column from table

New to snowflake. Any help would be appreciated.

Thanks,

Upvotes: 0

Views: 977

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

select PARSE_XML('<PropBag>
<Prop Name="Sub" Value="0"/>
<Prop Name="Adjustments 2" Value="0"/>
<Prop Name="Adjustments 3" Value="0"/>
<Prop Name="car" Value="1"/>
<Prop Name="Answers" Value="3"/>
<Prop Name="Transfer" Value="No"/>
<Prop Name="Applied" Value="No"/>
</PropBag>') as xml
    ,XMLGET(xml,'PropBag', 0 )
    ,GET(xml, '$') 
    ,GET(xml, '@attrname')
    ,GET(xml, '@')
    ,XMLGET(xml, 'Prop', 0 )
    ;

gives

XMLGET(XML,'PROPBAG', 0 )
NULL

GET(XML, '$')
[
  {
    "$": "",
    "@": "Prop",
    "@Name": "Sub",
    "@Value": 0
  },
  {
    "$": "",
    "@": "Prop",
    "@Name": "Adjustments 2",
    "@Value": 0
  },
  {
    "$": "",
    "@": "Prop",
    "@Name": "Adjustments 3",
    "@Value": 0
  },
  {
    "$": "",
    "@": "Prop",
    "@Name": "car",
    "@Value": 1
  },
  {
    "$": "",
    "@": "Prop",
    "@Name": "Answers",
    "@Value": 3
  },
  {
    "$": "",
    "@": "Prop",
    "@Name": "Transfer",
    "@Value": "No"
  },
  {
    "$": "",
    "@": "Prop",
    "@Name": "Applied",
    "@Value": "No"
  }
]

GET(XML, '@ATTRNAME')
NULL


GET(XML, '@')
"PropBag"

XMLGET(XML, 'PROP', 0 )
<Prop Name="Sub" Value="0"></Prop>

So based on XMLGET docs, that implies your you don't "need" to access the top level PropBag as you already have it. Thus was asking for it does not work, where-as asking for the Prop does work..

This can also be seen by the GET(xml, '$') which is "extract the content"

in these examples you see it read documents and the FLATTEN on "$" which loops on the sub objects.

like:

SELECT
    auction_announcement.index as auction_contents_index,
    auction_announcement.value as auction_contents_value
FROM treasury_auction_xml,
LATERAL FLATTEN(to_array(treasury_auction_xml.src_xml:"$" )) xml_doc,
LATERAL FLATTEN(to_array(xml_doc.VALUE:"$" )) auction_announcement;

Upvotes: 1

Related Questions