INSERT ALL master/detail data from XML

This is something I actually made for a quiz on PL/SQL Challenge, but I think the technique could be useful for others as well :-)

The idea is you may have some master/detail data (in this case orders and orderlines) for which you get XML with such data that needs to be inserted into two relational tables. Many might be tempted to parse the XML client side or in PL/SQL, loop through the data, and then insert the orders and lines row by row (or perhaps bulk insert from arrays.)

But it can be done in a single statement if you combine XMLTABLE function to read the XML relationally and INSERT ALL statement to do a multi-table insert.

Let's create a couple of tables for orders and orderlines:

SQL> create table orders
  2  (
  3   order_id    integer primary key
  4   , customer    varchar2(20)
  5   , state     varchar2(20)
  6  )
  7  /

Table created.

SQL> create table orderlines
  2  (
  3   order_id    integer references orders (order_id)
  4   , line_no     integer
  5   , item     varchar2(20)
  6   , quantity    number
  7  )
  8  /

Table created.

We will receive xml files from external source with order data.

In production that will probably be loaded via CLOB or BFILE or other method. For test we setup a simple VARCHAR2 bind variable containing the XML.

SQL> variable received_xml varchar2(4000)
SQL> 
SQL> begin
  2   :received_xml :=
  3  '<orders>
  4    <order id="100">
  5    <customer>Smith</customer>
  6    <state>OHIO</state>
  7    <lines>
  8      <line no="1">
  9        <item>Mouse</item>
 10        <qty>3</qty>
 11      </line>
 12      <line no="2">
 13        <item>Monitor</item>
 14        <qty>2</qty>
 15      </line>
 16    </lines>
 17    </order>
 18    <order id="102">
 19    <customer>Jackson</customer>
 20    <state>TEXAS</state>
 21    <lines>
 22      <line no="2">
 23        <item>Monitor</item>
 24        <qty>1</qty>
 25      </line>
 26      <line no="4">
 27        <item>Keyboard</item>
 28        <qty>2</qty>
 29      </line>
 30      <line no="6">
 31        <item>RJ-45 Cable</item>
 32        <qty>6</qty>
 33      </line>
 34    </lines>
 35    </order>
 36  </orders>
 37  ';
 38  end;
 39  /

PL/SQL procedure successfully completed.

XMLTABLE can be used to read XML data as relational data. For example:

SQL> select   xmlorders.order_id
  2       , xmlorders.customer
  3       , xmlorders.state
  4    from xmltable(
  5     '/Orders/Order'
  6     passing xmltype(:received_xml)
  7     columns order_id integer      path '@Id'
  8           , customer varchar2(20) path 'Customer'
  9           , state   varchar2(20) path 'State'
 10         ) xmlorders
 11  /

ORDER_ID CUSTOMER STATE                                                         
-------- -------- -----                                                         
     100 Smith    OHIO                                                          
     102 Jackson  TEXAS                                                         

'/Orders/Order' is the XQuery path that tells Oracle we want all Order child items of the Orders root item. PASSING is the actual XML data, where XMLTYPE is a constructor to construct an XMLTYPE object from the VARCHAR2 bind-variable. COLUMNS then defines the relational columns and PATH shows where in the XML the data for that column can be found. Note that @Id denotes that Id is an attribute of the Order item, while Customer and State are child items.

XMLTABLE can of course also find the order lines at the lower levels of the XML. For example:

SQL> select   xmllines.line_no
  2       , xmllines.item
  3       , xmllines.quantity
  4    from xmltable(
  5     '/Orders/Order/Lines/Line'
  6     passing xmltype(:received_xml)
  7     columns line_no  integer      path '@No'
  8           , item   varchar2(20) path 'Item'
  9           , quantity number       path 'Qty'
 10         ) xmllines
 11  /

 LINE_NO ITEM        QUANTITY                                                   
-------- ----------- --------                                                   
       1 Mouse              3                                                   
       2 Monitor            2                                                   
       2 Monitor            1                                                   
       4 Keyboard           2                                                   
       6 RJ-45 Cable        6                                                   

The problem with the above XMLTABLE expression is, that it does not identify the order id for each line, as that id is only available at the higher level of XML. But it is possible to use XMLTABLE twice in order to solve that:

