I have an SQL query that returns XML data. One of the nodes to be returned won't always have data and in the cases where there is no data, I get an empty self closing tag...
[< additionalDocument/>]
If this section is empty, I don't want to see the tags at all, the output needs to just move on to the next section. From a quick search online and seeing similar questions, the consensus seems to be that it shouldn't matter and that the empty self closing tag won't create problems with syntax. However, the data is intended for import into a 3rd party system and their dev guys won't accept the file with this empty tag present. Is there any way of suppressing the section entirely when empty? Thanks Steve
Full query below...
@Consignment_id decimal(18,0),
@Supplier decimal(18,0)
AS
BEGIN
DECLARE @Orders table
(OSeqNum int identity,
OOrder_id decimal(18,0),
OProduct_id decimal(18,0),
OPurchaseOrder varchar(20),
OCases int,
OHealthCertRef varchar(20),
OHealthCertIssueDate date)
INSERT INTO @Orders
(OProduct_id,
OPurchaseOrder,
OCases,
OHealthCertRef,
OHealthCertIssueDate)
(SELECT Product_id,
PurchaseOrderNumber,
(SELECT SUM(Cartons) FROM Order_products WHERE order_id=Orders.Order_id AND Status='Active'),
HealthCertRef,
HealthCertIssueDate
FROM Orders
WHERE ConsignmentNumber_id=@Consignment_id AND Client=@Supplier)
SELECT 'OUT' AS direction,
(CONVERT(varchar(8),Consignment_id)+(CONVERT(varchar(8),(SELECT TOP 1 OpurchaseOrder FROM @Orders)))+UPPER(CONVERT(varchar(3),(SELECT DepotLocation FROM General WHERE Uber=DeliveryPoint))))AS externalReference,
('New')AS messageType,
storageLocationReference=
CASE WHEN CollectionPoint='311208108054' THEN 'X-Dock'
ELSE (SELECT DepotLocation FROM General WHERE Uber=CollectionPoint)
END,
('1.0')AS version,
(SELECT PointOfExit AS pointOfExit,
PointOfEntry AS pointOfEntry,
(SELECT '1' AS category,
'D24' AS type,
(CONVERT(varchar(20),DestinationPortETADate,112)+SUBSTRING(DestinationPortETATime,1,2)+SUBSTRING(DestinationPortETATime,4,2))AS refNum
FROM Consignments
WHERE Consignment_id=@Consignment_id
FOR XML PATH('additionalDocument'), TYPE)
'additionalDocuments',
(SELECT 'ROVRN'AS code,
(TrailerNumber)AS text
FROM Consignments
WHERE Consignment_id=@Consignment_id
FOR XML PATH('additionalInformation'), TYPE)
'additionalInformations',
(SELECT (SELECT Supplier# FROM General WHERE Uber=@Supplier)AS code
FOR XML PATH('declarant'), TYPE),
(SELECT
(SELECT UPPER(CONVERT(varchar(3),(SELECT DepotLocation FROM General WHERE Uber=(SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=@Consignment_id))))AS code
FOR XML PATH('consignee'), TYPE),
(SELECT
(SELECT BorderCountry AS country,
identification=
CASE WHEN Accompanied='true' THEN VehicleReg
ELSE FerryNumber
END,
'6' AS mode,
Accompanied AS isAccompanied
FOR XML PATH('borderTransportMeans'), TYPE),
(SELECT DepartureCountry AS country,
VehicleReg AS identification,
'30'AS identificationType,
'3' AS mode
FOR XML PATH('departureTransportMeans'), TYPE),
(SELECT
(SELECT '1' AS sealQuantity,
(SELECT
(SELECT '1' AS seqNum,
SealNumber AS identification
FROM Consignments
WHERE Consignment_id=@Consignment_id
FOR XML PATH ('seal'),TYPE)
FOR XML PATH ('seals'),TYPE)
FOR XML PATH ('transportEquipment'),TYPE)
FOR XML PATH('transportEquipments'),TYPE)
FOR XML PATH('consignment'),TYPE),
(SELECT (SELECT Supplier# FROM General WHERE Uber=@Supplier) AS code
FOR XML PATH ('exporter'),TYPE),
(SELECT (SELECT 'Aldi '+DepotLocation FROM General WHERE Uber=(SELECT DeliveryPoint FROM Consignments WHERE Consignment_id=@Consignment_id)) AS place
FOR XML PATH ('tradeTerms'),TYPE),
(SELECT (CONVERT(varchar(8),Consignment_id)+(CONVERT(varchar(8),(SELECT TOP 1 OpurchaseOrder FROM @Orders)))+UPPER(CONVERT(varchar(3),(SELECT DepotLocation FROM General WHERE Uber=DeliveryPoint))))AS traderReference
FOR XML PATH ('ucr'),TYPE),
(SELECT
(SELECT OSeqNum AS seqNum,
(SELECT
(SELECT
category=
CASE WHEN (SELECT HealthCertificateRequired FROM Products WHERE Product_id=OProduct_id)='No' THEN NULL
ELSE 'C'
END,
CONVERT(date,OHealthCertIssueDate,112) AS issueDate,
lpcoExemptionCode=
CASE WHEN (SELECT HealthCertificateRequired FROM Products WHERE Product_id=OProduct_id)='No' THEN NULL
ELSE 'AC'
END,
OHealthCertRef AS refNum,
type=
CASE WHEN (SELECT HealthCertificateRequired FROM Products WHERE Product_id=OProduct_id)='No' THEN NULL
ELSE '687'
END
FOR XML PATH('additionalDocument'),TYPE),
(SELECT '1' AS category,
'D94' AS type,
FerryNumber AS refNum
FOR XML PATH('additionalDocument'),TYPE)
FOR XML PATH ('additionalDocuments'),TYPE),
(SELECT (SELECT CONVERT(varchar(50),Code)+CONVERT(varchar(50),(SELECT Supplier# FROM General WHERE Uber=@Supplier)) FROM Products WHERE Product_id=OProduct_id) AS itemId
FOR XML PATH ('commodity'),TYPE),
(SELECT OCases AS qty
FOR XML PATH ('goodsMeasure'),TYPE),
(SELECT
(SELECT (SELECT CountryCode FROM Countries WHERE Country_id=(SELECT CountryOfOrigin FROM Products WHERE Product_id=OProduct_id)) AS country,
type=
CASE WHEN (SELECT COOPreferential FROM Products WHERE Product_id=OProduct_id)='Yes' THEN 'PreferentialOrigin'
WHEN (SELECT COOPreferential FROM Products WHERE Product_id=OProduct_id)='No' THEN 'NonPreferentialOrigin'
END
FOR XML PATH ('origin'),TYPE)
FOR XML PATH('origins'),TYPE),
(SELECT
(SELECT OCases AS quantity,
@Consignment_id AS shippingMarks,
'CS'AS type
FOR XML PATH ('packaging'),TYPE)
FOR XML PATH('packagings'),TYPE)
FROM @Orders
FOR XML PATH ('governmentAgencyGoodsItem'),TYPE)
FOR XML PATH ('governmentAgencyGoodsItems'),TYPE)
FOR XML PATH('goodsShipment'), TYPE)
FROM
Consignments
WHERE Consignment_id=@Consignment_id
FOR XML PATH(''), TYPE)
'singleAdministrativeDocument'
FROM Consignments
WHERE Consignment_id=@Consignment_id
FOR XML PATH ('customsShipment'), ELEMENTS, TYPE
END
The section that I'm trying to suppress when empty is...
(SELECT
category=
CASE WHEN (SELECT HealthCertificateRequired FROM Products WHERE Product_id=OProduct_id)='No' THEN NULL
ELSE 'C'
END,
CONVERT(date,OHealthCertIssueDate,112) AS issueDate,
lpcoExemptionCode=
CASE WHEN (SELECT HealthCertificateRequired FROM Products WHERE Product_id=OProduct_id)='No' THEN NULL
ELSE 'AC'
END,
OHealthCertRef AS refNum,
type=
CASE WHEN (SELECT HealthCertificateRequired FROM Products WHERE Product_id=OProduct_id)='No' THEN NULL
ELSE '687'
END
FOR XML PATH('additionalDocument'),TYPE),
Is there any way of wrapping this in some kind of IF statement or equivilent?
