CODEWITHSUNDEEP
sqlxmlsnowflake-cloud-data-platform
Webb
Webb

Reputation: 139

Snowflake Get value from XML column

I am working in Snowflake

I need a specific value from XML

SELECT data_xml,REGEXP_SUBSTR(data_xml,'<pinLessNetworkBin>(.*?)(</pinLessNetworkBin>)',3) as network
FROM  "DW"."DB"."TABLE"

My results for now

<pinLessNetworkBin>STAR</pinLessNetworkBin>

I just need the value inside

Here the xml:

<?xml version="1.0" encoding="UTF-8"?>
<ns0:FundingSource xmlns:ns0="www.url.com/be/example/payments/model/Concepts/FundingSource" Id="12887819260" extId="">
  <id>3939</id>
  <pinLessNetworkBin>STAR</pinLessNetworkBin>
</ns0:FundingSource>

How I can get that value?

Regards

Upvotes: 1

Views: 774

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25968

the contents of an XML object is retrieved via GET(object, '$') thus for your regex result GET(parse_xml(network), '$') will get you the content. See GET

or you should really retrieve the pinLessNetworkBin via XMLGET:

SELECT data_xml,
    XMLGET(parse_xml(data_xml), 'pinLessNetworkBin') as pinLessNetworkBin
FROM  "DW"."DB"."TABLE" 
  parse_xml(data_xml)

which will give you the <pinLessNetworkBin>STAR</pinLessNetworkBin> thus you want to fetch the contents

SELECT data_xml,
    get(XMLGET(parse_xml(data_xml), 'pinLessNetworkBin'), '$') as pinLessNetworkBin
FROM  "DW"."DB"."TABLE" 
  parse_xml(data_xml)

should give you 'STAR'

see the PARSE_XML

Upvotes: 2

Related Questions