Selecting values out of XML with SQL Server 2008 with namespaces

sql, xml, xml-namespaces

Solution

You need to define the namespaces - not the prefixes! - in your `WITH XMLNAMESPACES` statement.

Try this:

;WITH XMLNAMESPACES
   ('http://collaborate.com/svn/capabilities/tdp/ManageNetworkAndServiceDiagnosticsV4/' AS p, 
    'http://wsi.nat.com/2005/06/StandardHeader/' as p1)
select 
    feed.xx.value('.','VARCHAR(MAX)') as statecode
from 
    @mxml.nodes('/p:initiateTest/p1:standardHeader/p1:stateCode') feed(xx)

Problem

My XML looks like ``` <p:initiateTest xmlns:S52="https://collaborate.com/svn/edm/tdp/CharacteristicEnumerations" xmlns:p="http://collaborate.com/svn/capabilities/tdp/ManageNetworkAndServiceDiagnosticsV4/" xmlns:p1="http://wsi.nat.com/2005/06/StandardHeader/" xmlns:p2="https://collaborate.com/svn/edm/tdp/Test" xmlns:p3="https://collaborate.com/svn/edm/tdp/Parties" xmlns:p4="https://collaborate.com/svn/edm/tdp/MORT" > <p1:standardHeader> <p1:stateCode>OK</p1:stateCode> </p1:standardHeader> </p:initiateTest> ``` And when I run the TSQL below it completes but does not return the statecode value ``` Declare @mxml XML Select @mxml = xmlString From CT_GTCS_Temp_XML Where ID = 1 ;WITH XMLNAMESPACES( 'p' AS p, 'p1' as p1, 'p2' AS p2) select feed.xx.value('.','VARCHAR(MAX)') as statecode from @mxml.nodes('//p:initiateTest/p1:standardHeader/p1:stateCode') feed(xx) ```

Original source