SQL XML Query nodes.value

Question

Microsoft SQL Server XML查询:如何获取ExtendedFields/Field/Identifier,ExtendedFields/Field/Identifier,IdentifierCode和OwnerID中的数据?

我期望得到值ID_US, US1和John Smith。

感谢

DECLARE @myDoc XML  
DECLARE @ProdID varchar(30)  
SET @myDoc = '<?xml version="1.0" encoding="UTF-8"?><MyFeed header="header value">
<f:TheFeed xmlns:f="urn:Thefeed-xsd">
<f:ConventionalValue>-100.681356</f:ConventionalValue><f:ReceiveXPercent>1.0</f:ReceiveXPercent><f:ReceiveXMonth>3</f:ReceiveXMonth>
<f:Fields><f:Field calcrt="SW1" name="Identifier">ID_US</f:Field><f:Field calcrt="SW5" name="IdentifierCode">US1</f:Field><f:Field calcrt="SW10" name="OwnerID">John Smith</f:Field>
</f:Fields>
</f:TheFeed>
</MyFeed>';
WITH XMLNAMESPACES(DEFAULT 'urn:Thefeed-xsd')
SELECT 
OgrRol.value('(ConventionalValue/text())[1]','nvarchar(50)') AS ConventionalValue,
OgrRol.value('(ReceiveXPercent/text())[1]','nvarchar(50)') AS ReceiveXPercent,
OgrRol.value('(ReceiveXMonth/text())[1]','nvarchar(50)') AS ReceiveXMonth,
OgrRol.value('(ExtendedFields/@Identifier)[1]','nvarchar(50)') AS Identifier,
OgrRol.value('(ExtendedFields/@IdentifierCode)[1]','nvarchar(50)') AS IdentifierCode,
OgrRol.value('(ExtendedFields/@OwnerID)[1]','nvarchar(50)') AS OwnerID
FROM 
@myDoc.nodes('/*:MyFeed/TheFeed') A(ogrRol)

Answer 1

请尝试以下操作

最好不要使用通配符命名空间。

/p>

DECLARE @myDoc XML =
'<?xml version="1.0" encoding="UTF-8"?>
<MyFeed header="header value">
<f:TheFeed xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.fpml.org/2009/FpML-4-7"
xmlns:f="urn:Thefeed-xsd">
<f:ConventionalValue>-100.681356</f:ConventionalValue>
<f:ReceiveXPercent>1.0</f:ReceiveXPercent>
<f:ReceiveXMonth>3</f:ReceiveXMonth>
<f:ExtendedFields>
<f:Field calcrt="SW1" name="Identifier">ID_US</f:Field>
<f:Field calcrt="SW5" name="IdentifierCode">US1</f:Field>
<f:Field calcrt="SW10" name="OwnerID">John Smith</f:Field>
</f:ExtendedFields>
</f:TheFeed>
</MyFeed>';
WITH XMLNAMESPACES('urn:Thefeed-xsd' AS f)
SELECT c.value('(f:ConventionalValue/text())[1]','nvarchar(50)') AS ConventionalValue
,c.value('(f:ReceiveXPercent/text())[1]','nvarchar(50)') AS ReceiveXPercent
,c.value('(f:ReceiveXMonth/text())[1]','INT') AS ReceiveXMonth
,p.value('(f:Field[@calcrt="SW1"]/text())[1]','nvarchar(50)') AS Identifier
,p.value('(f:Field[@calcrt="SW5"]/text())[1]','nvarchar(50)') AS IdentifierCode
,p.value('(f:Field[@calcrt="SW10"]/text())[1]','nvarchar(50)') AS OwnerID
FROM @myDoc.nodes('/MyFeed/f:TheFeed') t1(c)
CROSS APPLY t1.c.nodes('f:ExtendedFields') AS t2(p);

+-------------------+-----------------+---------------+------------+----------------+------------+
| ConventionalValue | ReceiveXPercent | ReceiveXMonth | Identifier | IdentifierCode |  OwnerID   |
+-------------------+-----------------+---------------+------------+----------------+------------+
|       -100.681356 |             1.0 |             3 | ID_US      | US1            | John Smith |
+-------------------+-----------------+---------------+------------+----------------+------------+