MSSQL2K FOR XML Explicit help?

underprocessableHi Ian...
not quite understanding how the different attributes get calculated, here is
both a FOR XML EXPLICIT and FOR XML PATH solution (the later requires SQL
Server 2005, hint hint). You may need to change some of the formatting of
the values to get them to be completely consistent with your expected
results.
FOR XML EXPLICIT:
select 1 as tag, 0 as parent
, '1' as "ExternalProducts!1!AccountID"
, GETUTCDATE() as "ExternalProducts!1!TimeStamp"
, 0 as "ExternalProducts!1!UpdateType"
, '' as "ExternalProducts!1!DependantFilename"
, '07042001.EDN' as "ExternalProducts!1!SourceFilename"
, (select count(ProductID) from Products) as
"ExternalProducts!1!ProductCount"
, (select count(OuterBarcode) from Products)
as"ExternalProducts!1!BarcodeCount"
, NULL as "Products!2!ID!hide"
, NULL as "Products!2!OuterBarcode"
, NULL as "Products!2!Department"
, NULL as "Products!2!SubDepartment"
, NULL as "Products!2!Vat"
, NULL as "Products!2!PromotionType"
, NULL as "Products!2!DiscontinuedDate"
, NULL as "Supplier!3!Reference"
, NULL as "Supplier!3!PreviousReference"
, NULL as "Supplier!3!CostPrice"
, NULL as "Supplier!3!Units"
, NULL as "Supplier!3!MinOrder"
, NULL as "Supplier!3!FutureCostPrice"
, NULL as "Supplier!3!FutureCostPriceChangeDate"
, NULL as "Supplier!3!FutureCostPriceRevertDate"
, NULL as "ProductVariant!4!Barcode"
, NULL as "ProductVariant!4!Description"
, NULL as "ProductVariant!4!Receipt"
, NULL as "ProductVariant!4!RetailPrice"
, NULL as "ProductVariant!4!PriceMarked"
union all
select 2 , 1
, '1' , NULL, NULL, NULL, NULL, NULL, NULL /*ExternalProducts*/
, ProductID
, ISNULL(OuterBarcode, '')
, DepartmentID
, SubDepartmentID
, VatCode
, ''
, ISNULL(CAST(DiscontinuedDate as nvarchar(20)), '') /*Products*/
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL /*Supplier*/
, NULL, NULL, NULL, NULL, NULL /*ProductVariant*/
from Products
union all
select 3, 2
, '1' , NULL, NULL, NULL, NULL, NULL, NULL /*ExternalProducts*/
, Products.ProductID, NULL, NULL, NULL, NULL, NULL, NULL
/*Products*/
, CostPrices.Reference, '', CostPerOuter, UnitsPerOuter, MinOrderQty
, ISNULL(CAST(FutureCost as nvarchar(20)), '')
, ISNULL(CAST(FutureDate as nvarchar(20)), '')
, ISNULL(CAST(ReversionDate as nvarchar(20)), '') /*Supplier*/
, NULL, NULL, NULL, NULL, NULL /*ProductVariant*/
from CostPrices
join Products on CostPrices.ProductID = Products.ProductID
union all
select 4, 2
, '1' , NULL, NULL, NULL, NULL, NULL, NULL /*ExternalProducts*/
, Products.ProductID, NULL, NULL, NULL, NULL, NULL, NULL
/*Products*/
, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL /*Supplier*/
, rp.BarcodeID, b.Description, b.Receipt, rp.Price
, ISNULL(CAST(rp.FuturePrice as nvarchar(20)), 'False')
/*ProductVariant*/
from RetailPrices rp
join Barcodes b on rp.BarcodeID = b.BarcodeID
join Products on b.ProductID = Products.ProductID
order by "ExternalProducts!1!AccountID", "Products!2!ID!hide", tag
for xml explicit
FOR XML PATH:
select '1' as "@.AccountID"
, GETUTCDATE() as "@.TimeStamp"
, 0 as "@.UpdateType"
, '' as "@.DependantFilename"
, '07042001.EDN' as "@.SourceFilename"
, (select count(ProductID) from Products) as "@.ProductCount"
, (select count(OuterBarcode) from Products) as"@.BarcodeCount"
, ( select ISNULL(OuterBarcode, '') as "@.OuterBarcode"
, DepartmentID as "@.Department"
, SubDepartmentID as "@.SubDepartment"
, VatCode as "@.Vat"
, '' as "@.PromotionType" -- what relational column?
, ISNULL(CAST(DiscontinuedDate as nvarchar(20)), '') as
"@.DiscontinuedDate"
, ( select Reference as "@.Reference"
, '' as "@.PreviousReference" -- how do you calculate
that?
, CostPerOuter as "@.CostPrice"
, UnitsPerOuter as "@.Units"
, MinOrderQty as "@.MinOrder"
, ISNULL(CAST(FutureCost as nvarchar(20)), '') as
"@.FutureCostPrice"
, ISNULL(CAST(FutureDate as nvarchar(20)), '') as
"@.FutureCostPriceChangeDate"
, ISNULL(CAST(ReversionDate as nvarchar(20)), '') as
"@.FutureCostPriceRevertDate"
from CostPrices
where CostPrices.ProductID = Products.ProductID
for xml path('Supplier'), type)
, ( select rp.BarcodeID as "@.Barcode"
, b.Description as "@.Description"
, b.Receipt as "@.Receipt"
, rp.Price as "@.RetailPrice"
, ISNULL(CAST(rp.FuturePrice as nvarchar(20)), 'False') as
"@.PriceMarked" -- how is it calculated?
from RetailPrices rp
join Barcodes b on rp.BarcodeID = b.BarcodeID
where b.ProductID = Products.ProductID
for xml path('ProductVariant'), type)
from Products
for xml path('Products'),type)
for xml path('ExternalProducts')
I hope this helps
Michael
"Ian Ashworth" <spamcatch@.miragesoftware.co.uk> wrote in message
news:eTH0TszhHHA.208@.TK2MSFTNGP05.phx.gbl...
> Hey guys
> Currently Im pulling multiple recordsets back from SQL Server 2000 into
> VB6 and using text parsing techniques to create an XML file in the desired
> format. It does the job but is extremely slow and so I've been pointed in
> the direction of FOR XML EXPLICIT. Unfortunately this totally baffles me
> and I've been struggilng most of the day trying to get my head around it.
> I have attached a script to create the tables/example data and have
> included an example of the desired output in the comments so any help
> would be greatly appreciated.
> Thanks in advance for any assistance that you may provide.
> Ian Ashworth
>
>