SQL XML Query nodes.value

Question

Microsoft SQL Server XML query: how can I get the data in ExtendedFields/Field/Identifier, ExtendedFields/Field/Identifier, IdentifierCode and OwnerID?

I am expecting to get values ID_US, US1 and John Smith.

Thanks

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)

TT. · Accepted Answer · 2021-03-11 06:49:16Z

1

Please try the following.

It is better not to use wildcard namespaces.

SQL

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

Output

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

edited Mar 11, 2021 at 6:49

TT.

16.2k6 gold badges53 silver badges95 bronze badges

answered Mar 10, 2021 at 20:45

Yitzhak Khabinsky

22.9k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Charlieface Over a year ago

f:Field[@name="Identifier"] etc probably better reflects requirement

Yitzhak Khabinsky Over a year ago

I completely agree. So far, no reaction from the OP.

Collectives™ on Stack Overflow

SQL XML Query nodes.value

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related