SQL> select   xmlorders.order_id
  2       , xmllines.line_no
  3       , xmllines.item
  4       , xmllines.quantity
  5    from xmltable(
  6     '/Orders/Order'
  7     passing xmltype(:received_xml)
  8     columns order_id integer      path '@Id'
  9           , lines   xmltype      path 'Lines'
 10         ) xmlorders
 11       , xmltable(
 12     '/Lines/Line'
 13     passing xmlorders.lines
 14     columns line_no  integer      path '@No'
 15           , item   varchar2(20) path 'Item'
 16           , quantity number       path 'Qty'
 17         ) xmllines
 18  /

ORDER_ID  LINE_NO ITEM        QUANTITY                                          
-------- -------- ----------- --------                                          
     100        1 Mouse              3                                          
     100        2 Monitor            2                                          
     102        2 Monitor            1                                          
     102        4 Keyboard           2                                          
     102        6 RJ-45 Cable        6                                          

The first XMLTABLE gets the order_id and then also a column lines of XMLTYPE that will contain the XML of each orders Lines item. That in turn is passed to the second XMLTABLE using PASSING, and so that retrieves the columns of each line for each order.

Inserting the data into the relational tables can then be done in a two-pass operation, first the orders, then the lines:

SQL> insert into orders
  2  select   xmlorders.order_id
  3       , xmlorders.customer
  4       , xmlorders.state
  5    from xmltable(
  6     '/Orders/Order'
  7     passing xmltype(:received_xml)
  8     columns order_id integer      path '@Id'
  9           , customer varchar2(20) path 'Customer'
 10           , state   varchar2(20) path 'State'
 11         ) xmlorders
 12  /

2 rows created.

SQL> insert into orderlines
  2  select   xmlorders.order_id
  3       , xmllines.line_no
  4       , xmllines.item
  5       , xmllines.quantity
  6    from xmltable(
  7     '/Orders/Order'
  8     passing xmltype(:received_xml)
  9     columns order_id integer      path '@Id'
 10           , lines   xmltype      path 'Lines'
 11         ) xmlorders
 12       , xmltable(
 13     '/Lines/Line'
 14     passing xmlorders.lines
 15     columns line_no  integer      path '@No'
 16           , item   varchar2(20) path 'Item'
 17           , quantity number       path 'Qty'
 18         ) xmllines
 19  /

5 rows created.

Or it can be done in one call using the INSERT ALL syntax for inserting into two tables simultaneously.

SQL> insert all
  2  when seq = 1
  3  then
  4     into orders
  5   values (order_id
  6         , customer
  7         , state)
  8  when 1 = 1
  9  then
 10     into orderlines
 11   values (order_id
 12         , line_no
 13         , item
 14         , quantity)
 15  select   xmlorders.order_id
 16       , xmlorders.customer
 17       , xmlorders.state
 18       , xmllines.line_no
 19       , xmllines.item
 20       , xmllines.quantity
 21       , xmllines.seq
 22    from xmltable(
 23     '/Orders/Order'
 24     passing xmltype(:received_xml)
 25     columns order_id integer      path '@Id'
 26           , customer varchar2(20) path 'Customer'
 27           , state   varchar2(20) path 'State'
 28           , lines   xmltype      path 'Lines'
 29         ) xmlorders
 30       , xmltable(
 31     '/Lines/Line'
 32     passing xmlorders.lines
 33     columns line_no  integer      path '@No'
 34           , item   varchar2(20) path 'Item'
 35           , quantity number       path 'Qty'
 36           , seq   for ordinality
 37         ) xmllines
 38  /

7 rows created.

See how the data now are nicely inserted in the tables :-)

SQL> select   order_id
  2       , customer
  3       , state
  4    from orders
  5  order by order_id
  6  /

ORDER_ID CUSTOMER STATE                                                         
-------- -------- -----                                                         
     100 Smith    OHIO                                                          
     102 Jackson  TEXAS                                                         

SQL> select   order_id
  2       , line_no
  3       , item
  4       , quantity
  5    from orderlines
  6  order by order_id, line_no
  7  /

