Today I thought I would share a little bit about the difficult XPath trail I had to go down to be able to parse data in an SQL Table. I simply wanted to extract the value of an attribute in the root node of the source XML. I found many examples online, but all simply returned null.
Here is an obfuscated XML document against which I was trying to build and XQuery. I have generated an XML variable with the contents against which I can query. Note: since there is really no web site www.btaylor.com/myXsd running the examples in this editorial will not work. However, it was the presence of an XSD in my working document that made the difficulty when finding help online, and getting the final results to work.
DECLARE @XML as XML = '<smn:Request
amp;quot;
amp;quot;
version="5">
<RequestEnvelope>
<RequestId>ZR-BDCVTGW</RequestId>
<Purpose>purchase</Purpose>
<DateCreated>2013-12-19T06:20:14Z</DateCreated>
</ RequestEnvelope>
</smn:Request>
'
What I needed to do was to extract the version attribute from the root node from xml documents such as this example. All the online documentation and examples did not include an XSD, nor any examples of how to include and use the XSD when querying.
I tried .value(‘/Request/@version[1]’, N’int’) which should have returned the number 5. Instead the value returned was null.
//Request points to the root node (there is lots of documentation on this part)
/@Version points to the value assigned to the attribute with the tag “version”.
[1] points to the first instance of @version, even though there is only one instance
To make sure my failure was not due to case sensitivity I wrote the query using all the same case as the document I was parsing. Here is my select statement
SELECT @XML.value(//zmn: Request /@version)[1]', N'int') as Version
Results
NULL
Finally I had a breakthrough finding I could supply the necessary name space for the XSD against which my documents were created. There were no real examples for this technique, but piecing together different pieces of MS Docuementation, and online examples I was able to put this together.
SELECT @XML.value('declare namespace amp;quot;; (//zmn: Request /@version)[1]', N'int') as Version
Resutls
5
The big difference in this query is that for each value I have to specify the XSD used to define the document. Interestingly, I only had to use the XSD declaration when retrieving data from the attribute of the root node. All other deeper elements worked fine when retrieving the values of child elements.
If you know a better way to make this work I would certainly be grateful. For the time being, this technique has worked for me. If others are experiencing what I did with this problem, they probably just walk away in frustration.
Reader Feedback – XPath Documentation is Lacking
Matthew
I couldn’t agree more–the documentation is lacking to say the least! I am in the middle of struggling to learn XPath and XQuery right now to extract data from some HR-XML and it is painful to say the least. Dealing with the XMLNamespaces really makes things fun, muchless if your XML is encoded differently that the database you are storing it in! Took me almost an hour to figure out why my queries weren’t returning data–the namespace server was down! Very frustrating and the error messages SQL Server gives you reminds me of the old NT 3.51 days where they spewed out something in hex and you had to figure it out.
Would love to see a decent primer on XPath. I have found this site helpful in my trials and tribulations http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx
Collet
We are using this extensively for producing xml files with order information..
There is also a requirement to read and modify the xml data without shredding it first.
I did a brief course with Learning Tree some years ago that touched on this, and just picked the rest as necessary by trial and error.
Thanks everyone for the feedback. Please post any other similar XPath or XQuery techniques you find are just not documented well enough. The best place is to leave them here online. You can still send some to me by Email and I’ll try and gather them up for publishing later.
Cheers,
Ben