By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,265 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,265 IT Pros & Developers. It's quick & easy.

SQL Server 2000 XML Element Order

P: 29
Hey everyone,

Im trying to create an XML document with specific element order. Here is what I need:
Expand|Select|Wrap|Line Numbers
  1. <invoice_shipment>
  2.   <internal_id/>
  3.   <shipping_cost/>
  4.   <items>
  5.     <item_code/>
  6.   </items>
  7.   <order_number>
  8. </invoice_shipment>
  9.  
So I created a SQl query that uses FOR XML EXPLICIT:

Expand|Select|Wrap|Line Numbers
  1. SELECT     1 AS Tag, NULL AS Parent, 
  2. internal_id AS [invoice_shipment!1!internal_id!element], 
  3. ship_cost AS [invoice_shipment!1!shipping_cost!element], 
  4. NULL AS [items!2!order_Id],
  5. NULL AS [item!3!item_code!element], 
  6. order_number AS [order_number!4]
  7. FROM         [order]
  8.  
  9. UNION
  10. SELECT     2 AS Tag, 1 AS Parent, 
  11. internal_id AS [invoice_shipment!1!internal_id!element], 
  12. ship_cost AS [invoice_shipment!1!shipping_cost!element], 
  13. items.order_Id AS [items!2!order_Id], 
  14. NULL AS [item!3!item_code!element], 
  15. order_number AS [order_number!4]
  16. FROM         [order] INNER JOIN
  17.                       items ON [order].internal_id = items.order_Id
  18. UNION ALL
  19. SELECT     3 AS Tag, 2 AS Parent, 
  20. internal_id AS [invoice_shipment!1!internal_id!element], 
  21. ship_cost AS [invoice_shipment!1!shipping_cost!element], 
  22. items.order_Id AS [items!2!order_Id], 
  23. item.code AS [item!3!item_code!element], 
  24. order_number AS [order_number!4]
  25. FROM         [order] 
  26. INNER JOIN
  27. items ON [order].internal_id = items.order_Id INNER JOIN
  28. item ON items.items_Id = item.items_Id
  29.  
  30. UNION ALL
  31. SELECT     4 AS Tag, 1 AS Parent, 
  32. internal_id AS [invoice_shipment!1!internal_id!element], 
  33. ship_cost AS [invoice_shipment!1!shipping_cost!element], 
  34. null AS [items!2!order_Id], 
  35. null AS [item!3!item_code!element], 
  36. order_number AS [order_number!4]
  37. FROM         [order]
  38.  
  39.  
  40. ORDER BY [invoice_shipment!1!internal_id!element], [items!2!order_Id], [item!3!item_code!element], [order_number!4] FOR XML EXPLICIT
  41.  
  42.  
And that returns this:
Expand|Select|Wrap|Line Numbers
  1. <invoice_shipment>
  2.   <internal_id>1445627</internal_id>
  3.   <shipping_cost>0</shipping_cost>
  4.   <order_number>10348</order_number>
  5.   <items order_Id="1445627">
  6.     <item>
  7.       <item_code>EGBBDG</item_code>
  8.     </item>
  9.   </items>
  10.   <order_number>10349</order_number>
  11. </invoice_shipment>
  12. <invoice_shipment>
  13.   <internal_id>1445628</internal_id>
  14.   <shipping_cost>2</shipping_cost>
  15.   <items order_Id="1445628">
  16.     <item>
  17.       <item_code>EGOBDG</item_code>
  18.     </item>
  19.   </items>
  20.   <order_number>10349</order_number>
  21. </invoice_shipment>
  22. <invoice_shipment>
  23.   <internal_id>1445629</internal_id>
  24.   <shipping_cost>10</shipping_cost>
  25.   <items order_Id="1445629">
  26.     <item>
  27.       <item_code>EGBBDG</item_code>
  28.     </item>
  29.   </items>
  30.   <order_number>10350</order_number>
  31. </invoice_shipment>
  32. <invoice_shipment>
  33.   <internal_id>1445630</internal_id>
  34.   <shipping_cost>0</shipping_cost>
  35.   <items order_Id="1445630">
  36.     <item>
  37.       <item_code>EGBBDG</item_code>
  38.     </item>
  39.   </items>
  40. </invoice_shipment>
  41.  
The first <invoice_shipment> 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
May 27 '09 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.