ORDER_ID  LINE_NO ITEM        QUANTITY                                          
-------- -------- ----------- --------                                          
     100        1 Mouse              3                                          
     100        2 Monitor            2                                          
     102        2 Monitor            1                                          
     102        4 Keyboard           2                                          
     102        6 RJ-45 Cable        6                                          

But what if our xml data was simpler and did not contain any id information?

SQL> begin
  2   :received_xml :=
  3  '<Orders>
  4    <Order>
  5    <Customer>Smith</Customer>
  6    <State>OHIO</State>
  7    <Lines>
  8      <Line>
  9        <Item>Mouse</Item>
 10        <Qty>3</Qty>
 11      </Line>
 12      <Line>
 13        <Item>Monitor</Item>
 14        <Qty>2</Qty>
 15      </Line>
 16    </Lines>
 17    </Order>
 18    <Order>
 19    <Customer>Jackson</Customer>
 20    <State>TEXAS</State>
 21    <Lines>
 22      <Line>
 23        <Item>Monitor</Item>
 24        <Qty>1</Qty>
 25      </Line>
 26      <Line>
 27        <Item>Keyboard</Item>
 28        <Qty>2</Qty>
 29      </Line>
 30      <Line>
 31        <Item>RJ-45 Cable</Item>
 32        <Qty>6</Qty>
 33      </Line>
 34    </Lines>
 35    </Order>
 36  </Orders>
 37  ';
 38  end;
 39  /

PL/SQL procedure successfully completed.

These data has no order_id and no line_no:

SQL> select   xmlorders.customer
  2       , xmlorders.state
  3       , xmlorders.orderseq
  4       , xmllines.item
  5       , xmllines.quantity
  6       , xmllines.lineseq
  7    from xmltable(
  8     '/Orders/Order'
  9     passing xmltype(:received_xml)
 10     columns customer varchar2(20) path 'Customer'
 11           , state   varchar2(20) path 'State'
 12           , lines   xmltype      path 'Lines'
 13           , orderseq for ordinality
 14         ) xmlorders
 15       , xmltable(
 16     '/Lines/Line'
 17     passing xmlorders.lines
 18     columns item   varchar2(20) path 'Item'
 19           , quantity number       path 'Qty'
 20           , lineseq  for ordinality
 21         ) xmllines
 22  /

CUSTOMER STATE ORDERSEQ ITEM        QUANTITY  LINESEQ                           
-------- ----- -------- ----------- -------- --------                           
Smith    OHIO         1 Mouse              3        1                           
Smith    OHIO         1 Monitor            2        2                           
Jackson  TEXAS        2 Monitor            1        1                           
Jackson  TEXAS        2 Keyboard           2        2                           
Jackson  TEXAS        2 RJ-45 Cable        6        3                           

For the line_no we can use the ordinality column lineseq - no problem. For the order_id we can also have ordinality available to us, but what happens when the next batch of orders arrive?

We need a sequence for these orders.


SQL> create sequence order_no_seq
  2  /

Sequence created.

Now I would like to do this for the query to be used in INSERT ALL:

SQL> select   case xmllines.lineseq
  2     when 1 then order_no_seq.nextval
  3     else      order_no_seq.currval
  4         end order_id
  5       , xmlorders.customer
  6       , xmlorders.state
  7       , xmllines.lineseq line_no
  8       , xmllines.item
  9       , xmllines.quantity
 10    from xmltable(
 11     '/Orders/Order'
 12     passing xmltype(:received_xml)
 13     columns customer varchar2(20) path 'Customer'
 14           , state   varchar2(20) path 'State'
 15           , lines   xmltype      path 'Lines'
 16         ) xmlorders
 17       , xmltable(
 18     '/Lines/Line'
 19     passing xmlorders.lines
 20     columns item   varchar2(20) path 'Item'
 21           , quantity number       path 'Qty'
 22           , lineseq  for ordinality
 23         ) xmllines
 24  /

ORDER_ID CUSTOMER STATE  LINE_NO ITEM        QUANTITY                           
-------- -------- ----- -------- ----------- --------                           
       1 Smith    OHIO         1 Mouse              3                           
       2 Smith    OHIO         2 Monitor            2                           
       3 Jackson  TEXAS        1 Monitor            1                           
       4 Jackson  TEXAS        2 Keyboard           2                           
       5 Jackson  TEXAS        3 RJ-45 Cable        6                           

