Generate XML format from T-SQL

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

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.