Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Filter SQL queries on the XML column using XPath/XQuery

Tags:

sql

xpath

xquery

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.

like image 401
Mats Fredriksson Avatar asked Nov 13 '09 15:11

Mats Fredriksson


People also ask

How do I filter XML data in SQL Server?

XQuery in the SQL Server helps to query and extract data from XML documents. XQuery gives different approaches to get information from the XML document and the equivalent can be used on applying a data filter or where clause on XML elements as well.

How can I get SQL query results in XML?

You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.

How do I filter in XML?

Navigate through your XML Document in the Messages tab and right-click the element/attribute's value that you want to filter. Next, click the Add Data Filter action.


1 Answers

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
like image 65
Mats Fredriksson Avatar answered Nov 05 '22 06:11

Mats Fredriksson