Filter SQL queries on the XML column using XPath/XQuery

sql xpath xquery
Mats Fredriksson picture Mats Fredriksson · Nov 13, 2009 · Viewed 24.5k times · Source

I'm having a table with one XML column. I'd like to filter out the rows where a specific attribute in the XML match a string, essentially doing a WHERE or HAVING.

The table looks something like this

| id | xml |

And the XML something similar to 

<xml>
  <info name="Foo">
    <data .../>
  </info>
<xml>

I want to get all ids where the @name attribute matched a value.

I have been able to do the following:

SELECT id, xml.query('data(/xml/info/@name)') as Value
FROM Table1
WHERE CAST(xml.query('data(/xml/info/@name)') as varchar(1024)) = @match

But it's incredibly slow.

There must be a better way of filtering on the output of the query.

Answer

Mats Fredriksson picture Mats Fredriksson · Nov 13, 2009

Found it. Instead of using query() I should be using exist().

My query would then be

SELECT id, xml.query('data(/xml/info/@name)') as Value
FROM Table1
WHERE xml.exist('/xml/info/[@name=sql:variable("@match")]') = 1

Related questions

xQuery LIKE-operator?

Is there any way to perform a LIKE operation with XQuery in the same way as with SQL? I wan't to construct some "startswith", "endswith" and "contains"-expressions. Example of what I want to achieve: for $x in /user where $…

Read More
Merging XML in an SQL Server

Let's say I have the following two pieces of XML in my database <!-- XML 1 --> <pairs> <item key="a">xml 1 a value</item> <item key="b">xml 1 b value&…

Read More
Search XML with a LIKE or similar full search operation

I want to search an XML valued column to see if a contains a string. I don't know the schema, I want to know if the string is contained anywhere at all. I don't know if XPATH would work in …

Read More