The problem
Stored in a varchar column,payload_xml
, we have xml that looks something like this:
<?xml version="1.0" encoding="utf-16"?>
<ReceiveMoneyParameters
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
...
<PaymentArrangementExternalReference>
JT00009084
</PaymentArrangementExternalReference>
...
</ReceiveMoneyParameters>
I need to return the underlined section as a varchar.
The solution
With the help of Tim Chapman's article Shred XML data with XQuery, I've arrived at the following code:select cast(cast(cast(payload_xml as nvarchar(max)) as xml) .query('data(//ReceiveMoneyParameters/PaymentArrangementExternalReference)') as varchar(max)) as PaymentArrangementExternalReference,Which is, admittedly a bit of a mess, but each part has its purpose, and it works!
Observations
Interesting observations from inside out:- cast as
nvarchar
so that the encoding matches"utf-16"
otherwise sql server fails with the cryptic ' data()
function returns the actual content of the node, not the whole node- I didn't need to use
[]
indexers because there is only one element.
XML parsing: ... unable to switch the encoding
'
Update
To handle different namespaces, e.g.
<?xml version="1.0" encoding="utf-16"?> <PremiumDistributionRequest xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <Payment> <PaymentId xmlns="http://il.net.za/Agreement/Payment"> 14079 </PaymentId>
add a namespace declaration to the query as follows:
select @x.query( 'declare namespace ap="http://il.net.za/Agreement/Payment"; data(//PremiumDistributionRequest/Payment/ap:PaymentId)')
3 comments:
Thanks for sharing I really loved this article the way you presented is really amazing
this helped me to gain knowledge this is one of my wonderful article i have ever seen
Best Software training institues
Come up with a great learning experience ofAzure Training in Chennai, from Infycle Technologies, the best software training institute in Chennai. Get up with other technical courses like Data Science, Selenium Automation Testing, Mobile App Development, Cyber Security, Big Data, Full Stack Development with a great learning experience and outstanding placements in top IT firms. For best offers with learning, reach us on +91-7504633633, +91-7502633633
This exact is by and large evidently stinky and close by fantastic convincing along at the rear of without a doubt legit disapproved what's more as a matter of fact helpful My impact is trying to view as in front assumed for this remarkable valuable stuffs. Windows 10 Crack Key
Post a Comment