Click here to Skip to main content
15,903,798 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to parse xml string with child nodes having same name while fetching from a table in sql. Below is the xml string and I want to fetch value in the DebitAccountnumber nodes. below fetch query works only for distinct node names
My xml string is as below:
<pre><HB_EAI_REQUEST>
   <HB_EAI_HEADER>
      <MsgFormat>SETTLEMENT.POSTING</MsgFormat>
      <MsgVersion>0000</MsgVersion>
      <RequestorId>HB</RequestorId>
      <RequestorChannelId>DDS</RequestorChannelId>
      <RequestorUserId>00379</RequestorUserId>
      <RequestorLanguage>E</RequestorLanguage>
      <RequestorSecurityInfo>RequestorSecurityInfo</RequestorSecurityInfo>
      <EaiReference>0</EaiReference>
      <ReturnCode>0000</ReturnCode>
   </HB_EAI_HEADER>
   <Request>
      <SettlementPostingRequest>
         <ReferenceNum>IPOS071122000989</ReferenceNum>
         <DebitAccount>
            <DebitAccountNo>028676488148</DebitAccountNo>
            <DebitAmount>100000</DebitAmount>
            <DebitNarration>Net Settlement 11.11.22</DebitNarration>
         </DebitAccount>
         <DebitAccount>
            <DebitAccountNo>AED1281000020002</DebitAccountNo>
            <DebitAmount>100000</DebitAmount>
            <DebitNarration>Mirror Ac 11.11.22;</DebitNarration>
         </DebitAccount>
         <CreditAccount>
            <CreditAccountNo>020900001046</CreditAccountNo>
            <CreditAmount>200000</CreditAmount>
            <CreditNarration>Net Settlement 11.11.22</CreditNarration>
         </CreditAccount>
         <BranchCode />
         <TimeStamp>20221107161306660</TimeStamp>
         <SourceSystem>L</SourceSystem>
      </SettlementPostingRequest>
   </Request>
</HB_EAI_REQUEST>


What I have tried:

select CAST(CAST(REQ.MQXMLdata AS XML).query('data(/*/Request/DebitAccount/DebitAccountNo)') AS VARCHAR(50)) AS DebitAccountNumber from test
Posted
Updated 8-Dec-22 2:36am
v2

1 solution

Your query doesn't work with your example XML, because the DebitAccount node is not a direct child of the Request node.

Change your query to specify the correct path to the node:
SQL
select CAST(CAST(REQ.MQXMLdata AS XML).query('data(/*/Request/SettlementPostingRequest/DebitAccount/DebitAccountNo)') AS VARCHAR(50)) AS DebitAccountNumber from test 
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900