The following SQL script generates an XML ResultSet document and stores it in a table:
declare @query java.lang.StringBuffer
select @query = new java.lang.StringBuffer()
-- The following “appends” build up a SQL select statement in
the @query variable
-- We use a StringBuffer, and the append method, so that the
@query can be as long as needed.
select @query>>append("select order_date as Date,
c.customer_id as CustomerId, ")
select @query>>append("customer_name as CustomerName, ")
select @query>>append("o.item_id as ItemId, i.item_name as ItemName, ")
select @query>>append("quantity as Quantity, o.unit as unit " )
select @query>>append("from customers c, orders o, items i ")
select @query>>append("where c.customer_id=o.customer_id and"
+ "o.item_id=i.item_id ")
declare @rsx xml.resultset.ResultSetXml
select @rsx = new xml.resultset.ResultSetXml (@query>>toString(), 'none', 'yes', '')
insert into resultset_docs values("1", @rsx)