But because of the way sequences work (search asktom.oracle.com for explanation) that statement assigned an individual order_id to each orderline :-( We can work around that by retrieving our sequence via function calls:

SQL> create package order_api
  2  as
  3   function order_no_seq_nextval return number;
  4   function order_no_seq_currval return number;
  5  end order_api;
  6  /

Package created.

SQL> create package body order_api
  2  as
  3   function order_no_seq_nextval return number
  4   is
  5   begin
  6      return order_no_seq.nextval;
  7   end order_no_seq_nextval;
  8  
  9   function order_no_seq_currval return number
 10   is
 11   begin
 12      return order_no_seq.currval;
 13   end order_no_seq_currval;
 14  end order_api;
 15  /

Package body created.

And then we make a little change in the query:

SQL> select   case xmllines.lineseq
  2     when 1 then order_api.order_no_seq_nextval
  3     else      order_api.order_no_seq_currval
  4         end order_id
  5       , xmlorders.customer
  6       , xmlorders.state
  7       , xmllines.lineseq line_no
  8       , xmllines.item
  9       , xmllines.quantity
 10    from xmltable(
 11     '/Orders/Order'
 12     passing xmltype(:received_xml)
 13     columns customer varchar2(20) path 'Customer'
 14           , state   varchar2(20) path 'State'
 15           , lines   xmltype      path 'Lines'
 16         ) xmlorders
 17       , xmltable(
 18     '/Lines/Line'
 19     passing xmlorders.lines
 20     columns item   varchar2(20) path 'Item'
 21           , quantity number       path 'Qty'
 22           , lineseq  for ordinality
 23         ) xmllines
 24  /

ORDER_ID CUSTOMER STATE  LINE_NO ITEM        QUANTITY                           
-------- -------- ----- -------- ----------- --------                           
       6 Smith    OHIO         1 Mouse              3                           
       6 Smith    OHIO         2 Monitor            2                           
       7 Jackson  TEXAS        1 Monitor            1                           
       7 Jackson  TEXAS        2 Keyboard           2                           
       7 Jackson  TEXAS        3 RJ-45 Cable        6                           

This time we get the desired order_id numbering. And so we can do our insert again:

SQL> insert all
  2  when line_no = 1
  3  then
  4     into orders
  5   values (order_id
  6         , customer
  7         , state)
  8  when 1 = 1
  9  then
 10     into orderlines
 11   values (order_id
 12         , line_no
 13         , item
 14         , quantity)
 15  select   *
 16    from (
 17   select  case xmllines.lineseq
 18        when 1 then order_api.order_no_seq_nextval
 19        else order_api.order_no_seq_currval
 20     end order_id
 21          , xmlorders.customer
 22          , xmlorders.state
 23          , xmllines.lineseq line_no
 24          , xmllines.item
 25          , xmllines.quantity
 26       from xmltable(
 27        '/Orders/Order'
 28        passing xmltype(:received_xml)
 29        columns customer varchar2(20) path 'Customer'
 30       , state    varchar2(20) path 'State'
 31       , lines    xmltype   path 'Lines'
 32     ) xmlorders
 33          , xmltable(
 34        '/Lines/Line'
 35        passing xmlorders.lines
 36        columns item     varchar2(20) path 'Item'
 37       , quantity number   path 'Qty'
 38       , lineseq  for ordinality
 39     ) xmllines
 40      where rownum >= 1
 41  )
 42  /

7 rows created.

Notice the query from before has been put in an inline view with a "where rownum >= 1" clause. If I do not have that, then the insert fails because of referential integrity. The reason is that the optimizer would rewrite and take my "case" expression and use that instead of "order_id" in both parts of the INSERT ALL. Thus the insert into orders would call nextval and the first insert into orderlines would ALSO call nextval and then the subsequent inserts into orderlines would call currval. To work around that I put the query into an inline view and put a "where rownum >= 1" which forces Oracle to evaluate the case expression for order_id BEFORE doing the INSERT ALL.

So now we also have nice data that has been assigned order id:

SQL> select   order_id
  2       , customer
  3       , state
  4    from orders
  5  order by order_id
  6  /

ORDER_ID CUSTOMER STATE                                                         
-------- -------- -----                                                         
       8 Smith    OHIO                                                          
       9 Jackson  TEXAS                                                         

SQL> select   order_id
  2       , line_no
  3       , item
  4       , quantity
  5    from orderlines
  6  order by order_id, line_no
  7  /

ORDER_ID  LINE_NO ITEM        QUANTITY                                          
-------- -------- ----------- --------                                          
       8        1 Mouse              3                                          
       8        2 Monitor            2                                          
       9        1 Monitor            1                                          
       9        2 Keyboard           2                                          
       9        3 RJ-45 Cable        6                                          


Rather than the simple bind variable, we could have passed xml many ways:

We might have this in a procedure having a CLOB parameter:

   ...
   passing xmltype( p_in_clob )
   ...

Or we might have the xml in a file on the server in a folder for which we have created a directory object XML_IN_DIR:

   ...
   passing xmltype( BFILENAME('XML_IN_DIR', p_in_filename) )
   ...

Or the xml may be the return output of an http call to a webserver:

   ...
   passing httpuritype('http://a.server.com/getxml?id=123').getxml()
   ...

(Note in this last example there is no xmltype constructor as getxml() member method returns the datatype xmltype rather than simply text.)

The possibilities are endless :-)

