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:
<Method xmlns="http://tempuri.org/Method.xsd">
  <Methods>
    <Key>1</Key>
    <Name>PKI</Name>
    <DateCreated>2006-05-01T00:00:00-04:00</DateCreated>
    <DateModified>2006-05-02T00:00:00-04:00</DateModified>
    <LowLimit>5</LowLimit>
    <HighLimit>10</HighLimit>
  </Methods>
  <SubMethod>
    <Key>1</Key>
    <Pressure>10</Pressure>
    <Temperature>25</Temperature>
  </SubMethod>
</Method>

 

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 "<Methods>" 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 "<Methods>" 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
"<Methods>" then it will return the row where HighLimt = 32:
    WHERE XMLCol.value('(/Method/Methods/HighLimit)[2]', 'int') = 32
Thanks for any help.
<Method xmlns="http://tempuri.org/Method.xsd">
  <Methods>
    <Key>3</Key>
    <Name>Joe</Name>
    <DateCreated>2005-07-09T00:00:00-04:00</DateCreated>
    <DateModified>2006-03-15T00:00:00-05:00</DateModified>
    <LowLimit>1</LowLimit>
    <HighLimit>8</HighLimit>
  </Methods>
  <Methods>
    <Key>4</Key>
    <Name>Mary</Name>
    <DateCreated>0200-05-12T00:00:00-04:00</DateCreated>
    <DateModified>2004-07-30T00:00:00-04:00</DateModified>
    <LowLimit>18</LowLimit>
    <HighLimit>32</HighLimit>
  </Methods>
  <SubMethod>
    <Key>1</Key>
    <Pressure>10</Pressure>
    <Temperature>25</Temperature>
  </SubMethod>
  <SubMethod>
    <Key>2</Key>
    <Pressure>20</Pressure>
    <Temperature>100</Temperature>
  </SubMethod>
</Method>

 

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