Hi,
When I run the following T-SQL code against my Database
T-SQL
WITH Unpivotted (ZIPCODEFROM ,ZIPCODETO,Value,Name) AS (
SELECT ZIPCODEFROM ,NULL,'start','ID'
FROM dbo.Mileage
WHERE ZIPCODEFROM IS NOT NULL
UNION ALL
SELECT ZIPCODEFROM ,NULL,'Start','NAME'
FROM dbo.Mileage
WHERE ZIPCODEFROM IS NOT NULL
UNION ALL
SELECT ZIPCODEFROM ,NULL,'1','RIGIDACC'
FROM dbo.Mileage
WHERE ZIPCODEFROM IS NOT NULL
UNION ALL
SELECT NULL ,ZIPCODETO,'end','ID'
FROM dbo.Mileage
WHERE ZIPCODETO IS NOT NULL
UNION ALL
SELECT NULL ,ZIPCODETO,'End','NAME'
FROM dbo.Mileage
WHERE ZIPCODETO IS NOT NULL
UNION ALL
SELECT ZIPCODEFROM ,NULL,ZIPCODEFROM,'POSTCODE'
FROM dbo.Mileage
WHERE ZIPCODEFROM IS NOT NULL
UNION ALL
SELECT NULL ,ZIPCODETO,ZIPCODETO,'POSTCODE'
FROM dbo.Mileage
WHERE ZIPCODETO IS NOT NULL
)
SELECT 'DEPOT' AS "@ObjectType",
(SELECT a.Name AS "@KeywordName",
a.Value AS "text()"
FROM Unpivotted a
WHERE a.ZIPCODEFROM=b.ZIPCODEFROM
FOR XML PATH('Value'), ROOT('Object'), TYPE)
FROM dbo.Mileage b
WHERE b.ZIPCODEFROM<>''
UNION ALL
SELECT 'CUST' AS "@ObjectType",
(SELECT a.Name AS "@KeywordName",
a.Value AS "text()"
FROM Unpivotted a
WHERE a.ZIPCODETO=b.ZIPCODETO
FOR XML PATH('Value'), ROOT('Object'), TYPE)
FROM dbo.Mileage b
WHERE b.ZIPCODETO<>''
FOR XML PATH('ObjectSequence'), ROOT('SupplierData'), TYPE;
it produces xml similar to the example shown below:
XML
<SupplierData>
<ObjectSequence ObjectType="DEPOT">
<Object>
<Value KeywordName="ID">start</Value>
<Value KeywordName="NAME">Start</Value>
<Value KeywordName="RIGIDACC">1</Value>
<Value KeywordName="POSTCODE">XX37 9</Value>
</Object>
</ObjectSequence>
<ObjectSequence ObjectType="CUST">
<Object>
<Value KeywordName="ID">end</Value>
<Value KeywordName="NAME">End</Value>
<Value KeywordName="POSTCODE">ZZ12 4</Value>
</Object>
</ObjectSequence>
</SupplierData>
However, when I run this code as 'Direct input' in an 'Execute SQL Task' I get an error:
'XML document must have a top level element. Error processing resource.'
The Source Output is '<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?>'
Any ideas please?
Thanks in advance,
Neal