Sunday, March 25, 2012

Adding a line

Is it possible to add this line into the main select:
SELECT 1 as Tag, NULL As Parent,
'http://PartnerLink.Artifacts.StockMovement.Schemas.StockMovement_BTSQL' As
[StockMovement!1!xmlns]
for xml explicit
output
<StockMovement
xmlns="http://PartnerLink.Artifacts.StockMovement.Schemas.StockMovement_BTSQ
L"/>
SELECT Delivery.DeliveryID, DeliveryType, DeliveryDate, DeliveryInfo,
ReceiveStore, ReceiveCountry, ReceiveCurrency, SenderStore, SenderCountry,
SenderCurrency, Status, Direction, GUID,
Style.DeliveryID as StyleTableDeliveryID, Style.LineNumber as
StyleLineNumber, StyleID, MasterEAN, Category, Department, [Description],
Style.Quantity as StyleQty, CostPrice, SalesPrice, VatCode,
InfoTextReference,
Variant.DeliveryID as VariantTableDeliveryID, Variant.LineNumber as
VariantTableLineNumber, VariantEAN, Variant.Quantity as VariantQty, Color,
Length, [Size]
FROM Delivery
inner join Style on Delivery.DeliveryID = Style.DeliveryID
inner join Variant on Style.LineNumber = Variant.LineNumber AND
Delivery.DeliveryID = Variant.DeliveryID
Where Delivery.Status = 1
for xml auto, elements
Output
<Delivery><DeliveryID>1</DeliveryID><DeliveryType>35</DeliveryType><Delivery
Date>20060431</DeliveryDate><DeliveryInfo>Afleveres
ved
porten</DeliveryInfo><ReceiveStore>1</ReceiveStore><ReceiveCountry>1</Receiv
eCountry><ReceiveCurrency>1</ReceiveCurrency><Se
Result should be
<StockMovement
xmlns="http://PartnerLink.Artifacts.StockMovement.Schemas.StockMovement_BTSQ
L">
<Delivery><DeliveryID>1</DeliveryID><DeliveryType>35</DeliveryType><Delivery
Date>20060431</DeliveryDate><DeliveryInfo>Afleveres
ved
porten</DeliveryInfo><ReceiveStore>1</ReceiveStore><ReceiveCountry>1</Receiv
eCountry><ReceiveCurrency>1</ReceiveCurrency><SeNo, not as long as you specify , ELEMENTS (I am guessing that you are on SS
2000, otherwise have a look at FOR XML PATH).
/ Tobias

No comments:

Post a Comment