FOR XML PATH - how to implement grouping?



I have a T-SQL code which creates XML files out of database table. The tables contain order responses info. Each record represents a line of the response. For each response there are many lines.


The query returns one result for many records in the table. I want to rewrite the query to return one result set for each order response (not each line). The XML file should look like that:



<Line-Item>
<Line>xxxxxx</Line>
<Line>yyyyyy</Line>
</Line-Item>


Currently the code looks like that:



Select '231' as "OrderResponse-Header/OrderResponseType",
'Const' as "OrderResponse-Header/OrderResponseNumber",
coalesce(convert(varchar(10), GETDATE(), 20), '') as "OrderResponse-Header/OrderResponseDate",
coalesce(T.OrderNumber, '') as "OrderResponse-Header/OrderNumber",
coalesce(convert(varchar(10), GETDATE(), 20), '') as "OrderResponse-Header/OrderDate",
'0' as "OrderResponse-Header/DocumentFunctionCode",

'' as "DetailsOfTransport/TermsOfDelivery",
'' as "OrderResponse-Parties/Buyer/ILN",
'' as "OrderResponse-Parties/Buyer/PartyName",
'' as "OrderResponse-Parties/Buyer/StreetAndNumber",
'' as "OrderResponse-Parties/Buyer/CityName",
'' as "OrderResponse-Parties/Buyer/PostCode",
'' as "OrderResponse-Parties/Buyer/Country",

'' as "OrderResponse-Parties/Seller/ILN",
'' as "OrderResponse-Parties/Seller/PartyName",
'' as "OrderResponse-Parties/Seller/StreetAndNumber",
'' as "OrderResponse-Parties/Seller/CityName",
'' as "OrderResponse-Parties/Seller/PostCode",
'' as "OrderResponse-Parties/Seller/Country",

'' as "OrderResponse-Parties/DeliveryPoint/ILN",

'' as "OrderResponse-Parties/ShipFrom/ILN",

coalesce(T.LineNumber, '') as "OrderResponse-Lines/Line/Line-Item/LineNumber",
coalesce(T.BuyerItemCode, '') as "OrderResponse-Lines/Line/Line-Item/BuyerItemCode",
'' as "OrderResponse-Lines/Line/Line-Item/ItemDescription",
'' as "OrderResponse-Lines/Line/Line-Item/ItemStatus",
'' as "OrderResponse-Lines/Line/Line-Item/ItemType",
'0' as "OrderResponse-Lines/Line/Line-Item/OrderedQuantity",
coalesce(T.QuantityToBeDelivered, '') as "OrderResponse-Lines/Line/Line-Item/QuantityToBeDelivered",
'0' as "OrderResponse-Lines/Line/Line-Item/QuantityDifference",
'' as "OrderResponse-Lines/Line/Line-Item/UnitOfMeasure",
'0' as "OrderResponse-Lines/Line/Line-Item/OrderedUnitNetPrice",
'0' as "OrderResponse-Lines/Line/Line-Item/Discount",
coalesce(convert(varchar(10), T.ExpectedDeliveryDate, 20), '') as "OrderResponse-Lines/Line/Line-Item/ExpectedDeliveryDate",
'0' as "OrderResponse-Summary/TotalLines"
from Import.OrderResponses as T
where T.OrderNumber = 'Gr342'
for xml path(''), root('Document-OrderResponse'), type


Do I have to create another table for each line and join the 2 tables together?


No comments:

Post a Comment