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

Question

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

possible duplicate of How can I query a value in SQL Server XML column — Gidil
– Gidil, Commented Jul 24, 2014 at 7:12
i don't understand is the question not clear? why down voting and closing? — Bhalchandra K
– Bhalchandra K, Commented Jul 24, 2014 at 7:13
I didn't down vote your Q. I suggest you follow the link in my prev comment, I think you'll find the answer there. — Gidil
– Gidil, Commented Jul 24, 2014 at 7:15
Then you agree that this post is a duplicate? Try to do some searching first next time before you post a Q :-) — Gidil
– Gidil, Commented Jul 24, 2014 at 7:24

Mikael Eriksson · Accepted Answer · 2014-07-24 08:38:08Z

3

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

answered Jul 24, 2014 at 8:38

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related