For XML Explicit Column order

Question

Post reply

For XML Explicit Column order

mark.picton

Valued Member

Points: 53
More actions
December 2, 2018 at 6:55 am

#416527

Is it possible to generate output from FOR XML EXPLICIT a specific column order - any help much appreciated.
Example SQL
declare @orderID int = 225963
declare @tblOrders table (MOVEMENT_NUMBER int, COMPARTMENT_NUMBER int, PRODUCT_NUMBER int, PRODUCT_NAME nvarchar(200), SUPPLIER_NAME nvarchar(200), ORDERED_QUANTITY int)
insert into @tblOrders select '225963', 1, 156, 'Product 1', 'Customer 1', 6000
insert into @tblOrders select '225963', 2, 156, 'Product 2', 'Customer 1', 6001
select 10 Tag, 0 Parent
    , vw.MOVEMENT_NUMBER [OrderDetail!10!MovementNumber!Element]
    , null [Compartment!20!Number!Element]
    , null [Compartment!20!Product!Element]
    , null [Compartment!20!Supplier!Element]
    , vw.SUPPLIER_NAME [OrderDetail!10!Supplier!Element]
from @tblOrders vw
where MOVEMENT_NUMBER = @orderID
union
select 20 Tag, 10 Parent
    , null
    , vw.COMPARTMENT_NUMBER
    , vw.PRODUCT_NAME
    , vw.ORDERED_QUANTITY
    , null
from @tblOrders vw
where MOVEMENT_NUMBER = @orderID
for xml explicit, root('Order')
XML Output
<Order>
<OrderDetail>
<MovementNumber>225963</MovementNumber>
<Supplier>Customer 1</Supplier>
<Compartment>
  <Number>1</Number>
  <Product>Product 1</Product>
  <Supplier>6000</Supplier>
</Compartment>
<Compartment>
  <Number>2</Number>
  <Product>Product 2</Product>
  <Supplier>6001</Supplier>
</Compartment>
</OrderDetail>
</Order>
I would like the Supplier element to be the last in the 'Order Detail' sequence such that the XMl read:
<Order>
<OrderDetail>
<MovementNumber>225963</MovementNumber>
<Compartment>
  <Number>1</Number>
  <Product>Product 1</Product>
  <Supplier>6000</Supplier>
</Compartment>
<Compartment>
  <Number>2</Number>
  <Product>Product 2</Product>
  <Supplier>6001</Supplier>
</Compartment>
<Supplier>Customer 1</Supplier>
</OrderDetail>
</Order>

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76944 More actions · Answer 1

Yes, but it's MUCH, MUCH, MUCH, MUCH easier to use FOR XML PATH. Do you really need to use FOR XML EXPLICIT?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

mark.picton Valued Member Points: 53 More actions · Answer 2

Thanks for that. And yes I loooked at the real life query I was trying to model and it is considerably easier using XML PATH. So that’s settled then ??