How to Query Nested XML in Oracle?

Question

I need to query/output the EMPLOYEE nodes which is repeating multiple times. My query is giving me error that it is not expecting multi-item sequence. Eventually I would need to loop the repeated EMPLOYEE data in PLSQL. But First I would like to at least be able to run the SQL to produce the desire result. I ran out of ideas.

See my query and XML below.

    select
    xmlt.recordid,
    xmlt.mfirstname,
    xmlt.mlastname,
    xmlt.efirstname,
    xmlt.elastname,
    FROM   
    test_xml x,
    xmltable(xmlnamespaces('http:testxml.com' AS "ns"), '//ns:abccomp' 
    passing            xmltype(x.xml) 
    columns
    recordid varchar2(200) path '//ns:recordid/ns:identification',
    mfirstname varchar2(200)  path '//ns:accounting/ns:manager/ns:personname/ns:firstname',
    mlastname varchar2(200)  path '//ns:accounting/ns:manager/ns:personname/ns:lastname',
    efirstname varchar2(200)  path '//ns:accounting/ns:employee/ns:personname/ns:firstname',
    elastname varchar2(200)  path '//ns:accounting/ns:employee/ns:personname/ns:lastname'
    )Xmlt;


        <ns:wrap
xmlns:ns="http:testxml.com">
<ns:body>
    <ns:abccomp>
        <ns:recordid>
            <ns:identification>955613218915</ns:identification>
        </ns:recordid>
        <ns:accounting>
            <ns:manager>
                <ns:personname>
                    <ns:firstname>frank</ns:firstname>
                    <ns:lastname>phillips</ns:lastname>
                </ns:personname>
            </ns:manager>
            <ns:employee>
                <ns:personname>
                    <ns:firstname>jimmy</ns:firstname>
                    <ns:lastname>smith</ns:lastname>
                </ns:personname>
            </ns:employee>
            <ns:employee>
                <ns:personname>
                    <ns:firstname>yuri</ns:firstname>
                    <ns:lastname>oga</ns:lastname>
                </ns:personname>
            </ns:employee>
                <ns:personname>
                    <ns:firstname>amanda</ns:firstname>
                    <ns:lastname>hicks</ns:lastname>
                </ns:personname>
            </ns:employee>
        </ns:accounting>
    </ns:abccomp>
</ns:body>

</ns:wrap>

MT0 · Accepted Answer · 2020-10-08 23:06:34Z

You can use multiple XMLTABLEs:

SELECT x.recordid,
       x.mfirstname,
       x.mlastname,
       e.*
FROM   test_xml t
       CROSS APPLY XMLTABLE(
         xmlnamespaces('http:testxml.com' AS "ns"),
         '//ns:wrap/ns:body/ns:abccomp' 
         PASSING xmltype(t.xml) 
         COLUMNS
           recordid   varchar2(200) path '//ns:recordid/ns:identification',
           mfirstname varchar2(200) path '//ns:accounting/ns:manager/ns:personname/ns:firstname',
           mlastname  varchar2(200) path '//ns:accounting/ns:manager/ns:personname/ns:lastname',
           accounting XMLTYPE       path '//ns:accounting'
       ) x
       CROSS APPLY XMLTABLE(
         xmlnamespaces('http:testxml.com' AS "ns"),
         '//ns:accounting/ns:employee' 
         PASSING x.accounting
         COLUMNS
           efirstname varchar2(200) path '//ns:personname/ns:firstname',
           elastname  varchar2(200) path '//ns:personname/ns:lastname'
       ) e;

Which outputs:

RECORDID     | MFIRSTNAME | MLASTNAME | EFIRSTNAME | ELASTNAME
:----------- | :--------- | :-------- | :--------- | :--------
955613218915 | frank      | phillips  | jimmy      | smith    
955613218915 | frank      | phillips  | yuri       | oga      
955613218915 | frank      | phillips  | amanda     | hicks

For your test data (which was missing an opening <ns:employee> and a closing </ns:wrap> tag):

INSERT INTO test_xml ( xml ) VALUES ( '<ns:wrap xmlns:ns="http:testxml.com">
<ns:body>
    <ns:abccomp>
        <ns:recordid>
            <ns:identification>955613218915</ns:identification>
        </ns:recordid>
        <ns:accounting>
            <ns:manager>
                <ns:personname>
                    <ns:firstname>frank</ns:firstname>
                    <ns:lastname>phillips</ns:lastname>
                </ns:personname>
            </ns:manager>
            <ns:employee>
                <ns:personname>
                    <ns:firstname>jimmy</ns:firstname>
                    <ns:lastname>smith</ns:lastname>
                </ns:personname>
            </ns:employee>
            <ns:employee>
                <ns:personname>
                    <ns:firstname>yuri</ns:firstname>
                    <ns:lastname>oga</ns:lastname>
                </ns:personname>
            </ns:employee>
            <ns:employee>
                <ns:personname>
                    <ns:firstname>amanda</ns:firstname>
                    <ns:lastname>hicks</ns:lastname>
                </ns:personname>
            </ns:employee>
        </ns:accounting>
    </ns:abccomp>
</ns:body>
</ns:wrap>' );

db<>fiddle here

Collectives™ on Stack Overflow

How to Query Nested XML in Oracle?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related