CODEWITHSUNDEEP
sql-serverxmlt-sqlxquery
Codez
Codez

Reputation: 1

SQL XML Query nodes.value

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)

Upvotes: 0

Views: 68

Answers (1)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22157

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 |
+-------------------+-----------------+---------------+------------+----------------+------------+

Upvotes: 1

Related Questions