ChannelDB2

Retrieving data as XML using stored procedure

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.  Using the table set of tables from the previous post lets retrieve all the order details for a given customer.  Here 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) :

CREATE PROCEDURE spRcustomer_order_history
(IN customerid int, OUT customerorders XML)
DYNAMIC RESULT SETS 1

P1: BEGIN 
declare not_found condition for '02000';
declare exit handler for not_found 
   set customerorders = xmlcast('NO ROWS FOUND' as xml);

#####################################################
-- # Returns results in a XML parameter
#####################################################

select xmlelement(name "customerid" , xmlattributes (o.customer_id),
xmlagg(
xmlelement(name "order_id",o.order_id,
xmlelement(name "order_timestamp",o.order_timestamp),
( select 
xmlagg( 
xmlelement(name "product_id", oi.product_id, 
(xmlforest (oi.qty as "qty",
oi.unit_price as "unit_price",
oi.product_discount as "product_discount",
(oi.qty*oi.unit_price-oi.product_discount) as "total_price"
)))) from order_items oi
where o.order_id = oi.order_id
)))) into customerorders
FROM orders o
where o.customer_id = customerid
group by customer_id

END P1
@

Now lets call the stored procedure:
Call spRcustomer_order_history(13453,?);


1003
2012-05-24T21:05:33.552802
Z001
10
10.50
.00
105.00

Z002
14
20.50
.00
287.00


This will retrieve order history for the customer 13453 and return the data in XML format.  Since the data is returned in an XML format, you do not need to use a cursor.  Test it out and me know if there is any errors or a better way to do it.

Views: 1011

Comment

You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Featured Downloads

Try BLU Acceleration on Cloud

© 2019   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service