使用带有命名空间的 SQL Server 2008 从 XML 中选择值
问题描述:
我的 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)