Generate XML format from T-SQL
-
Hi,
I need to produce an XML format using T-SQL. The following code is the requirement, where the 'POSTCODE' values are NVarChar columns from a SQL 'Mileage' table.
<SupplierData><ObjectSequence ObjectType="DEPOT">
<Object>
<Value KeywordName="ID">start</Value>
<Value KeywordName="NAME">Start</Value>
<Value KeywordName="POSTCODE">XX16 6DN</Value>
<Value KeywordName="RIGIDACC">1</Value>
</Object>
</ObjectSequence>
<ObjectSequence ObjectType="CUST">
<Object>
<Value KeywordName="ID">end</Value>
<Value KeywordName="NAME">End</Value>
<Value KeywordName="POSTCODE">ZZ21 2EU</Value>
</Object>
</ObjectSequence>
</SupplierData>
In the above example
- The 'XX16 6DN' value (ObjectType 'DEPOT') needs to be extracted from the ZIPCODEFROM column
- The 'ZZ21 2EU' value (ObjectType 'CUST') needs to be extracted from the ZIPCODETO column
- All other values are hard-coded
NOTE:
There could be single OR multiple records in the 'MILEAGE' table.
I have started the basic code using the following T-SQL with my limited knowledge and can't get the desired output:
SELECT 'DEPOT' AS '@ObjectType',(SELECT 'ID' AS '@KeywordName',
ZIPCODEFROM AS POSTCODE FROM dbo.MILEAGE
FOR XML PATH('Value'), TYPE) AS [Object]
FOR XML PATH('ObjectSequence'), ROOT('SupplierData')
Any ideas please?
Thanks in advance.
-
See if this helps
DECLARE @MILEAGE TABLE(ZIPCODEFROM VARCHAR(30),ZIPCODETO VARCHAR(30), ID VARCHAR(30), NAME VARCHAR(30), RIGIDACC VARCHAR(30))
INSERT INTO @MILEAGE(ZIPCODEFROM,ZIPCODETO,ID,NAME,RIGIDACC)
SELECT 'XX16 6DN',NULL,'start','Start',1 UNION ALL
SELECT NULL,'ZZ21 2EU','end','End',NULL ;
WITH Unpivotted (ZIPCODEFROM ,ZIPCODETO,Value,Name) AS (
SELECT ZIPCODEFROM ,ZIPCODETO,ID,'ID'
FROM @MILEAGE
WHERE ID IS NOT NULL
UNION ALL
SELECT ZIPCODEFROM ,ZIPCODETO,NAME,'NAME'
FROM @MILEAGE
WHERE NAME IS NOT NULL
UNION ALL
SELECT ZIPCODEFROM ,ZIPCODETO,RIGIDACC,'RIGIDACC'
FROM @MILEAGE
WHERE RIGIDACC IS NOT NULL
UNION ALL
SELECT ZIPCODEFROM ,ZIPCODETO,ZIPCODEFROM,'POSTCODE'
FROM @MILEAGE
WHERE ZIPCODEFROM IS NOT NULL
UNION ALL
SELECT ZIPCODEFROM ,ZIPCODETO,ZIPCODETO,'POSTCODE'
FROM @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 @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 @MILEAGE b
WHERE b.ZIPCODETO<>''
FOR XML PATH('ObjectSequence'), ROOT('SupplierData'), TYPE;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi Mark,
Thanks - Firstly, the structure is perfect.
However, the 'Postcode' values aren't being pulled from the Mileage table though. The output contains the values that I gave in my example ('XX16 6DN' and 'ZZ21 2EU') as you are using a Temporary table. How do I retrieve the values from my table.
Sorry,
Neal
-
DerbyNeal (3/3/2011)
Hi Mark,Thanks - Firstly, the structure is perfect.
However, the 'Postcode' values aren't being pulled from the Mileage table though. The output contains the values that I gave in my example ('XX16 6DN' and 'ZZ21 2EU') as you are using a Temporary table. How do I retrieve the values from my table.
Sorry,
Neal
It would help if you could post some sample data and expected results, see the link in my signature for more information.
> you are using a Temporary table. How do I retrieve the values from my table.
Change @MILEAGE to dbo.MILEAGE
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
I did try changing the '@Mileage to dbo.Mileage before my last post. The only fields that reside in the Mielage table are the ZIPCODEFROM and ZIPCODETO columns. The others (i.e. 'ID', 'NAME', 'RIGIDACC') do not belong to this table.
Example1
Mileage table fields
ZIPCODEFROM, ZIPCODETO, RECID
XX16 6DN, ZZ21 2EU, 77
Then..
<ParagonData><ObjectSequence ObjectType="DEPOT">
<Object>
<Value KeywordName="ID">start</Value>
<Value KeywordName="NAME">Start</Value>
<Value KeywordName="RIGIDACC">1</Value>
<Value KeywordName="POSTCODE">XX16 6DN</Value>
</Object>
</ObjectSequence>
<ObjectSequence ObjectType="CUST">
<Object>
<Value KeywordName="ID">end</Value>
<Value KeywordName="NAME">End</Value>
<Value KeywordName="POSTCODE">ZZ21 2EU</Value>
</Object>
</ObjectSequence>
</ParagonData>
Example2
Mileage table fields
ZIPCODEFROM, ZIPCODETO, RECID
XY16 6ZZ, ZZ21 2WX, 77
Then..
<ParagonData><ObjectSequence ObjectType="DEPOT">
<Object>
<Value KeywordName="ID">start</Value>
<Value KeywordName="NAME">Start</Value>
<Value KeywordName="RIGIDACC">1</Value>
<Value KeywordName="POSTCODE">XY16 6ZZ</Value>
</Object>
</ObjectSequence>
<ObjectSequence ObjectType="CUST">
<Object>
<Value KeywordName="ID">end</Value>
<Value KeywordName="NAME">End</Value>
<Value KeywordName="POSTCODE">ZZ21 2WX</Value>
</Object>
</ObjectSequence>
</ParagonData>
Thanks again - but I don't think my original post was too misleading or unhelpful,
-
Ideally you should provide table definitions for all relevant tables along with sample data in the format
CREATE TABLE dbo.MILEAGE(ZIPCODEFROM VARCHAR(.... )
INSERT INTO dbo.MILEAGE ...
You'll usually get more responses. Meanwhile, here's an updated query for you to try.
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;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark, many thanks. I appreciate your efforts.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply