XML Element Positioning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

XML Element Positioning

Author

Topic

cday119
Starting Member

1 Post

Posted - 2009-05-27 : 11:06:49

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_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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources