Question

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 1

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

友情链接