SQL Server 2000 XML Element Order

[cday119]

Hey everyone,

Im trying to create an XML document with specific element order. Here is what I need:

<invoice_shipment>
  <internal_id/>
  <shipping_cost/>
  <items>
    <item_code/>
  </items>
  <order_number>
</invoice_shipment>
 

So I created a SQl query that uses FOR XML EXPLICIT:

SELECT     1 AS Tag, NULL AS Parent, 
internal_id AS [invoice_shipment!1!internal_id!element], 
ship_cost AS [invoice_shipment!1!shipping_cost!element], 
NULL AS [items!2!order_Id],
NULL AS [item!3!item_code!element], 
order_number AS [order_number!4]
FROM         [order]
 
UNION
SELECT     2 AS Tag, 1 AS Parent, 
internal_id AS [invoice_shipment!1!internal_id!element], 
ship_cost AS [invoice_shipment!1!shipping_cost!element], 
items.order_Id AS [items!2!order_Id], 
NULL AS [item!3!item_code!element], 
order_number AS [order_number!4]
FROM         [order] INNER JOIN
                      items ON [order].internal_id = items.order_Id
UNION ALL
SELECT     3 AS Tag, 2 AS Parent, 
internal_id AS [invoice_shipment!1!internal_id!element], 
ship_cost AS [invoice_shipment!1!shipping_cost!element], 
items.order_Id AS [items!2!order_Id], 
item.code AS [item!3!item_code!element], 
order_number AS [order_number!4]
FROM         [order] 
INNER JOIN
items ON [order].internal_id = items.order_Id INNER JOIN
item ON items.items_Id = item.items_Id
 
UNION ALL
SELECT     4 AS Tag, 1 AS Parent, 
internal_id AS [invoice_shipment!1!internal_id!element], 
ship_cost AS [invoice_shipment!1!shipping_cost!element], 
null AS [items!2!order_Id], 
null AS [item!3!item_code!element], 
order_number AS [order_number!4]
FROM         [order]
 
 
ORDER BY [invoice_shipment!1!internal_id!element], [items!2!order_Id], [item!3!item_code!element], [order_number!4] FOR XML EXPLICIT
 
 

And that returns this:

<invoice_shipment>
  <internal_id>1445627</internal_id>
  <shipping_cost>0</shipping_cost>
  <order_number>10348</order_number>
  <items order_Id="1445627">
    <item>
      <item_code>EGBBDG</item_code>
    </item>
  </items>
  <order_number>10349</order_number>
</invoice_shipment>
<invoice_shipment>
  <internal_id>1445628</internal_id>
  <shipping_cost>2</shipping_cost>
  <items order_Id="1445628">
    <item>
      <item_code>EGOBDG</item_code>
    </item>
  </items>
  <order_number>10349</order_number>
</invoice_shipment>
<invoice_shipment>
  <internal_id>1445629</internal_id>
  <shipping_cost>10</shipping_cost>
  <items order_Id="1445629">
    <item>
      <item_code>EGBBDG</item_code>
    </item>
  </items>
  <order_number>10350</order_number>
</invoice_shipment>
<invoice_shipment>
  <internal_id>1445630</internal_id>
  <shipping_cost>0</shipping_cost>
  <items order_Id="1445630">
    <item>
      <item_code>EGBBDG</item_code>
    </item>
  </items>
</invoice_shipment>
 

The first <invoice_shipme nt> item has 2 <order_number > and the last has none. Anybody see what Im doing wrong here or maybe know of another way of doing this?

Thanks
Chad