Database Reference
In-Depth Information
select
o.OrderId as [@OrderId]
,o.OrderNum as [OrderNum]
,o.CustomerId as [CustomerId]
,o.OrderDate as [OrderDate]
,(
select
i.ArticleId as [@ArticleId]
,i.Quantity as [@Quantity]
,i.Price as [@Price]
from @OrderLineItems i
where i.OrderId = o.OrderId
for xml path('OrderLineItem'),root('OrderLineItems'), type
)
from @Orders o
for xml path('Order'),root('Orders')
-- RESULT:
<Orders>
<Order OrderId="42">
<OrderNum>10025</OrderNum>
<CustomerId>123</CustomerId>
<OrderDate>2013-07-15</OrderDate>
<OrderLineItems>
<OrderLineItem ArticleId="250" Quantity="3" Price="9.99" />
<OrderLineItem ArticleId="404" Quantity="1" Price="19.99" />
</OrderLineItems>
</Order>
<Order OrderId="54">
<OrderNum>10032</OrderNum>
<CustomerId>25</CustomerId>
<OrderDate>2013-07-15</OrderDate>
<OrderLineItems>
<OrderLineItem ArticleId="15" Quantity="1" Price="14.99" />
<OrderLineItem ArticleId="121" Quantity="2" Price="6.99" />
</OrderLineItems>
</Order>
</Orders>
You can use a
FOR XML PATH
clause to generate a delimiter-separated list of values. The code shown in Listing 11-20
generates comma-separated list of RecId values from the table.
