Scott Wood
Scott Wood

Reputation: 1127

How do I query an xml attribute in snowflake?

Here is my XML code:

<xml>
    <transaction date="06092022" reseller-id="100">
        <transactionid>1</transactionid>
        <eventName>Steely Dan Concert</eventName>
        <numberOfPurchasedTickets>40</numberOfPurchasedTickets>
        <totalAmount>1200</totalAmount>
        <salesChannel>mobile app</salesChannel>
        <customer>
            <firstName>Donald</firstName>
            <lastName>Fagen</lastName>
        </customer>
        <officeLocation>Los Angeles</officeLocation>
        <dateCreated>06/09/2022</dateCreated>
    </transaction>
</xml>

Here is my query:

SELECT 
    XMLGET( retail.value, '@date' ):"$" as "Transaction Date",
    XMLGET( retail.value, '@reseller-id' ):"$" as "Reseller ID",
    XMLGET( retail.value, 'transactionid' ):"$" as "Transaction ID",
    XMLGET( retail.value, 'eventName' ):"$" as "Event Name",
    XMLGET( retail.value, 'numberOfPurchasedTickets' ):"$" as "Number of Purchased Tickets",
    XMLGET( retail.value, 'totalAmount' ):"$" as "Total Amount",
    XMLGET( retail.value, 'salesChannel' ):"$" as "Sales Channel",
    XMLGET( retail.value, 'officeLocation' ):"$" as "Office Location",
    XMLGET( retail.value, 'dateCreated' ):"$" as "Date Created",
    XMLGET( customer.value, 'firstName' ):"$" as "First Name",
    XMLGET( customer.value, 'lastName' ):"$" as "Last Name"
FROM stage_thirdparty retail_xml,
LATERAL FLATTEN(to_array(retail_xml.src_xml:"$" )) retail,
LATERAL FLATTEN(to_array(retail.value:"$" )) customer
where GET( customer.value, '@') = 'customer'
;

Date of the transaction and the reseller-id always come out null. Everything else works.

I've tried quite a number of variants, but I just can't find the ones that allow me to see the attributes in the transaction.

Upvotes: 2

Views: 695

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175716

Using : to access attribute value:

SELECT 
     retail.value:"@date"::TEXT  as "Transaction Date",
     retail.value:"@reseller-id"::TEXT as "Reseller ID"
     -- ...
FROM stage_thirdparty retail_xml,
LATERAL FLATTEN(to_array(retail_xml.src_xml:"$" )) retail,
LATERAL FLATTEN(to_array(retail.value:"$" )) customer
where GET( customer.value, '@') = 'customer';

For sample data:

CREATE TABLE stage_thirdparty 
AS
SELECT 
PARSE_XML('<xml>
    <transaction date="06092022" reseller-id="100">
        <transactionid>1</transactionid>
        <eventName>Steely Dan Concert</eventName>
        <numberOfPurchasedTickets>40</numberOfPurchasedTickets>
        <totalAmount>1200</totalAmount>
        <salesChannel>mobile app</salesChannel>
        <customer>
            <firstName>Donald</firstName>
            <lastName>Fagen</lastName>
        </customer>
        <officeLocation>Los Angeles</officeLocation>
        <dateCreated>06/09/2022</dateCreated>
    </transaction>
</xml>
') AS src_xml;

Output:

enter image description here

Upvotes: 1

Related Questions