Search code examples
sqlsql-serverxmlxqueryxquery-sql

SQL Server XQuery to select record if XML Column contains element with value

I want to select the records from the Orders table. It contains the OrderXML as XML type column.

if OrderXML has order with status given by user then it should select the record. I am trying following query but not working -

SELECT * 
FROM   ORDER 
WHERE  ORDERXML.EXISTS('/Order/header/status/text()="Processing"') = 1
Solution

  • You need to put the predicate within brackets and exist need to be lower case. XML is case sensitive and even the XML function names in SQL Server is case sensitive.

    select O.*
from [Order] as O
where O.OrderXML.exist('/Order/header/status[text() = "Processing"]') = 1