XPath to select value of XMLNS attribute in T-SQL

Question

I have an XML document that looks like this. It may be that it's invalid (I didn't create it!)

<ns:url xmlns:ns="http://www.urlone.com/" xmlns:news="http://www.urltwo.com/">
.. node tree
</ns:url>

I need to build a T-SQL Query to extract the value of the xmlns:news attribute and update it. This requires XPath. But all the queries I've attempted so far fail with an error similar to:

XPath expression uses unbound namespace prefix xmlns

Is is possible to select and update this node given the XML format? If so, how?

Cheers, Matt

Mikael Eriksson Mikael Eriksson · Accepted Answer · 2012-10-22T09:36:59

To get the value you can use the OPENXML edge table.

declare @XML xml = '
<ns:url xmlns:ns="http://www.urlone.com/" xmlns:news="http://www.urltwo.com/">
</ns:url>'

declare @idoc int

exec sp_xml_preparedocument @idoc output, @XML

select X1.[text]
from openxml(@idoc, '*') as X1
  inner join openxml(@idoc, '*') as X2
    on X1.parentid = X2.id
where X2.localname = 'news' and -- Attribute name
      X2.prefix = 'xmlns' and   -- Namespace
      X2.parentid = 0           -- Attribute on root

exec sp_xml_removedocument @idoc

I don't know of a way to update the URL other than recreating the XML or to use string manipulation.

XPath to select value of XMLNS attribute in T-SQL

2 Answers