使用带有命名空间的 SQL Server 2008 从 XML 中选择值 [英] Selecting values out of XML with SQL Server 2008 with namespaces

问题描述

我的 XML 看起来像

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>

当我运行下面的 TSQL 时，它完成但不返回状态代码值

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)

推荐答案

您需要定义命名空间 - 而不是前缀！- 在您的 WITH XMLNAMESPACES 语句中.

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

试试这个:

;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)

这篇关于使用带有命名空间的 SQL Server 2008 从 XML 中选择值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！