SELECT FROM XML MULTIPLE ELEMENTS SQL

Question

I have a return of XML that I wish to select using SQL. I don't have a problem selecting from the XML when I specify the index for the element. This is the XML in question.

<root httpStatusCode="200">
    <messages />
    <succesfulResponses>
        <item position="0">
            <response dln="TESTDLN" ServiceVersion="1" hubServiceVersion="1.0.0.0" ProcessingDate="2017-11-20T10:42:20.579Z" hubProcessingDate="2017-11-20T10:41:16.5415151Z" httpStatusCode="200">
                <licence status="FC" validFrom="2017-03-18" validTo="2024-10-31" directiveIndicator="4">
                    <entitlements>
                        <item code="A" validFrom="2006-04-07" validTo="2057-05-20" priorTo="false" type="P">
                            <restrictions />
                        </item>
                        <item code="AM" validFrom="2014-10-21" validTo="2057-05-20" priorTo="false" type="F">
                            <restrictions>
                                <item type="122" info="null" />
                            </restrictions>
                        </item>
                        <item code="B" validFrom="2014-10-21" validTo="2057-05-20" priorTo="false" type="F">
                            <restrictions />
                        </item>
                        <item code="BE" validFrom="2014-11-01" validTo="2057-05-20" priorTo="false" type="P">
                            <restrictions />
                        </item>
                        <item code="F" validFrom="2014-10-21" validTo="2057-05-20" priorTo="false" type="F">
                            <restrictions />
                        </item>
                        <item code="G" validFrom="2014-11-01" validTo="2057-05-20" priorTo="false" type="P">
                            <restrictions />
                        </item>
                        <item code="H" validFrom="2014-11-01" validTo="2057-05-20" priorTo="false" type="P">
                            <restrictions />
                        </item>
                        <item code="K" validFrom="2014-10-21" validTo="2057-05-20" priorTo="false" type="F">
                            <restrictions />
                        </item>
                        <item code="Q" validFrom="2014-10-21" validTo="2057-05-20" priorTo="false" type="F">
                            <restrictions>
                                <item type="122" info="null" />
                            </restrictions>
                        </item>
                    </entitlements>
                    <endorsements />
                </licence>
                <messages />
            </response>
        </item>
    </succesfulResponses>
    <errorResponses />
</root>

The SQL I have written so far is as follows:

select
Rec.value('(@dln)[1]','char(50)'),
Rec.value('(licence/@status)[1]','char(2)'),
pd.value('(entitlements/item/@code)[1]','char(2)')
FROM @xmlData.nodes('//root/succesfulResponses/item/response') as x(Rec)
cross apply @xmlData.nodes('//root/succesfulResponses/item/response/licence') as i(pd)

This returns the obviously First Row code of 'A', however there can be multiple 'entitlements' and I don't ever know how many there could be 3 there could be 9.

I thought a Cross Apply would work but I can't seem to make that work either.

Any thoughts/help.

marc_s · Accepted Answer · 2017-11-21 11:13:04Z

2

Try this - expand the XPath after the CROSS APPLY to include the entitlements/item part:

select
    Rec.value('(@dln)[1]','char(50)'),
    Rec.value('(licence/@status)[1]','char(2)'),
    pd.value('@code', 'char(2)')
FROM
     @xmlData.nodes('//root/succesfulResponses/item/response') as x(Rec)
cross apply 
    @xmlData.nodes('//root/succesfulResponses/item/response/licence/entitlements/item') as i(pd)

Returns:

answered Nov 21, 2017 at 11:13

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

Lynchie Over a year ago

Top man - I tried something similar but couldn't get it to work.

Sergey Menshov · Accepted Answer · 2017-11-21 11:17:25Z

The variant with OPENXML

DECLARE @idoc int, @doc varchar(MAX)

SET @doc='<root httpStatusCode="200">
    <messages />
    <succesfulResponses>
        <item position="0">
            <response dln="TESTDLN" ServiceVersion="1" hubServiceVersion="1.0.0.0" ProcessingDate="2017-11-20T10:42:20.579Z" hubProcessingDate="2017-11-20T10:41:16.5415151Z" httpStatusCode="200">
                <licence status="FC" validFrom="2017-03-18" validTo="2024-10-31" directiveIndicator="4">
                    <entitlements>
                        <item code="A" validFrom="2006-04-07" validTo="2057-05-20" priorTo="false" type="P">
                            <restrictions />
                        </item>
                        <item code="AM" validFrom="2014-10-21" validTo="2057-05-20" priorTo="false" type="F">
                            <restrictions>
                                <item type="122" info="null" />
                            </restrictions>
                        </item>
                        <item code="B" validFrom="2014-10-21" validTo="2057-05-20" priorTo="false" type="F">
                            <restrictions />
                        </item>
                        <item code="BE" validFrom="2014-11-01" validTo="2057-05-20" priorTo="false" type="P">
                            <restrictions />
                        </item>
                        <item code="F" validFrom="2014-10-21" validTo="2057-05-20" priorTo="false" type="F">
                            <restrictions />
                        </item>
                        <item code="G" validFrom="2014-11-01" validTo="2057-05-20" priorTo="false" type="P">
                            <restrictions />
                        </item>
                        <item code="H" validFrom="2014-11-01" validTo="2057-05-20" priorTo="false" type="P">
                            <restrictions />
                        </item>
                        <item code="K" validFrom="2014-10-21" validTo="2057-05-20" priorTo="false" type="F">
                            <restrictions />
                        </item>
                        <item code="Q" validFrom="2014-10-21" validTo="2057-05-20" priorTo="false" type="F">
                            <restrictions>
                                <item type="122" info="null" />
                            </restrictions>
                        </item>
                    </entitlements>
                    <endorsements />
                </licence>
                <messages />
            </response>
        </item>
    </succesfulResponses>
    <errorResponses />
</root>'


EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

SELECT *  
FROM OPENXML(@idoc,'/root/succesfulResponses/item/response/licence/entitlements/item',2)   
  WITH (
          status        varchar(10)         '../../@status',
          code          varchar(10)         './@code',
          validFrom     date                './@validFrom'
        );

EXEC sp_xml_removedocument @idoc;
GO

Collectives™ on Stack Overflow

SELECT FROM XML MULTIPLE ELEMENTS SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related