Reputation: 1127
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
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:
Upvotes: 1