Loop an XMLTABLE in PL/SQL Oracle 12c

Hakan K
Hakan K used Ask the Experts™
on
I have stored my xml in a table. Select and insert in a new table works.

I save the xml file with sqloader as xmltype as HUGECLOB. I can select with xpath the data and insert it in a new table.

My Table structure is:
MAINPART | MAINPART2 | MAINPART3 | MAINPARTEXTENSION1 | MAINPARTEXTENSION2 | MAINPARTEXTENSION3 | MAINPARTEXTENSION4

I can map everything from the xml file except the mainpartextension. The select gives me a singleton error. How can I handle with Select or PL/SQL with a Loop or another xpath xpression to laod the xml tag MAINPARTEXTENSION into MAINPARTEXTENSION1 , MAINPARTEXTENSION2, MAINPARTEXTENSION3,....

Please help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If you are using XMLTABLE in the select and you have nested nodes, you might need another XMLTABLE as part of the query to return the individual nodes.

If you can create a small sample XML file that mimics what you are trying to do, post it with your expected results and we can provide the SQL.

Remember, just a small made up sample.  We don't want your entire XML file.

Author

Commented:
Here is my XML FILE:

<TESTUS>
    <TEST>
        <MAINPART>
            <GO1>12</GO1>
            <GO2>13</GO2>
        </MAINPART>
        <MAINPART2>
             <GO1>12</GO1>
            <GO2>13</GO2>
        </MAINPART2>
        <MAINPART3>
          <GO1>12</GO1>
            <GO2>13</GO2>
        </MAINPART3>
        <MAINPARTEXTENSION>
           <GO1>12</GO1>
            <GO2>13</GO2>
        </MAINPARTEXTENSION>
        <MAINPARTEXTENSION>
           <GO1>12</GO1>
            <GO2>13</GO2>
        </MAINPARTEXTENSION>
            <MAINPARTEXTENSION>
           <GO1>12</GO1>
            <GO2>13</GO2>
        </MAINPARTEXTENSION>
            <MAINPARTEXTENSION>
           <GO1>12</GO1>
            <GO2>13</GO2>
        </MAINPARTEXTENSION>
    </TEST>
</TESTUS>
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Can we get your expected results from that test XML?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
This is my select and I get the error: ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

INSERT INTO MYTABLE
SELECT x.*
     FROM XMLDATA t,
          XMLTABLE ('/TESTUS/TEST'
                    PASSING t.XMLTEST
                    COLUMNS MAINPART VARCHAR2(255) PATH 'MAINPART/GO1',
                                          MAINPART VARCHAR2(255) PATH 'MAINPART/GO2',
                            MAINPART2 VARCHAR2(255) PATH 'MAINPART2/GO1',
                            MAINPART2 VARCHAR2(255) PATH 'MAINPART2/GO2',
                            MAINPART3 VARCHAR2(255) PATH 'MAINPART3/GO1',
                                          MAINPART3 VARCHAR2(255) PATH 'MAINPART3/GO2',
                                          MAINPARTEXTENSION VARCHAR2(255) PATH 'MAINPARTEXTENSION/GO1',
                                          MAINPARTEXTENSION VARCHAR2(255) PATH 'MAINPARTEXTENSION/GO2',
                            ) x;

Author

Commented:
So I expect to get all the xml tags into one row in my table, I have the problem with the MAINPARTEXTENSION becaus it comes 4 times and I dont know how to get all in one row.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
What is the maximum number of 'MAINPARTEXTENSION's you can have?
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
try positional parameters.

This gets the first two to give you the idea.  Just repeat it for all the possible entries:
SELECT x.*
     FROM xmldata t,
          XMLTABLE ('/TESTUS/TEST'
                    PASSING t.XMLTEST
                    COLUMNS MAINPARTa VARCHAR2(255) PATH 'MAINPART/GO1', 
                            MAINPARTb VARCHAR2(255) PATH 'MAINPART/GO2', 
                            MAINPART2a VARCHAR2(255) PATH 'MAINPART2/GO1',
                            MAINPART2b VARCHAR2(255) PATH 'MAINPART2/GO2',
                            MAINPART3a VARCHAR2(255) PATH 'MAINPART3/GO1',
                            MAINPART3b VARCHAR2(255) PATH 'MAINPART3/GO2',
                            MAINPARTEXTENSION1a VARCHAR2(255) PATH 'MAINPARTEXTENSION[1]/GO1',
                            MAINPARTEXTENSION1b VARCHAR2(255) PATH 'MAINPARTEXTENSION[1]/GO2',
                            MAINPARTEXTENSION2a VARCHAR2(255) PATH 'MAINPARTEXTENSION[2]/GO1',
                            MAINPARTEXTENSION2b VARCHAR2(255) PATH 'MAINPARTEXTENSION[2]/GO2'
                            ) x; 
/

Open in new window

Author

Commented:
Hi the selevt works but I have no data in the fields.

Author

Commented:
Hi slightwv (䄆 Netminder), thank you so much, your solution was right. I solved it.  Positional Parameters was the solution.

Author

Commented:
Thank you so much, you saved my time
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Glad to help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial