{"id":1318,"date":"2012-05-25T20:22:16","date_gmt":"2012-05-25T14:22:16","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=1318"},"modified":"2016-01-07T11:31:04","modified_gmt":"2016-01-07T05:31:04","slug":"stored-procedure-to-insert-data-to-parent-and-child-using-xml","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1318","title":{"rendered":"Stored Procedure to insert data to parent and child using XML"},"content":{"rendered":"

Couple of days back I wrote an article<\/a> that showed how to quickly insert a child table with a \u00c2\u00a0parent table having generated column. On this one I am going to show you how to insert data using XML as a value. \u00c2\u00a0As 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) :<\/p>\n

create table orders<\/span>
\n(order_id\u00c2\u00a0 int not null<\/span>
\ngenerated always as identity (start with 1000,increment by 1, nocache)<\/span>
\n,customer_id int not null<\/span>
\n,order_timestamp\u00c2\u00a0 timestamp<\/span>
\n,order_channel_id\u00c2\u00a0 varchar(5)<\/span>
\n,csr_id\u00c2\u00a0 varchar(10)<\/span>
\n,primary key (order_id)<\/span>
\n);<\/span>
\ncreate table order_items<\/span>
\n(order_id\u00c2\u00a0 int not null<\/span>
\n,seq_num\u00c2\u00a0 int not null<\/span>
\n,product_id\u00c2\u00a0 varchar(20) not null<\/span>
\n,qty int<\/span>
\n,unit_price dec(9,2)<\/span>
\n,product_discount dec(9,2)<\/span>
\n,primary key(order_id,seq_num)<\/span>
\n);<\/span><\/p>\n

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\u00c2\u00a0demonstrating\u00c2\u00a0a method.<\/p>\n

create or replace procedure spIorders<\/span>
\n(in customerid int<\/span>
\n,in orderchannelid varchar(5)<\/span>
\n,in csrid varchar(10)<\/span>
\n,in orderitems XML<\/span>
\n,out orderid int<\/span>
\n)<\/span>
\nLANGUAGE SQL<\/span>
\nNO EXTERNAL ACTION<\/span>
\nP1:BEGIN<\/span>
\n— declaring global temporary table to hold the data<\/span>
\ndeclare global temporary table session.gtt_orders<\/span>
\n(customer_id int not null<\/span>
\n,order_channel_id varchar(5)<\/span>
\n,csr_id varchar(10)<\/span>
\n,order_item XML)<\/span>
\non commit delete rows<\/span>
\nnot logged;<\/span>
\n— insert the data to the global temporary table from the data passed in<\/span>
\ninsert into session.gtt_orders<\/span>
\n(customer_id,order_channel_id,csr_id,order_item)<\/span>
\nvalues<\/span>
\n(customerid<\/span>
\n,orderchannelid<\/span>
\n,csrid<\/span>
\n,orderitems);<\/span>
\n–insert into parent table (orders) from global temporary table<\/span>
\nselect order_id into orderid<\/span>
\nfrom new table<\/span>
\n(insert into orders<\/span>
\n(customer_id,order_timestamp,order_channel_id,csr_id)<\/span>
\nselect gtt.customer_id,current_timestamp,gtt.order_channel_id,gtt.csr_id<\/span>
\nfrom session.gtt_orders gtt);<\/span>
\n–insert into child table (order_items) from global temporary table<\/span>
\n–xmltable will split the XML document to multiple rows<\/span>
\n–row_number() over() is an olap function that will generate rows number for seq_num<\/span>
\ninsert into order_items<\/span>
\n(order_id,seq_num,product_id,qty,unit_price,product_discount)<\/span>
\nselect orderid,row_number() over(),x.productid,x.qty,x.unitprice,x.productdiscount<\/span>
\nfrom session.gtt_orders gtt,<\/span>
\nxmltable(‘$c\/orderitem\/item’ passing gtt.order_item as “c”<\/span>
\ncolumns<\/span>
\nproductid varchar(20) path ‘productid’,<\/span>
\nqty int path ‘qty’,<\/span>
\nunitprice dec(9,2) path ‘unitprice’,<\/span>
\nproductdiscount dec(9,2) path ‘productdiscount’) as x;<\/span>
\n—<\/span>
\ncommit;<\/span>
\n— dropping the global temporary table since the inserts have be done.<\/span>
\ndrop table session.gtt_orders;<\/span>
\nEND P1<\/span>
\n@<\/span><\/p>\n

Now lets call the stored procedure we just created an pass in an XML that has three order items :<\/p>\n

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

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.
\nPlease feel free to comment on this and possible improvements or alternate methods.<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

Couple of days back I wrote an article that showed how to quickly insert a child table with a \u00c2\u00a0parent table having generated column. On this one I am going to show you how to insert data using XML as a value. \u00c2\u00a0As my previous article show its easy to insert to child table , […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-container-style":"default","site-container-layout":"default","site-sidebar-layout":"default","site-transparent-header":"default","disable-article-header":"default","disable-site-header":"default","disable-site-footer":"default","disable-content-area-spacing":"default","footnotes":""},"categories":[3,358],"tags":[799,802,808,809,805,804,800,746,803,801,807,806],"class_list":["post-1318","post","type-post","status-publish","format-standard","hentry","category-db2","category-how-to","tag-db2-stored-procedure","tag-db2-xml","tag-inserting-child-data-using-xml","tag-inserting-multiple-rows-using-xml","tag-inserting-xml-data","tag-inserting-xml-to-db2-table","tag-purexml","tag-stored-procedure","tag-xml-column-type","tag-xml-query","tag-xmlparse","tag-xmltable"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1318","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1318"}],"version-history":[{"count":4,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1318\/revisions"}],"predecessor-version":[{"id":1320,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1318\/revisions\/1320"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1318"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1318"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1318"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}