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,?);

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

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.