Category Archives: Advance SQL Server

How-to: Query XML Column Using value

Using SQL Server 2005, I am trying to perform a query on a table that has an
XML column type.
I want to return rows that contain a value within the XML, however it is not
working.
See query below:
SELECT *
FROM XMLTable
WHERE XMLCol.value(‘(/Method/Methods/@LowLimit)[1]’, ‘int’) = 5
The XML in the column is:

 
    1
    PKI
    2006-05-01T00:00:00-04:00
    2006-05-02T00:00:00-04:00
    5
    10
 
 
    1
    10
    25
 

 

Solution

WITH XMLNAMESPACES(DEFAULT ‘http://tempuri.org/Method.xsd’ )
SELECT *
FROM XMLTable
WHERE XMLCol.value(‘(/Method/Methods/LowLimit)[1]’, ‘int’) = 5

Problem 2

Also I have been trying to query on the similar XML but the current query
using the value() method stops at the first XML tag it sees and doesn’t check
the remaiming,.
See XML below.
There is more than one “” however using the query:
WITH XMLNAMESPACES(DEFAULT ‘http://tempuri.org/Method.xsd’ )
SELECT *
FROM XMLTable
WHERE XMLCol.value(‘(/Method/Methods/HighLimit)[1]’, ‘int’) = 32
It only sees the first “” not the ones below it.
I have tried ti use the FLWOR with no success.
How can the query using the vaiue() method be used to propagte down?
If I bump up the the number to 2 in the WHERE clause it only sees the second
“” then it will return the row where HighLimt = 32:
    WHERE XMLCol.value(‘(/Method/Methods/HighLimit)[2]’, ‘int’) = 32
Thanks for any help.

 
    3
    Joe
    2005-07-09T00:00:00-04:00
    2006-03-15T00:00:00-05:00
    1
    8
 
 
    4
    Mary
    0200-05-12T00:00:00-04:00
    2004-07-30T00:00:00-04:00
    18
    32
 
 
    1
    10
    25
 
 
    2
    20
    100
 

 

Solution 2

Have found (through DataPoints download 
http://msdn.microsoft.com/msdnmag/issues/06/03/DataPoints/default.aspx)
can use the exists() method to perfom the search throughout the XML document:

WHERE XMLCol.exist(‘/Method/Methods/LowLimit[contains(.,”18″)]’) = 1

Source and this and this

My Tip do not mention namespace if you don’t have any Schema for your XML

Advertisements