plsqloracle11gextractoracle-xml-db

Extract Node Values within a CLOB from XMLType in PL/SQL and not a Table


Assuming I have the following XML data assigned to a local variable in PL/SQL of type CLOB, i.e:

l_clob := ‘<xml>
  <ticket_order>
    <table_no>1<table_no/>
    <waiter>Jack<waiter/>
    <total_people>12<total_people/>
  </ticket_order>
  <ticket_order>
    <table_no>4<table_no/>
    <waiter>Jackie<waiter/>
    <total_people>3<total_people/>
  </ticket_order>
  <ticket_order>
    <table_no>2<table_no/>
    <waiter>Sally<waiter/>
    <total_people>2<total_people/>
  </ticket_order>
  <ticket_order>
    <table_no>11<table_no/>
    <waiter>Mike<waiter/>
    <total_people>6<total_people/>
  </ticket_order>
</xml>';

So I am not storing this XML in a table column as I would like to process within my PL/SQL package procedure alone.

Using the above l_clob variable, I then perform the following:

retrieve_all_table_no(p_response => SYS.XMLTYPE.CREATEXML(l_clob));

Now within my procedure:

retrieve_all_table_no(p_response in XMLType)

How can I extract on a per row basis, all node values within <table_no> ?

Again, this is not coming from a table, all done within PL/SQL on the fly.

I expect to be able to access each individual value, for me to process, i.e.:

1
4
2
11

I can't use extract as this will concatenate all values.


Solution

  • Solved this as follows where I would change this when adding to my procedure but this achieved the result I was after.

    l_xmlType   XMLTYPE;
    
    l_xmltype := SYS.XMLTYPE.CREATEXML(l_clob);  -- l_clob value from above
    
    select x.*
    from xmltable('xml/ticket_order'
        passing l_xmltype
        columns table_no number path 'table_no'
    ) x;