Steve Jones discusses a couple peculiarities of XML querying within SQL Server:
This doesn’t work.
The reason this doesn’t work is that XML is case sensitive. Meaning ORDERID != OrderID. The former is in the query, the latter in the XML document. If I change the query, this works (note I have OrderID below).
Like Steve, I’m not a big fan of doing XML processing within SQL Server, but if it’s a necessary part of your workload, it’s worth knowing.