Posted by on Jan 3, 2014 in Blog, SQL | 1 comment

While it’s fresh in my head, I learned a new trick in querying an XML datatype field in SQL Server. Not really a trick, but I was enlightened on how to query a XML datatype field. I was needing to know the data structure of a XML document for some C# code I was working on. I knew what table and column the XML document was stored in, but I was looking for a particular XML document. The XML datatype field could store any form of XML document with many different types of document structures. The XML document I was looking for contained the node “Decimals” in it. Initially I tried the following, but it failed:

The error I received was:

Msg 8116, Level 16, State 1, Line 1
Argument data type xml is invalid for argument 1 of like function.

1-3-2014 11-12-16 AM

Admittedly, I was lazy in writing the query, hoping this would work, but alas it didn’t. So, down to the nitty gritty. If I didn’t know what the structure looked like, but I knew it must contain the node “Decimals”, then how would I go about filtering down the XML documents stored to what I needed? For starters, why don’t we cast the XML datatype field to a VARCHAR? With it cast then for surely I could use the LIKE statement I previously attempted. So, it would go like this:

1-3-2014 11-14-58 AM

Ahh… we are finally getting somewhere. So how does this help then? Well, now I know what the XML document would look like. So, in our case the structure of the document would be:

Now, I need to find all XML documents whose “Decimals” value is greater than 4. We would accomplish this by using the following:

And just like that we have our results. Now I can use these documents with my C# code and continue to be productive.

1-3-2014 11-20-36 AM

 

I used the following as reference in solving my issue: http://stackoverflow.com/questions/1832987/use-a-like-statment-on-sql-server-xml-datatype

Thanks to @eopdev for pointing me in the right direction. Till next time.

 

Recommended Reading:


XML in a Nutshell (In a Nutshell (O'Reilly)) (Kindle Edition)

By (author): Elliotte Rusty Harold, W. Scott Means

Kindle Edition: Check Amazon for Pricing Digital Only



Kindle Edition: Check Amazon for Pricing Digital Only


Beginning XML, 5th Edition (Kindle Edition)

By (author): Joe Fawcett, Danny Ayers, Liam R. E. Quin

Kindle Edition: Check Amazon for Pricing Digital Only

AboutNewsletter

Was this post useful? If so, consider donating: Donate via PayPalDonate Bitcoins