Skip to Main Content

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Convert XML Data into table format

3679117Jun 8 2018 — edited Jun 9 2018

Hello,

I have a table with the following type of XML Type records:

<row id="AA17314BPKZS">

    <c1>USD</c1>

    <c2>AAACT17314JN3GFGBF</c2>

    <c2 m="2">AAACT17314JN3GFGBH</c2>

    <c2 m="3">AAACT17318QL758BY2</c2>

    <c2 m="4">AAACT17318ZS22GYRV</c2>

    <c3>LENDING-SETTLE-SETTLEMENT</c3>

    <c3 m="2">LENDING-APPLYPAYMENT-PO.WITHDRAWAL</c3>

    <c3 m="3">LENDING-APPLYPAYMENT-PR.REPAYMENT</c3>

    <c3 m="4">LENDING-APPLYPAYMENT-PR.REPAYMENT</c3>

    <c4>20171110</c4>

    <c4 m="2">20171110</c4>

    <c4 m="3">20171114</c4>

    <c4 m="4">20171114</c4>

    <c5>1000000001329.CREDITAA.USD50000000*USD</c5>

    <c5 m="2">ACCOUNT.PAYACCOUNT</c5>

    <c5 m="3">CONTINSUFEE.DUECONTINSUFEE</c5>

    <c5 m="3" s="2">CONTMGMTFEE.DUECONTMGMTFEE</c5>

    <c5 m="3" s="3">CONTMGMTFEE-VAT.DUECONTMGMTFEE-VAT</c5>

    <c5 m="3" s="4">PRINCIPALINT.DUEPRINCIPALINT</c5>

    <c5 m="3" s="5">ACCOUNT.DUEACCOUNT</c5>

    <c5 m="4">ACCOUNT.DUEACCOUNT</c5>

    <c6>50000000</c6>

    <c6 m="2">50000000</c6>

    <c6 m="3">100000</c6>

    <c6 m="3" s="2">500000</c6>

    <c6 m="3" s="3">90000</c6>

    <c6 m="3" s="4">102739.73</c6>

    <c6 m="3" s="5">737260.27</c6>

    <c6 m="4">38656</c6>

    <c7></c7>

    <c7 m="2">AABILL1731431Q8C</c7>

    <c7 m="3">AABILL17318GL714</c7>

    <c7 m="4">AABILL17318GL714</c7>

</row>

I would like to convert it into something like this:

   

DATEBALANCEAMOUNT
201711101000000001329.CREDITAA.USD50000000*USD50000000
20171110ACCOUNT.PAYACCOUNT50000000
20171114CONTINSUFEE.DUECONTINSUFEE100000
20171114CONTMGMTFEE.DUECONTMGMTFEE500000
20171114CONTMGMTFEE-VAT.DUECONTMGMTFEE-VAT90000
20171114PRINCIPALINT.DUEPRINCIPALINT102739.73
20171114ACCOUNT.DUEACCOUNT737260.27
20171114ACCOUNT.DUEACCOUNT38656

I did ask something a bit similar, but feeling a bit lost with this particular case.

Getting XMLRecord value by position

Your help would be greatly appreciated.

This post has been answered by odie_63 on Jun 9 2018
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 7 2018
Added on Jun 8 2018
5 comments
6,800 views