You can download this demo script here.

Comments

  1. HI Kim,

    Very Nice and most useful post...I am doing the same work now...But am facing performance problem on Oracle 10g..

    Could you please help me to resolve my problem..Its urgent...
    MY XML is"


    -
    -
    -
    -
    PHIL
    POLLAK
    1591 Miller Rd
    Lilburn
    GA
    30047
    Sod Sales Direct
    null@cybersource.com
    US
    6785757676

    -
    -
    1339
    11
    2016
    Visa


    -
    -
    P
    1
    2015.81
    0.00
    default


    -
    -
    0
    DCARDREFUSED
    DECLINED

    -




    -
    3982617706980176056193
    fdiglobal
    2015.81
    USD
    0.00
    Z
    Z
    51
    2015.81
    USD
    474165

    -
    145 4/23/14


    -
    -
    PHIL
    POLLAK
    1591 Miller Rd
    Lilburn
    GA
    30047
    Polak Enterprises Inc.
    null@cybersource.com
    US
    6785757676

    -
    -
    1339
    11
    2016
    Visa


    -
    -
    P
    1
    2015.81
    0.00
    default


    -
    -
    0
    DCARDREFUSED
    DECLINED

    -




    -
    3982619956700176056193
    fdiglobal
    2015.81
    USD
    0.00
    Z
    Z
    51
    2015.81
    USD
    474165

    -
    145 4/23/14


    -
    -
    PHIL
    POLAK
    1591 Miller Rd
    Lilburn
    GA
    30047
    Polak Enterprises Inc.
    null@cybersource.com
    US
    6785757676

    -
    -
    1339
    11
    2016
    Visa


    -
    -
    P
    1
    2015.81
    0.00
    default


    -
    -
    1
    SOK
    Request was processed successfully.

    -
    1
    SOK
    Request was processed successfully.


    -
    3982625069050176056193
    fdiglobal
    2015.81
    USD
    0.00
    O
    517408
    Z
    Z
    M
    00
    2015.81
    USD
    474165

    -
    145 4/23/14



    "
    and my coding...

    ReplyDelete
    Replies
    1. Hi, Chithambaram

      Well, pasting XML into a Blogger comment is not very useful, as you can see for yourself - all the XML tags are missing ;-) And your coding is also missing - presumably the comment has become too large for Blogger. Sorry about that - Blogger is not really optimal for "support" type comments :-(

      As to "performance problem" - that is a wide question. What kind of performance problem?

      If I can I'll try to help if there's a bit more detail. Rather than pasting into Blogger comment, can you upload XML and code to some file sharing site? Then you can post the link in a comment along with a description of the performance problem.

      (I can't guarantee the speed of the reply, though, if it's really really urgent you might have to pay some consultant somewhere for fast action ;-)

      Regards
      Kim

      Delete
  2. Hi, Its a very useful Post. I am trying to accomplish the same thing. But my XML tags are little Different. Here is my XML sample:-


    7425
    BANK OF AMERICA CENTER
    42803075

    700 LOUISIANA ST
    HOUSTON
    TEXAS
    77002


    1557141
    M-M PROPERTIES
    7137286020
    HTTP://WWW.MMPROP.COM



    37230
    ONE ALLEN CENTER
    13330559

    500 DALLAS ST
    HOUSTON
    TEXAS
    77002


    1557141
    M-M PROPERTIES
    7137286020
    HTTP://WWW.MMPROP.COM



    I am able to get the BUILDINGS and BUILDINGADDRESS. But I am not able to get the PROPERTYMANAGER tag.

    Thanks,
    Rachit Jauhari.

    ReplyDelete
    Replies
    1. Hi, Rachit

      I'm sorry, but posting XML to Blogger removes all the XML tags, so I cannot see how your XML is structured :-(

      Either if you have the ability to upload the XML to some file sharing site and post a link?
      Or alternatively paste your XML into this HTML encoder:
      http://www.way2blogging.org/blogger-tools/html-entities-encoder-decoder
      Paste your XML there, press "encode" button.
      That will give you something that should look like this:

      &lt;xmltag&gt;
      The value
      &lt;/xmltag&gt;

      Then copy that HTML encoded result and paste it into a post here on the blog.
      And then it should look like this:

      <xmltag>
      The value
      </xmltag>

      Delete
  3. Here is my code:-
    /* Formatted on 6/25/2015 10:16:13 AM (QP5 v5.240.12305.39446) */
    DECLARE
    v_xml XMLTYPE;
    ECODE NUMBER;
    EMESG VARCHAR2(2000);

    BEGIN
    SELECT xmltype (xml_type) INTO v_xml FROM XML_TEMP;

    FOR R IN (
    select xmlorders.ID
    , xmlorders.NAME
    , xmlorders.CUSTOMERBUILDINGID
    from xmltable(
    '/BUILDINGS/BUILDING'
    passing v_xml
    columns ID integer path 'ID'
    , NAME varchar2(100) path 'NAME'
    , CUSTOMERBUILDINGID integer path 'CUSTOMERBUILDINGID'
    ) xmlorders )
    LOOP
    DBMS_OUTPUT.PUT_LINE('-->' || R.ID||'-->' || R.NAME||'-->' || R.CUSTOMERBUILDINGID);
    END LOOP;


    FOR buildingaddress IN (
    select xmlorders.ID
    , xmlbuildingaddress.STREETADDRESS1
    , xmlbuildingaddress.CITY
    , xmlbuildingaddress.STATE
    , xmlbuildingaddress.ZIP
    from xmltable(
    '/BUILDINGS/BUILDING'
    passing v_xml
    columns ID integer path 'ID'
    , BUILDING xmltype path 'BUILDING'
    ) xmlorders
    ,xmltable(
    '/BUILDING/BUILDINGADDRESS'
    passing xmlorders.BUILDING
    columns STREETADDRESS1 VARCHAR2(100) path 'STREETADDRESS1',
    CITY VARCHAR2(50) path 'CITY',
    STATE VARCHAR2(100) path 'STATE',
    ZIP VARCHAR2(100) path 'ZIP'
    ) xmlbuildingaddress
    )
    LOOP
    DBMS_OUTPUT.PUT_LINE('-->' || buildingaddress.ID||'-->' || buildingaddress.STREETADDRESS1||'-->' || buildingaddress.CITY||'-->' || buildingaddress.STATE);
    END LOOP;


    FOR propertymanager IN (
    select xmlorders.ID
    , xmlpropertymanager.COMPANYID
    , xmlpropertymanager.NAME
    , xmlpropertymanager.PHONE
    , xmlpropertymanager.WEBSITE
    from xmltable(
    '/BUILDINGS/BUILDING'
    passing v_xml
    columns ID integer path '@ID'
    , BUILDING xmltype path 'BUILDING'
    ) xmlorders
    ,xmltable(
    '/BUILDING/PROPERTYMANAGER'
    passing xmlorders.BUILDING
    columns COMPANYID VARCHAR2(100) path 'COMPANYID',
    NAME VARCHAR2(50) path 'NAME',
    PHONE VARCHAR2(100) path 'PHONE',
    WEBSITE VARCHAR2(100) path 'WEBSITE'
    ) xmlpropertymanager )
    LOOP
    DBMS_OUTPUT.PUT_LINE('-->' || propertymanager.ID||'-->' || propertymanager.COMPANYID||'-->' || propertymanager.NAME||'-->' || propertymanager.PHONE);
    END LOOP;


    EXCEPTION

    WHEN NO_DATA_NEEDED THEN
    RAISE;


    WHEN OTHERS THEN
    ECODE := SQLCODE;
    EMESG := SQLERRM;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(ECODE) || '-' || EMESG);
    dbms_output.put_line('===>>> CLEAN UP (NO_DATA_NEEDED)');


    END;

    ReplyDelete
  4. Hello.

    Great post.
    But how to do it, if you have two detail nodes and sometimes one can be missing.

    BR,
    Dip

    ReplyDelete
    Replies
    1. Hi, Dip

      Let me split that in two:

      - Detail is missing:

      For example in the last insert statement in the blogpost, line 32 could be changed to "left outer join xmltable(" and line 39 changed to ") xmllines on 1=1". And then the CASE structure in line 17-20 should be adapted to handle NULL values in lineseq when there are no orderlines.

      - Multiple details:

      If an order contained for example both orderlines and payments, you could add another xmltype column to the first xmltable call containing the payments, and then left outer join a third xmltable call passing this column. But the problem would then be that within each order, the orderlines and payments would actually be cartesian joined - you would need a way (maybe analytic row_number) to make sure each orderline and each payment would be inserted only once.

      That could easily become cumbersome, and depending on the number of detail rows it could also perform sub-optimally, so the benefit of a single insert compared to looping over the orders might be less (or even worse).

      If you have IDs in the data (no need for sequences), I'd probably do 2 inserts - one like shown in the post, one extra for all rows from the second detail nodes.


      Cheerio
      /Kim

      Delete
    2. Hi.

      Yes, i have ID column in all tables. But XML nodes have none.
      If i insert master and first detail node, how can i then insert second detail in new insert statement when i don't know to which master it belongs.
      XML master node can have only minor difference or none. Such is the structure of my XML.

      Example:
      Parts>
      ....Name confidential="true ">acetate/Name>
      ....NumberExists confidential="false">true/NumberExists>
      ....FullNumber confidential="false">111/FullNumber>
      ....AdditionalNumbers>
      ........Number confidential="false">123/Number>
      ........Number confidential="false">234/Number>
      ........Number confidential="false">134534/Number>
      ..../AdditionalNumbers>
      ....Quantity confidential="false">.22/Quantity>
      ....Functions>
      ........Function confidential="true ">13/Function>
      ..../Functions>
      ....WhetherClassification confidential="true ">false/WhetherClassification>
      ....Details>
      ........DataAvailable confidential="false">2/DataAvailable>
      ........Emission confidential="false">true/Emission>
      ....Details>
      /Parts>


      BR,
      Dip

      Delete
    3. Okay, when you have no IDs in the XML, then you cannot just split into two insert statements.

      Depending on your data you have two choices that I can think of.

      One way is a PL/SQL for-loop on a query that uses the single XMLTable to retrieve all master attributes + the additionalnumbers and functions as XMLType columns. Inside the for-loop you insert a single master (part) RETURNING it's ID. Then two insert statements using XMLtable on the two XMLType columns.

      The other is doing everything in a single INSERT ALL and accepting the complexity of the cartesian join. I have created an example of this on LiveSQL you can look at: https://livesql.oracle.com/apex/livesql/s/h684g2dl0wsv9c3b9l83mt7wo

      Which method is preferable depends on your data, how many rows, how large the cartesian joins between additionalnumbers and functions can become, etc. You would have to try it out and see which works the best for you ;-)

      Cheerio
      /Kim

      Delete

Post a Comment