0

I have an XML file which I process by extracting tag values and inserting them into a table. The problem comes when the file has multiple entries of the same type, like the one below. Is there any way to loop between the xml tag or between the Document tag. The problem is really simple (in theory), but I am not very experienced with transforming XML files with PL/SQL. Do you have any suggestions ?

 <?xml version="1.0" encoding="UTF-8" ?>
 <Document xmlns="urn:iso:std:iso:20022:tech:xsd:ps.002.002.004"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <BlaBluBli>
     <GrpHdr>
       <MsgId>GJ1</MsgId>
       <Acbm>2010-09-22T14:47:05</Acbm>
       <NbOfTxs>1</NbOfTxs>
       <OtherTag >1.01</OtherTag>
       <ThisTag>2015-02-09</ThisTag>
       </GrpHdr>
   </BlaBluBli>
 </Document>
 <?xml version="1.0" encoding="UTF-8" ?>
 <Document xmlns="urn:iso:std:iso:20022:tech:xsd:ps.002.002.004"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <BlaBluBli>
     <GrpHdr>
       <MsgId>GJ2</MsgId>
       <Acbm>2010-09-22T14:47:05</Acbm>
       <NbOfTxs>1</NbOfTxs>
       <OtherTag >2.01</OtherTag>
       <ThisTag>2015-02-09</ThisTag>
       </GrpHdr>
   </BlaBluBli>
 </Document>
 <?xml version="1.0" encoding="UTF-8" ?>
 <Document xmlns="urn:iso:std:iso:20022:tech:xsd:ps.002.002.004"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <BlaBluBli>
     <GrpHdr>
       <MsgId>GJ3</MsgId>
       <Acbm>2010-09-22T14:47:05</Acbm>
       <NbOfTxs>1</NbOfTxs>
       <OtherTag >3.01</OtherTag>
       <ThisTag>2015-02-09</ThisTag>
        </GrpHdr>
   </BlaBluBli>
 </Document>

The result should be three files.

1-

 <?xml version="1.0" encoding="UTF-8" ?>
 <Document xmlns="urn:iso:std:iso:20022:tech:xsd:ps.002.002.004"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <BlaBluBli>
     <GrpHdr>
       <MsgId>GJ1</MsgId>
       <Acbm>2010-09-22T14:47:05</Acbm>
       <NbOfTxs>1</NbOfTxs>
       <OtherTag >1.01</OtherTag>
       <ThisTag>2015-02-09</ThisTag>
       </GrpHdr>
   </BlaBluBli>
 </Document>

2-

 <?xml version="1.0" encoding="UTF-8" ?>
 <Document xmlns="urn:iso:std:iso:20022:tech:xsd:ps.002.002.004"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <BlaBluBli>
     <GrpHdr>
       <MsgId>GJ2</MsgId>
       <Acbm>2010-09-22T14:47:05</Acbm>
       <NbOfTxs>1</NbOfTxs>
       <OtherTag >2.01</OtherTag>
       <ThisTag>2015-02-09</ThisTag>
       </GrpHdr>
   </BlaBluBli>
 </Document>

3-

 <?xml version="1.0" encoding="UTF-8" ?>
 <Document xmlns="urn:iso:std:iso:20022:tech:xsd:ps.002.002.004"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <BlaBluBli>
     <GrpHdr>
       <MsgId>GJ3</MsgId>
       <Acbm>2010-09-22T14:47:05</Acbm>
       <NbOfTxs>1</NbOfTxs>
       <OtherTag >3.01</OtherTag>
       <ThisTag>2015-02-09</ThisTag>
        </GrpHdr>
   </BlaBluBli>
 </Document>
erni313
  • 193
  • 1
  • 2
  • 10
  • 4
    Your file contains three XML documents; the overall file isn't itself valid XML, so you can't use XML tools like XQuery to process it. How is the larger file getting to you, how are you reading it now, and can it be split into separate XML documents before you have to handle it? If not you'd have to treat it as a CLOB and split it up with substr/instr or regex, or read it line by line and detect the marker. – Alex Poole Feb 09 '15 at 16:46
  • I would say: Oracle XML DB (http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb01int.htm#ADXDB0100) – Cyryl1972 Feb 09 '15 at 19:40

0 Answers0