March 1, 2012 at 9:08 am
Hi Folks,
I have an interesting one here. Hopefully one of you will be able to help. I have an SP that generates an XML file using fields from a variety of tables. I have a few joins going on. One of the fields I want is suppliername. Sometimes my join will fail to find the supplier due to missing data. I'm not too pushed about this, but when no supplier is found, the corresponding xml tag`([stockHeader!3!supplierName]) is left out of the file and this causes problems. Is there a way to get the tag to appear regardless of whether it has any data or not?
SELECT 1 AS TAG,
NULL AS PARENT,
NULL AS [stock!1!Sort],
@Filename AS [stock!1!Filename],
NULL AS [stockHeaders!2!LoadJobNumber!Element],
NULL AS [stockHeader!3!supplierName!Element],
NULL AS [stockHeader!3!commodityCode!Element],
NULL AS [stockHeader!3!lotNumber!Element],
NULL AS [stockHeader!3!receivedDate!Element],
NULL AS [stockHeader!3!expiryDate!Element],
NULL AS [stockHeader!3!receivedWeight!Element],
NULL AS [stockHeader!3!grnNumber!Element],
NULL AS [stockDetails!4!stockDetails!Element],
NULL AS [stockDetail!5!locationCode!Element],
NULL AS [stockDetail!5!quantity!Element],
NULL AS [suppBatchNumbers!6!suppBatchNumber!Element]
UNION
SELECT 2 AS TAG,
1 AS PARENT,
2 AS [stock!1!Sort],
@Filename AS [stock!1!Filename!Element],
NULL AS [stockHeaders!2!LoadJobNumber],
NULL AS [stockHeader!3!supplierName],
NULL AS [stockHeader!3!commodityCode],
NULL AS [stockHeader!3!lotNumber],
NULL AS [stockHeader!3!receivedDate],
NULL AS [stockHeader!3!expiryDate],
NULL AS [stockHeader!3!receivedWeight],
NULL AS [stockHeader!3!grnNumber],
NULL AS [stockDetails!4!stockDetails],
NULL AS [stockDetail!5!locationCode],
NULL AS [stockDetail!5!quantity],
NULL AS [suppBatchNumbers!6!suppBatchNumber]
FROM fgstockmovements fgsm
INNER JOIN ContainerHeaders CH ON CH.ContainerId=@DocumentReference AND CH.Company=@MovementCompany AND CH.Location=@MovementLocation AND CH.Warehouse=@MovementWarehouse
LEFT JOIN GRNHeader GRN ON GRN.GRNNumber = CH.GRNNUMBER AND GRN.Company = @MovementCompany
LEFT JOIN PurchaseOrderHeaders POH ON POH.PurchaseOrderNumber = GRN.PurchaseOrderNumber AND POH.Company = GRN.Company AND POH.Location=GRN.Location AND POH.Warehouse=GRN.Warehouse
LEFT JOIN SupplierDetails SD on SD.Company = @MovementCompany and SD.SupplierCode = POH.SupplierCode
WHERE fgsm.DateTime=@MovementDateTime AND MovementType=@MovementType AND DocumentReference=@DocumentReference AND fgsm.Company=@MovementCompany AND fgsm.Location=@MovementLocation AND fgsm.Warehouse=@MovementWarehouse
UNION
SELECT 3 AS TAG,
2 AS PARENT,
3 AS [stock!1!Sort],
@Filename AS [stock!1!Filename!Element],
NULL AS [stockHeaders!2!LoadJobNumber],
--'WMS' AS [stockHeader!3!supplierName],
LTRIM(RTRIM(SD.SupplierName)) AS [stockHeader!3!supplierName],
RTRIM(ProductNumber) AS [stockHeader!3!commodityCode],
--ContainerId AS [stockHeader!3!lotNumber],
replace(str(ContainerID,9),' ','0') AS [stockHeader!3!lotNumber],
LEFT(@TimeStamp,10) AS [stockHeader!3!receivedDate],
LEFT(dbo.fnEdiDateFormater (BBEDate,'DATETIME'),10) AS [stockHeader!3!expiryDate],
CAST(Quantity AS VARCHAR(20)) AS [stockHeader!3!receivedWeight],
LTRIM(RTRIM(CAST(CH.GRNNUMBER AS VARCHAR(20)))) AS [stockHeader!3!grnNumber],
NULL AS [stockDetails!4!stockDetails],
NULL AS [stockDetail!5!locationCode],
NULL AS [stockDetail!5!quantity],
NULL AS [suppBatchNumbers!6!suppBatchNumber]
FROM fgstockmovements fgsm
INNER JOIN ContainerHeaders CH ON CH.ContainerId=@DocumentReference AND CH.Company=@MovementCompany AND CH.Location=@MovementLocation AND CH.Warehouse=@MovementWarehouse
LEFT JOIN GRNHeader GRN ON GRN.GRNNumber = CH.GRNNUMBER AND GRN.Company = @MovementCompany
LEFT JOIN PurchaseOrderHeaders POH ON POH.PurchaseOrderNumber = GRN.PurchaseOrderNumber AND POH.Company = GRN.Company AND POH.Location=GRN.Location AND POH.Warehouse=GRN.Warehouse
LEFT JOIN SupplierDetails SD on SD.Company = @MovementCompany and SD.SupplierCode = POH.SupplierCode
WHERE fgsm.DateTime=@MovementDateTime AND MovementType=@MovementType AND DocumentReference=@DocumentReference AND fgsm.Company=@MovementCompany AND fgsm.Location=@MovementLocation AND fgsm.Warehouse=@MovementWarehouse
UNION
SELECT 4 AS TAG,
3 AS PARENT,
4 AS [stock!1!Sort],
@Filename AS [stock!1!Filename!Element],
NULL AS [stockHeaders!2!LoadJobNumber],
NULL AS [stockHeader!3!supplierName],
NULL AS [stockHeader!3!commodityCode],
NULL AS [stockHeader!3!lotNumber],
NULL AS [stockHeader!3!receivedDate],
NULL AS [stockHeader!3!expiryDate],
NULL AS [stockHeader!3!receivedWeight],
NULL AS [stockHeader!3!grnNumber],
NULL AS [stockDetails!4!stockDetails],
NULL AS [stockDetail!5!locationCode],
NULL AS [stockDetail!5!quantity],
NULL AS [suppBatchNumbers!6!suppBatchNumber]
FROM fgstockmovements fgsm
INNER JOIN ContainerHeaders CH ON CH.ContainerId=@DocumentReference AND CH.Company=@MovementCompany AND CH.Location=@MovementLocation AND CH.Warehouse=@MovementWarehouse
LEFT JOIN GRNHeader GRN ON GRN.GRNNumber = CH.GRNNUMBER AND GRN.Company = @MovementCompany
LEFT JOIN PurchaseOrderHeaders POH ON POH.PurchaseOrderNumber = GRN.PurchaseOrderNumber AND POH.Company = GRN.Company AND POH.Location=GRN.Location AND POH.Warehouse=GRN.Warehouse
LEFT JOIN SupplierDetails SD on SD.Company = @MovementCompany and SD.SupplierCode = POH.SupplierCode
WHERE fgsm.DateTime=@MovementDateTime AND MovementType=@MovementType AND DocumentReference=@DocumentReference AND fgsm.Company=@MovementCompany AND fgsm.Location=@MovementLocation AND fgsm.Warehouse=@MovementWarehouse
UNION
SELECT 5 AS TAG,
4 AS PARENT,
5 AS [stock!1!Sort],
@Filename AS [stock!1!Filename!Element],
NULL AS [stockHeaders!2!LoadJobNumber],
NULL AS [stockHeader!3!supplierName],
NULL AS [stockHeader!3!commodityCode],
NULL AS [stockHeader!3!lotNumber],
NULL AS [stockHeader!3!receivedDate],
NULL AS [stockHeader!3!expiryDate],
NULL AS [stockHeader!3!receivedWeight],
NULL AS [stockHeader!3!grnNumber],
NULL AS [stockDetails!4!stockDetails],
RTRIM(@MovementLocation) AS [stockDetail!5!locationCode],
CAST(Quantity AS VARCHAR(20)) AS [stockDetail!5!quantity],
NULL AS [suppBatchNumbers!6!suppBatchNumber]
FROM fgstockmovements fgsm
INNER JOIN ContainerHeaders CH ON CH.ContainerId=@DocumentReference AND CH.Company=@MovementCompany AND CH.Location=@MovementLocation AND CH.Warehouse=@MovementWarehouse
LEFT JOIN GRNHeader GRN ON GRN.GRNNumber = CH.GRNNUMBER AND GRN.Company = @MovementCompany
LEFT JOIN PurchaseOrderHeaders POH ON POH.PurchaseOrderNumber = GRN.PurchaseOrderNumber AND POH.Company = GRN.Company AND POH.Location=GRN.Location AND POH.Warehouse=GRN.Warehouse
LEFT JOIN SupplierDetails SD on SD.Company = @MovementCompany and SD.SupplierCode = POH.SupplierCode
WHERE fgsm.DateTime=@MovementDateTime AND MovementType=@MovementType AND DocumentReference=@DocumentReference AND fgsm.Company=@MovementCompany AND fgsm.Location=@MovementLocation AND fgsm.Warehouse=@MovementWarehouse
UNION
SELECT 6 AS TAG,
5 AS PARENT,
6 AS [stock!1!Sort],
@Filename AS [stock!1!Filename!Element],
NULL AS [stockHeaders!2!LoadJobNumber],
NULL AS [stockHeader!3!supplierName],
NULL AS [stockHeader!3!commodityCode],
NULL AS [stockHeader!3!lotNumber],
NULL AS [stockHeader!3!receivedDate],
NULL AS [stockHeader!3!expiryDate],
NULL AS [stockHeader!3!receivedWeight],
NULL AS [stockHeader!3!grnNumber],
NULL AS [stockDetails!4!stockDetails],
NULL AS [stockDetail!5!locationCode],
NULL AS [stockDetail!5!quantity],
LTRIM(RTRIM(BatchNumber)) AS [suppBatchNumbers!6!suppBatchNumber]
FROM fgstockmovements fgsm
INNER JOIN ContainerHeaders CH ON CH.ContainerId=@DocumentReference AND CH.Company=@MovementCompany AND CH.Location=@MovementLocation AND CH.Warehouse=@MovementWarehouse
LEFT JOIN GRNHeader GRN ON GRN.GRNNumber = CH.GRNNUMBER AND GRN.Company = @MovementCompany
LEFT JOIN PurchaseOrderHeaders POH ON POH.PurchaseOrderNumber = GRN.PurchaseOrderNumber AND POH.Company = GRN.Company AND POH.Location=GRN.Location AND POH.Warehouse=GRN.Warehouse
LEFT JOIN SupplierDetails SD on SD.Company = @MovementCompany and SD.SupplierCode = POH.SupplierCode
WHERE fgsm.DateTime=@MovementDateTime AND MovementType=@MovementType AND DocumentReference=@DocumentReference AND fgsm.Company=@MovementCompany AND fgsm.Location=@MovementLocation AND fgsm.Warehouse=@MovementWarehouse
ORDER BY [stock!1!Sort]
FOR XML EXPLICIT
March 1, 2012 at 9:23 am
I don't think Explicit mode supports XSINIL in the FOR clause, so what you probably have to do is wrap the appropriate column in IsNull/Coalesce.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2012 at 9:38 am
That worked for me - thanks for the info:-)
March 1, 2012 at 9:40 am
I took a bit closer look at Explicit, and you can specify elementxsinil in the directive part of a column name.
Example at: http://msdn.microsoft.com/en-us/library/bb522622.aspx
Documentation at: http://msdn.microsoft.com/en-us/library/ms189068.aspx
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 1, 2012 at 9:58 am
For some reason I get the error
In the FOR XML EXPLICIT clause, mode 'ELEMENTXSINIL' in a column name is invalid.
when I have NULL AS [stockHeader!3!supplierName!ELEMENTXSINIL],
. I'll go with the ISNULL solution anyway
March 1, 2012 at 9:59 am
Cool biz.
Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply