Loop an XMLTABLE in PL/SQL Oracle 12c

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.
Hakan KAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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.
Hakan KAuthor 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>
slightwv (䄆 Netminder) Commented:
Can we get your expected results from that test XML?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hakan KAuthor 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;
Hakan KAuthor 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.
slightwv (䄆 Netminder) Commented:
What is the maximum number of 'MAINPARTEXTENSION's you can have?
slightwv (䄆 Netminder) 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hakan KAuthor Commented:
Hi the selevt works but I have no data in the fields.
Hakan KAuthor Commented:
Hi slightwv (䄆 Netminder), thank you so much, your solution was right. I solved it.  Positional Parameters was the solution.
Hakan KAuthor Commented:
Thank you so much, you saved my time
slightwv (䄆 Netminder) Commented:
Glad to help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XMLTABLE

From novice to tech pro — start learning today.