SQL & PL/SQL

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!

Repeating xml nodes with xmltable

TheManWhoSoldTheWorldNov 3 2016 — edited Nov 21 2016

Hi I have looked around and been unable to come up with a solution. If any of you can provide a hint to help me find the solution I would really appreciate it.

FILE SNIPPET:

<Pmt>

<pmt:Payments>

<pmt:PayInfo>

<pmt:Single>

<pmt:SingExtRef>HH00000066530</pmt:SingExtRef>

<pmt:SingSequence>0001</pmt:SingSequence>

<pmt:SingAddInfo>

<cmn:AddInfoCmpType>IndivID</cmn:AddInfoCmpType>

<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>

<cmn:AddInfoCmpText>30UAT23203</cmn:AddInfoCmpText>

</pmt:SingAddInfo>

<pmt:SingAddInfo>

<cmn:AddInfoCmpType>Transaction Reference</cmn:AddInfoCmpType>

<cmn:AddInfoCmpText>062001180000553</cmn:AddInfoCmpText>

</pmt:SingAddInfo>

<pmt:SingAddInfo>

<cmn:AddInfoCmpType>EntryDesc</cmn:AddInfoCmpType>

<cmn:AddInfoCmpText>TRADE PYMT</cmn:AddInfoCmpText>

</pmt:SingAddInfo>

<pmt:SingAddInfo>

<cmn:AddInfoCmpType>ACH Company ID</cmn:AddInfoCmpType>

<cmn:AddInfoCmpText>1444444444</cmn:AddInfoCmpText>

</pmt:SingAddInfo>

<pmt:SingAddInfo>

<cmn:AddInfoCmpType>PmtRelInfo0001</cmn:AddInfoCmpType>

<cmn:AddInfoCmpText>IV</cmn:AddInfoCmpText>

<cmn:AddInfoCmpText>UAT9999_00001</cmn:AddInfoCmpText>

<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>

<cmn:AddInfoCmpText>100.01</cmn:AddInfoCmpText>

<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>

</pmt:SingAddInfo>

<pmt:SingAddInfo>

<cmn:AddInfoCmpType>PmtRelInfo0002</cmn:AddInfoCmpType>

<cmn:AddInfoCmpText>IV</cmn:AddInfoCmpText>

<cmn:AddInfoCmpText>UAT9999_00002</cmn:AddInfoCmpText>

<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>

<cmn:AddInfoCmpText>100.01</cmn:AddInfoCmpText>

<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>

</pmt:SingAddInfo>

</pmt:Single>

</pmt:PayInfo>

</pmt:Payments>

</Pmt>

</xyzcompany>

CODE :

SELECT sing_d1.sing_info_type,

sing_d1.sing_info_text1,

sing_d1.sing_info_text2,

sing_d1.sing_info_text3,

sing_d1.sing_info_text4,

sing_d1.sing_info_text5,

sing_d1.sing_info_text6,

sing_d1.sing_info_text7,

sing_d1.sing_info_text8,

sing_d1.sing_info_text9,

sing_d1.sing_info_text10,

sing_d1.sing_info_text11,

sing_d1.sing_info_text12,

sing_d1.sing_info_text13,

sing_d1.sing_info_text14,

sing_d1.sing_info_text15,

sing_d1.sing_info_text16,

sing_d1.sing_info_text17,

sing_d1.sing_info_text18,

sing_d1.sing_info_text19,

sing_d1.sing_info_text20,

sing_d1.sing_info_text21,

sing_d1.sing_info_text22,

sing_d1.sing_info_text23,

sing_d1.sing_info_text24,

sing_d1.sing_info_text25,

sing_d1.sing_info_text26,

sing_d1.sing_info_text27,

sing_d1.sing_info_text28,

sing_d1.sing_info_text29,

sing_d1.sing_info_text30,

sing_d1.sing_info_text31,

sing_d1.sing_info_text32,

sing_d1.sing_info_text33,

sing_d1.sing_info_text34,

sing_d1.sing_info_text35

FROM XMLTABLE (

xmlnamespaces (

'http://www.xyzcompany.com/Canonical' AS "bbva",

'http:/xyzcompany.com/PmtInfo' AS "pmt",

'http://xyzcompany.com/CommonTypes' AS "cmn",

'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),

':xyzcompany:xyzcompany/xyzcompany:Pmt/pmt:Payments/pmt:PayInfo/pmt:Single/pmt:SingAddInfo'

PASSING payXml

COLUMNS sing_info_type CLOB

PATH /pmt:SingAddInfo/cmn:AddInfoCmpType',

sing_info_text1 CLOB

PATH /pmt:SingAddInfo/cmn:AddInfoCmpText[1]',

sing_info_text2 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[2]',

sing_info_text3 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[3]',

sing_info_text4 CLOB

PATH /pmt:SingAddInfo/cmn:AddInfoCmpText[4]',

sing_info_text5 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[5]',

sing_info_text6 CLOB

PATH /pmt:SingAddInfo/cmn:AddInfoCmpText[6]',

sing_info_text7 CLOB

PATH /pmt:SingAddInfo/cmn:AddInfoCmpText[7]',

sing_info_text8 CLOB

PATH /pmt:SingAddInfo/cmn:AddInfoCmpText[8]',

sing_info_text9 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[9]',

sing_info_text10 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[10]',

sing_info_text11 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[11]',

sing_info_text12 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[12]',

sing_info_text13 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[13]',

sing_info_text14 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[14]',

sing_info_text15 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[15]',

sing_info_text16 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[16]',

sing_info_text17 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[17]',

sing_info_text18 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[18]',

sing_info_text19 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[19]',

sing_info_text20 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[20]',

sing_info_text21 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[21]',

sing_info_text22 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[22]',

sing_info_text23 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[23]',

sing_info_text24 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[24]',

sing_info_text25 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[25]',

sing_info_text26 CLOB

PATH 'pmt:Single/pmt:SingAddInfo/cmn:AddInfoCmpText[26]',

sing_info_text27 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[27]',

sing_info_text28 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[28]',

sing_info_text29 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[29]',

sing_info_text30 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[30]',

sing_info_text31 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[31]',

sing_info_text32 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[32]',

sing_info_text33 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[33]',

sing_info_text34 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[34]',

sing_info_text35 CLOB

PATH '/pmt:SingAddInfo/cmn:AddInfoCmpText[35]'

) sing_d1;

The select only seems to pick up the first occurence of the <SINGADDINFO> tag which in the above example is the "INDIVID". Does the xpath need altering to make it realize there are repeating nodes? Any help is really appreciated.

Locked Post

New comments cannot be posted to this locked post.

Locked on Dec 19 2016

Added on Nov 3 2016

25 comments

1,553 views