Stored Procedure to insert data to parent and child using XML

Couple of days back I wrote an article that showed how to quickly insert a child table with a  parent table having generated column. On this one I am going to show you how to insert data using XML as a value.  As my previous article show its easy to insert to child table , but you have to insert one row at a time. Well what if we have multiple child rows that needs to be inserted . Here I am going to show you how to do that using XML as an input string holding child data. To make it nice and tight I wrote a small stored procedure . First lets create the tables (its the same set of tables I had it on my previous post) :

create table orders
(order_id  int not null
generated always as identity (start with 1000,increment by 1, nocache)
,customer_id int not null
,order_timestamp  timestamp
,order_channel_id  varchar(5)
,csr_id  varchar(10)
,primary key (order_id)
);
create table order_items
(order_id  int not null
,seq_num  int not null
,product_id  varchar(20) not null
,qty int
,unit_price dec(9,2)
,product_discount dec(9,2)
,primary key(order_id,seq_num)
);

Now that the table is created, lets create the stored procedure that will insert the data to the parent table and all data to the child table. Granted this stored procedure does not have all the bells and whistles and is only intended for demonstrating a method.

create or replace procedure spIorders
(in customerid int
,in orderchannelid varchar(5)
,in csrid varchar(10)
,in orderitems XML
,out orderid int
)
LANGUAGE SQL
NO EXTERNAL ACTION
P1:BEGIN
— declaring global temporary table to hold the data
declare global temporary table session.gtt_orders
(customer_id int not null
,order_channel_id varchar(5)
,csr_id varchar(10)
,order_item XML)
on commit delete rows
not logged;
— insert the data to the global temporary table from the data passed in
insert into session.gtt_orders
(customer_id,order_channel_id,csr_id,order_item)
values
(customerid
,orderchannelid
,csrid
,orderitems);
–insert into parent table (orders) from global temporary table
select order_id into orderid
from new table
(insert into orders
(customer_id,order_timestamp,order_channel_id,csr_id)
select gtt.customer_id,current_timestamp,gtt.order_channel_id,gtt.csr_id
from session.gtt_orders gtt);
–insert into child table (order_items) from global temporary table
–xmltable will split the XML document to multiple rows
–row_number() over() is an olap function that will generate rows number for seq_num
insert into order_items
(order_id,seq_num,product_id,qty,unit_price,product_discount)
select orderid,row_number() over(),x.productid,x.qty,x.unitprice,x.productdiscount
from session.gtt_orders gtt,
xmltable(‘$c/orderitem/item’ passing gtt.order_item as “c”
columns
productid varchar(20) path ‘productid’,
qty int path ‘qty’,
unitprice dec(9,2) path ‘unitprice’,
productdiscount dec(9,2) path ‘productdiscount’) as x;
—
commit;
— dropping the global temporary table since the inserts have be done.
drop table session.gtt_orders;
END P1
@

Now lets call the stored procedure we just created an pass in an XML that has three order items :

call spIorders (5000,’Web’,’RAJU’,
xmlparse(document
‘<orderitem>
<item>
<productid>iPad3-Cell-64</productid>
<qty>10</qty>
<unitprice>829.00</unitprice>
<productdiscount>50.00</productdiscount>
</item>
<item>
<productid>iPad2-Wifi-16</productid>
<qty>5</qty>
<unitprice>629.00</unitprice>
<productdiscount>0.00</productdiscount>
</item>
<item>
<productid>iPod-Tch-8</productid>
<qty>100</qty>
<unitprice>199.00</unitprice>
<productdiscount>100.00</productdiscount>
</item>
</orderitem>
‘ preserve whitespace),?);

If you query the orders and order_items you should be able to see one row in orders table and three rows in order_items.
Please feel free to comment on this and possible improvements or alternate methods.