Querying a SQL Server XML Datatype field using the SQL LIKE statement

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:

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

Filed under: Blog, SQL

  1. The Alchemist BR
    Thanks for the mention, man! Regards, @eopdev