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)')
9 comments:
Thank you so much for all the wonderful information about Technology! I love your work.
Spring Training in Chennai
Spring framework Certification
Spring framework Training
Hibernate Training in Chennai
Hibernate Training
Spring Hibernate Training in Chennai
Spring Hibernate Training in Chennai
Spring Hibernate Training
Such an excellent and interesting blog, do post like this more with more information, This was very useful, Thank you.
aviation training in Chennai
cabin crew course in Chennai
diploma in airline and airport management in Chennai
airport ground staff training in Chennai
Aviation Academy in Chennai
air hostess training in Chennai
airport management courses in Chennai
ground staff training in Chennai
Nice Blog....thanks for sharing..
Spring Training in Chennai
Spring and Hibernate Training in Chennai
Core Spring Training
spring Training in OMR
spring Training in Porur
Hibernate Training in Chennai
javascript training in chennai
QTP Training in Chennai
Mobile Testing Training in Chennai
SAS Training in Chennai
Content Writing Company in Delhi
Content Writing Services in Delhi
Mobile App Development Company Delhi
PPC Company in Delhi
PPC Company in India
wonderful article contains lot of valuable information. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
This article resolved my all queries.good luck an best wishes to the team members.continue posting.learn digital marketing use these following link
Digital Marketing Course in Chennai
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
Effectually composed data. it will probably be helpful to us all who utilizes it, comprising of me. continue to happen the sort produce results. For specific I will look at additional posts. This website page appears to income a tomfoolery measure of traffic.. MS Office 2019 Crack
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