Storing XML in SQL 2005 XMLNS issue

Question

I'm trying to store XML in SQL 2005. I have a very simple table with an Id and a XML column.

When the XML contains the attribute xmlns my searching doesn't work.

This is my XML;

insert into XMLTest (ItemXML) values ( 
'<MessageType>
    <ItemId id="ABC" xmlns="ss" />
    <Subject>sub</Subject>
</MessageType>
')

And this is my Query;

select itemid, ItemXML.query('(/MessageType/ItemId)') from XMLTest order by ItemId desc

If I change the attribute xmlns to anything else my query works.

I don't think I know enough about XML to understand what SQL is doing with the namespace. But it must be processing it and storing it differently maybe? Anyone had this issue?

Answer 1

If you have a XML namespace on your XML node, you need to use that when querying - something like this - either directly in every single .query() or .value() function locally like this:

SELECT 
   itemid, 
   ItemXML.query('declare namespace x="ss";(/MessageType/x:ItemId)') 
FROM 
   XMLTest 
ORDER BY 
   ItemId DESC

(see here on SQL Server Books Online for more details about this approach), or if you need to reference that XML namespace a lot, you can also define it as a scope:

WITH XMLNAMESPACES('ss' as x)
SELECT 
   itemid, 
   ItemXML.query('(/MessageType/x:ItemId)') 
FROM 
   XMLTest 
ORDER BY 
   ItemId DESC

See the details about WITH XMLNAMESPACES on MSDN SQL Server Books Online