Combining XML with Reverse Paths

Question

Hey everyone,

I am looking for a way that I can query a database with multiple SQL Queries and then, once I have the results of the queries (in XML), merge the separate XML together into one XML document, using reverse paths. My reason for doing this is related to the concept of Parallel Details (same data, with different manipulations applied).

For example, 3 Queries:

 SELECT * FROM Customer;
 SELECT * FROM Purchase_order;
 SELECT * FROM Line_Item;

Query 1 Results:

 <customer>
   <cust_id>2</cust_id>
   <fname>John</fname>
   <lname>Doe</lname>
 </customer>

Query 2 Results:

 <purchase_order>
   <order_id>2</order_id>
   <cust_id>2</cust_id>
   <shipped>7/7/2009</shipped>
 </purchase_order>

Query 3 Results:

 <line_item>
   <line_id>2</line_id>
   <order_id>2</order_id>
   <quantity>7</quantity>
 </line_item>

Desired Output:

 <collection>
   <customer>
     <cust_id>2</cust_id>
     <fname>John</fname>
     <lname>Doe</lname>
   </customer>
   <purchase_order>
     <order_id>2</order_id>
     <cust_id>2</cust_id>
     <shipped>7/7/2009</shipped>
   </purchase_order>
   <line_item>
     <line_id>2</line_id>
     <order_id>2</order_id>
     <quantity>7</quantity>
   </line_item>
 </collection>

This looks like it would be easy, but my SQL queries can return a lot of customers, and a lot of purchase orders and line items, and I need to be able to match them all up.

I cannot use one SQL Query with Joins that will collect all of this information at once.

Has anyone ever seen anything like this done? Any ideas?

Thanks.

Answer 1

XML formatting aside, it looks like what you want is to query the line item table and join the purchase order and customer tables on that.

SELECT * FROM Line_Item li 
  join Purchase_Order po on li.order_id on po.order_id 
  join Customer c = po.cust_id on c.cust_id

This will give you a result set of line items that has the purchase order and customer information inline. If you're using a server feature to render it to XML, you'll probably get something along the lines of

<line_item>
  <line_id>2</line_id>
  <quantity>7</quantity>
  <order_id>2</order_id>
  <shipped>7/7/2009</shipped>
  <fname>John</fname>
  <lname>Doe</lname>
</line_item>

Answer 2

SQL Server's FOR XML clause, given this input

SELECT 
  * 
FROM 
  Customer
  INNER JOIN Purchase_order ON Purchase_order.cust_id = Customer.cust_id
  INNER JOIN Line_Item      ON Line_Item.order_id = Purchase_order.order_id
FOR XML AUTO, ELEMENTS;

produces (I've added another line item to show how that would be generated):

<Customer>
  <cust_id>2</cust_id>
  <fname>John</fname>
  <lname>Doe</lname>
  <Purchase_order>
    <order_id>2</order_id>
    <cust_id>2</cust_id>
    <shipped>2009-07-07T00:00:00</shipped>
    <Line_Item>
      <line_id>2</line_id>
      <order_id>2</order_id>
      <quantity>7</quantity>
    </Line_Item>
    <Line_Item>
      <line_id>3</line_id>
      <order_id>2</order_id>
      <quantity>1</quantity>
    </Line_Item>
  </Purchase_order>
</Customer>

This is not exactly the way you want it, but maybe it's a start.

Answer 3

This will give you EXACTLY what you are looking for:

SELECT
    CONVERT(XML,
     (SELECT cust_id, fname, lname FROM Customer FOR XML PATH('customer'))),
    CONVERT(XML,
     (SELECT order_id, cust_id, shipped FROM Purchase_order FOR XML PATH('purchase_order'))),
    CONVERT(XML,
     (SELECT line_id, order_id, quantity FROM Line_Item FOR XML PATH('line_item')))
FOR XML PATH('collection')