{"id":1328,"date":"2012-06-04T09:30:11","date_gmt":"2012-06-04T03:30:11","guid":{"rendered":"http:\/\/techsatwork.com\/blog\/?p=1328"},"modified":"2016-01-07T11:31:04","modified_gmt":"2016-01-07T05:31:04","slug":"retrieving-data-as-xml-using-stored-procedure","status":"publish","type":"post","link":"https:\/\/techsatwork.com\/?p=1328","title":{"rendered":"Retrieving data as XML using stored procedure"},"content":{"rendered":"

On a previous post<\/a> I showed how to insert parent-child data from an XML document using stored procedure. Well here I am going to show how to retrieve data from a table as an XML document. \u00c2\u00a0Using the table set of tables from the previous post lets retrieve all the order details for a given customer. \u00c2\u00a0Here is a stored procedure that will take customer_id as an input and then return an xml document that has all the orders and order item that customer has purchased (customer order history) :<\/p>\n

CREATE PROCEDURE spRcustomer_order_history<\/span>
\n(IN customerid int, OUT customerorders XML)<\/span>
\nDYNAMIC RESULT SETS 1<\/span><\/p>\n

P1: BEGIN <\/span>
\ndeclare not_found condition for ‘02000’;<\/span>
\ndeclare exit handler for not_found <\/span>
\n\u00c2\u00a0 \u00c2\u00a0set customerorders = xmlcast(‘NO ROWS FOUND’ as xml);<\/span><\/p>\n

#####################################################<\/span>
\n — # Returns results in a XML parameter<\/span>
\n#####################################################<\/span><\/p>\n

select xmlelement(name “customerid” , xmlattributes (o.customer_id),<\/span>
\n xmlagg(<\/span>
\n xmlelement(name “order_id”,o.order_id,<\/span>
\n xmlelement(name “order_timestamp”,o.order_timestamp),<\/span>
\n ( select <\/span>
\n xmlagg( <\/span>
\n xmlelement(name “product_id”, oi.product_id, <\/span>
\n (xmlforest (oi.qty as “qty”,<\/span>
\n oi.unit_price as “unit_price”,<\/span>
\n oi.product_discount as “product_discount”,<\/span>
\n (oi.qty*oi.unit_price-oi.product_discount) as “total_price”<\/span>
\n )))) from order_items oi<\/span>
\n where o.order_id = oi.order_id<\/span>
\n )))) into customerorders<\/span>
\nFROM orders o<\/span>
\nwhere o.customer_id = customerid<\/span>
\n group by customer_id<\/span>
\n ; <\/span>
\nEND P1<\/span>
\n@<\/span><\/p>\n

Now lets call the stored procedure:
\nCall spRcustomer_order_history(13453,?);<\/span><\/p>\n

<customerid CUSTOMER_ID=”13453″><\/span>
\n<order_id>1003<\/span>
\n<order_timestamp>2012-05-24T21:05:33.552802<\/order_timestamp><\/span>
\n<product_id>Z001<\/span>
\n<qty>10<\/qty><\/span>
\n<unit_price>10.50<\/unit_price><\/span>
\n<product_discount>.00<\/product_discount><\/span>
\n<total_price>105.00<\/total_price><\/span>
\n<\/product_id><\/span>
\n<product_id>Z002<\/span>
\n<qty>14<\/qty><\/span>
\n<unit_price>20.50<\/unit_price><\/span>
\n<product_discount>.00<\/product_discount><\/span>
\n<total_price>287.00<\/total_price><\/span>
\n<\/product_id><\/span>
\n<\/order_id><\/span>
\n<\/customerid><\/span><\/p>\n

This will retrieve order history for the customer 13453 and return the data in XML format. \u00c2\u00a0Since the data is returned in an XML format, you do not need to use a cursor. \u00c2\u00a0Test it out and me know if there is any errors or a better way to do it.<\/p>\n","protected":false},"excerpt":{"rendered":"

On a previous post I showed how to insert parent-child data from an XML document using stored procedure. Well here I am going to show how to retrieve data from a table as an XML document. \u00c2\u00a0Using the table set of tables from the previous post lets retrieve all the order details for a given […]<\/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":[303,3,358],"tags":[799,802,825,800,826,823,746,487,824,801,822,818,821,820,819,488],"class_list":["post-1328","post","type-post","status-publish","format-standard","hentry","category-database","category-db2","category-how-to","tag-db2-stored-procedure","tag-db2-xml","tag-getting-data-from-db2-in-xml","tag-purexml","tag-querying-db2-in-xml","tag-retrieve-data-in-xml","tag-stored-procedure","tag-xml","tag-xml-output","tag-xml-query","tag-xml-stored-procedure","tag-xmlagg","tag-xmlattributes","tag-xmlelement","tag-xmlforest","tag-xquery"],"_links":{"self":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1328","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=1328"}],"version-history":[{"count":2,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1328\/revisions"}],"predecessor-version":[{"id":1331,"href":"https:\/\/techsatwork.com\/index.php?rest_route=\/wp\/v2\/posts\/1328\/revisions\/1331"}],"wp:attachment":[{"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1328"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1328"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/techsatwork.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1328"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}