Parsing unknown number nodes of a XML with namespace in PL/SQL procedure?

Question

I have a xml_data which I am passing into procedure-

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns2:a xmlns:ns2="http://www.sbc.com/iag/schemas/adapters" xmlns="http://www.sbc.com/iag/schemas/core">
     <ns2:b>

        <ns2:OrderNumber>99995</ns2:OrderNumber>
        <ns2:ServiceOrderNumber>88888</ns2:ServiceOrderNumber>

    </ns2:b>
    <ns2:b>

        <ns2:OrderNumber>99699</ns2:OrderNumber>
        <ns2:ServiceOrderNumber>88888</ns2:ServiceOrderNumber>

    </ns2:b>
    <ns2:b>

        <ns2:OrderNumber>69999</ns2:OrderNumber>
        <ns2:ServiceOrderNumber>88888</ns2:ServiceOrderNumber>

    </ns2:b
    <ns2:b>

        <ns2:OrderNumber>67999</ns2:OrderNumber>
        <ns2:ServiceOrderNumber>88888</ns2:ServiceOrderNumber>

    </ns2:b>
</ns2:a>

node

ns2:b

can be repeated any number of times and is not fixed. How do i fetch this

ns2:OrderNumber

from this xml data in oracle procedure.I am able to fetch the value if

ns2:b(ns2:OrderNumber)

occurs only one time through below code-

select extractValue(
  xml_data, 
   xmlns="http://www.sbc.com/iag/schemas/core"'
)   '/ns2:a/ns2:b/ns2:OrderNumber/text()',
  'xmlns:ns2="http://www.sbc.com/iag/schemas/adapters",
into order_number from dual;

  dbms_output.put_line('FailedRetry -' ||order_number);

But how do i keep iterating until I get all the values of that node

in XML.I have tried different solution posted on this site,but none worked.

Noel · Accepted Answer · 2015-04-02 09:33:31Z

1

Use XMLTABLE.

select y.* 
from dual,
xmltable( xmlnamespaces(default 'http://www.sbc.com/iag/schemas/core', 'http://www.sbc.com/iag/schemas/adapters' as "ns2"), 'ns2:a/ns2:b'
passing xml_data
columns OrderNumber varchar2(10) path 'ns2:OrderNumber',
    ServiceOrderNumber varchar2(10) path 'ns2:ServiceOrderNumber'
) y;

Sample query:

SQL> with x(xml_data) as (
        select xmltype('<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<ns2:a xmlns:ns2="http://www.sbc.com/iag/schemas/adapters" xmlns="http://www.sbc.com/iag/schemas/core">
     <ns2:b>
        <ns2:OrderNumber>99995</ns2:OrderNumber>
        <ns2:ServiceOrderNumber>88888</ns2:ServiceOrderNumber>
    </ns2:b>
    <ns2:b>
        <ns2:OrderNumber>99699</ns2:OrderNumber>
        <ns2:ServiceOrderNumber>88888</ns2:ServiceOrderNumber>
    </ns2:b>
    <ns2:b>
        <ns2:OrderNumber>69999</ns2:OrderNumber>
        <ns2:ServiceOrderNumber>88888</ns2:ServiceOrderNumber>
    </ns2:b>
    <ns2:b>
        <ns2:OrderNumber>67999</ns2:OrderNumber>
        <ns2:ServiceOrderNumber>88888</ns2:ServiceOrderNumber>
    </ns2:b>
</ns2:a>')
from dual
)
select y.* 
from x,
xmltable( xmlnamespaces(default 'http://www.sbc.com/iag/schemas/core', 'http://www.sbc.com/iag/schemas/adapters' as "ns2"), 'ns2:a/ns2:b'
passing xml_data
columns OrderNumber varchar2(10) path 'ns2:OrderNumber',
        ServiceOrderNumber varchar2(10) path 'ns2:ServiceOrderNumber'
) y;

ORDERNUMBE SERVICEORD
---------- ----------
99995      88888
99699      88888
69999      88888
67999      88888

Elapsed: 00:00:00.03
SQL>

If you want to iterate each record, use it in a cursor.

decare
    cursor c1 is select y.* 
    from dual,
    xmltable( xmlnamespaces(default 'http://www.sbc.com/iag/schemas/core', 'http://www.sbc.com/iag/schemas/adapters' as "ns2"), 'ns2:a/ns2:b'
    passing xml_data
    columns OrderNumber varchar2(10) path 'ns2:OrderNumber',
        ServiceOrderNumber varchar2(10) path 'ns2:ServiceOrderNumber'
    ) y;
begin
    for i in c1
    loop
        dbms_output.put_line('FailedRetry -' ||i.OrderNumber || ' '|| i.ServiceOrderNumber);
    end loop;
end;

edited Apr 2, 2015 at 9:33

answered Apr 2, 2015 at 4:25

Noel

10.6k30 gold badges53 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Rajat Over a year ago

thanks a lot Eat,but when I tried to compile its showing error-Error(14,2): PLS-00428: an INTO clause is expected in this SELECT statement,i'e at with x(xml_data) as line.

Noel Over a year ago

if u are using it in plsql, you should use it in a cursor.

Rajat Over a year ago

but 'with' is also supported in oracle,isint it?It would be really helpful if you could show me how can i use cursor here.

Collectives™ on Stack Overflow

Parsing unknown number nodes of a XML with namespace in PL/SQL procedure?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related