Parsing XML by OpenXML with multiple Parent nodes with multiple child nodes

Question

I have the following XML:

<Report>
    <Accounts>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>45555</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>502</PaymentCode>
              <PaymentAmount currCode="GBP">7000.00000000</PaymentAmount>
            </PaymentData>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>50000</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
    </Accounts>
</Report>

My SQL Code is parsing this with the following code:

SELECT  
            [currCode]  AS [Currency], 
            [AccountBalance] AS [AccountBalance],
            [PaymentCode] AS [PaymentCode],
            [PaymentCurrCode] AS [PaymentCurrCode],
            [PaymentAmount] AS [PaymentAmount]
        FROM OPENXML(@hDoc, 'Report/Accounts/Account',2)
            WITH 
            (
                [currCode] [nchar](3) 'currCode',
                [AccountBalance] [decimal](18, 0) 'AccountBalance',

                [PaymentCode] [nchar](10) 'Payments/PaymentData/PaymentCode',
                [PaymentCurrCode] [nchar](3) 'Payments/PaymentData/PaymentAmount/@currCode',
                [PaymentAmount] [decimal](18, 0) 'Payments/PaymentData/PaymentAmount'
            )

I am getting the following result:

currCode | AccountBalance | PaymentCode | PaymentCurrCode | PaymentAmount
————————————————————————————————————————————————————————————————————————————————
USD      | 45555          | 502         |   GBP           |7000.00000000
USD      | 50000          | 501         |   USD           |5000.00000000

I am trying to get the multiple paymentdata and multiple account with the same openXml query. How Can is get all the data with the following result:

currCode | AccountBalance | PaymentCode | PaymentCurrCode | PaymentAmount
————————————————————————————————————————————————————————————————————————————————
USD      | 45555          | 502         |   GBP           |7000.00000000
USD      | 45555          | 501         |   USD           |5000.00000000
USD      | 50000          | 501         |   USD           |5000.00000000

Hi Shuvra, OPENXML is absolutely outdated... Do you have to stick to this approach or might it be a more modern one too? — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 17, 2016 at 21:30
Hi Shuvra, didn't wait until you answer, just posted two answers. Pick whatever you like :-) (Btw: I voted this question up, as it is reall well articulated: sample data, own approach, wrong output and expected output. If just all questions where like this... Thx! — Gottfried Lesigang
– Gottfried Lesigang, Commented Feb 17, 2016 at 21:47
Hi Shnugo, Sorry to reply late. OPENXML works but as you suggest I will try with @XML.nodes. Thanks again for your help. — Shuvra
– Shuvra, Commented Feb 18, 2016 at 9:55

Gottfried Lesigang · Accepted Answer · 2016-02-17 21:46:19Z

This is an up-to-date and state-of-the-art approach with XQuery/XPath methods. The result is the same, just faster and better to read:

DECLARE @XML XML=
'<Report>
    <Accounts>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>45555</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>502</PaymentCode>
              <PaymentAmount currCode="GBP">7000.00000000</PaymentAmount>
            </PaymentData>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>50000</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
    </Accounts>
</Report>';

SELECT Payment.value('(../../Currency)[1]','nchar(3)') AS currCode
      ,Payment.value('(../../AccountBalance)[1]','decimal(18,0)') AS AccountBalance
      ,Payment.value('PaymentCode[1]','nchar(10)') AS PaymentCode
      ,Payment.value('PaymentAmount[1]/@currCode','nchar(3)') AS PaymentCurrCode
      ,Payment.value('PaymentAmount[1]','decimal(18,0)') AS PaymentCurrCode
FROM @XML.nodes('Report/Accounts/Account/Payments/PaymentData') AS One(Payment)

Gottfried Lesigang · Accepted Answer · 2016-02-17 21:39:25Z

This should work for you:

DECLARE @XML XML=
'<Report>
    <Accounts>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>45555</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>502</PaymentCode>
              <PaymentAmount currCode="GBP">7000.00000000</PaymentAmount>
            </PaymentData>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
        <Account>
          <Currency>USD</Currency>
          <AccountBalance>50000</AccountBalance>
          <Payments>
            <PaymentData>
              <PaymentCode>501</PaymentCode>
              <PaymentAmount currCode="USD">5000.00000000</PaymentAmount>
            </PaymentData>
          </Payments>
        </Account>
    </Accounts>
</Report>';

DECLARE @hDoc INT;
EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML; 

SELECT  
            [currCode]  AS [Currency], 
            [AccountBalance] AS [AccountBalance],
            [PaymentCode] AS [PaymentCode],
            [PaymentCurrCode] AS [PaymentCurrCode],
            [PaymentAmount] AS [PaymentAmount]
        FROM OPENXML(@hDoc, 'Report/Accounts/Account/Payments/PaymentData',2)
            WITH 
            (
                [currCode] [nchar](3) '../../Currency',
                [AccountBalance] [decimal](18, 0) '../../AccountBalance',

                [PaymentCode] [nchar](10) 'PaymentCode',
                [PaymentCurrCode] [nchar](3) 'PaymentAmount/@currCode',
                [PaymentAmount] [decimal](18, 0) 'PaymentAmount'
            )
EXEC sp_xml_removedocument @hDoc;

Collectives™ on Stack Overflow

Parsing XML by OpenXML with multiple Parent nodes with multiple child nodes

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related