FOR XML PATH help SQL 2005

Question

Post reply

FOR XML PATH help SQL 2005

Shaun-884394

Ten Centuries

Points: 1096
More actions
December 7, 2010 at 10:42 pm

#227872

Hi,

I am trying to convert some data in xml, using FOR XML PATH.

I am very new to FOR XML, hence need help in my query.

query for sample data:

;WITH SampleData(CustomerID,AddressID,StandardName,CustomName,AddressValue)

AS

(

SELECT 427 AS CustomerID,0 AS AddressID,'Address' AS StandardName,'Address' AS CustomName,'pune' AS AddressValue UNION ALL

SELECT 427,0,'City','City','Pune' UNION ALL

SELECT 427,0,'Cityid','Cityid','31' UNION ALL

SELECT 427,0,'CityTownLocality','City','' UNION ALL

SELECT 427,0,'Country','Country','India' UNION ALL

SELECT 427,0,'Countryid','Countryid','107' UNION ALL

SELECT 427,0,'CustomerName','CustomerName','Cust-26Aug1' UNION ALL

SELECT 427,0,'FlatNFloor_Number','Flat And Floor Number','pune' UNION ALL

SELECT 427,0,'HBSA_Name','House/Apartment Name','pune' UNION ALL

SELECT 427,0,'HBSA_Number','House/Apartment Number','pune' UNION ALL

SELECT 427,0,'PostalCode','Post Code','pune' UNION ALL

SELECT 427,0,'State','State','Maharashtra' UNION ALL

SELECT 427,0,'Stateid','Stateid','116' UNION ALL

SELECT 427,0,'Street','Street Name','pune' UNION ALL

SELECT 427,0,'Street_Number','Street Number','' UNION ALL

SELECT 427,1,'Address','Address',NULL UNION ALL

SELECT 427,1,'City','City','Pune' UNION ALL

SELECT 427,1,'Cityid','Cityid','31' UNION ALL

SELECT 427,1,'CityTownLocality','Town','pune' UNION ALL

SELECT 427,1,'Country','Country','India' UNION ALL

SELECT 427,1,'Countryid','Countryid','107' UNION ALL

SELECT 427,1,'CustomerName','CustomerName','Cust-26Aug1' UNION ALL

SELECT 427,1,'FlatNFloor_Number','Flat And Floor Number','pune' UNION ALL

SELECT 427,1,'HBSA_Name','House/Apartment Name','pune' UNION ALL

SELECT 427,1,'HBSA_Number','House/Apartment Number','pune' UNION ALL

SELECT 427,1,'PostalCode','Post Code','pune' UNION ALL

SELECT 427,1,'State','State','Maharashtra' UNION ALL

SELECT 427,1,'Stateid','Stateid','116' UNION ALL

SELECT 427,1,'Street','Street Name','pune' UNION ALL

SELECT 427,1,'Street_Number','Street Number','' UNION ALL

SELECT 427,2,'Address','Address',NULL UNION ALL

SELECT 427,2,'City','City','Pune' UNION ALL

SELECT 427,2,'Cityid','Cityid','31' UNION ALL

SELECT 427,2,'CityTownLocality','Tahasil','pune' UNION ALL

SELECT 427,2,'Country','Country','India' UNION ALL

SELECT 427,2,'Countryid','Countryid','107' UNION ALL

SELECT 427,2,'CustomerName','CustomerName','Cust-26Aug1' UNION ALL

SELECT 427,2,'FlatNFloor_Number','Flat No','pune' UNION ALL

SELECT 427,2,'HBSA_Name','House/Apartment','pune' UNION ALL

SELECT 427,2,'HBSA_Number','House/Apartment No','pune' UNION ALL

SELECT 427,2,'PostalCode','PinCode','pune' UNION ALL

SELECT 427,2,'State','State','Maharashtra' UNION ALL

SELECT 427,2,'Stateid','Stateid','116' UNION ALL

SELECT 427,2,'Street','Street Name','pune' UNION ALL

SELECT 427,2,'Street_Number','Street Number','' UNION ALL

SELECT 428,5,'Address','Address',NULL UNION ALL

SELECT 428,5,'City','City','Pune' UNION ALL

SELECT 428,5,'Cityid','Cityid','31' UNION ALL

SELECT 428,5,'CityTownLocality','City/Town/Tahasil','pune' UNION ALL

SELECT 428,5,'Country','Country','India' UNION ALL

SELECT 428,5,'Countryid','Countryid','107' UNION ALL

SELECT 428,5,'CustomerName','CustomerName','Cust-26Aug1' UNION ALL

SELECT 428,5,'FlatNFloor_Number','Flat And Floor Number','pune' UNION ALL

SELECT 428,5,'HBSA_Name','House/Apartment Name','pune' UNION ALL

SELECT 428,5,'HBSA_Number','House/Apartment Number','pune' UNION ALL

SELECT 428,5,'PostalCode','Post Code','pune' UNION ALL

SELECT 428,5,'State','State','Maharashtra' UNION ALL

SELECT 428,5,'Stateid','Stateid','116' UNION ALL

SELECT 428,5,'Street','Street Name','pune' UNION ALL

SELECT 428,5,'Street_Number','Street Number','' UNION ALL

SELECT 428,6,'Address','Address',NULL UNION ALL

SELECT 428,6,'City','City','Pune' UNION ALL

SELECT 428,6,'Cityid','Cityid','31' UNION ALL

SELECT 428,6,'CityTownLocality','City/Town/Tahasil','' UNION ALL

SELECT 428,6,'Country','Country','India' UNION ALL

SELECT 428,6,'Countryid','Countryid','107' UNION ALL

SELECT 428,6,'CustomerName','CustomerName','Cust-26Aug1' UNION ALL

SELECT 428,6,'FlatNFloor_Number','Flat And Floor Number','' UNION ALL

SELECT 428,6,'HBSA_Name','House/Apartment Name','' UNION ALL

SELECT 428,6,'HBSA_Number','House/Apartment Number','' UNION ALL

SELECT 428,6,'PostalCode','Post Code','' UNION ALL

SELECT 428,6,'State','State','Maharashtra' UNION ALL

SELECT 428,6,'Stateid','Stateid','116' UNION ALL

SELECT 428,6,'Street','Street Name','' UNION ALL

SELECT 428,6,'Street_Number','Street Number',NULL

)

SELECT CustomerID,AddressID,StandardName,CustomName,AddressValue FROM SampleData

query used for generating xml data:

SELECT

CustomerID AS "@CustomerID"

,AddressID AS "@AddressID"

,StandardName AS "Address/@StandardName"

,CustomName AS "Address/@CustomName"

,AddressValue AS "Address/text()"

,StandardName AS "CityTownLocality/@StandardName"

,CustomName AS "CityTownLocality/@CustomName"

,AddressValue AS "CityTownLocality/text()"

,StandardName AS "FlatNFloorNumber/@StandardName"

,CustomName AS "FlatNFloorNumber/@CustomName"

,AddressValue AS "FlatNFloorNumber/text()"

,StandardName AS "HBSA_Name/@StandardName"

,CustomName AS "HBSA_Name/@CustomName"

,AddressValue AS "HBSA_Name/text()"

,StandardName AS "HBSA_Number/@StandardName"

,CustomName AS "HBSA_Number/@CustomName"

,AddressValue AS "HBSA_Number/text()"

,StandardName AS "Street/@StandardName"

,CustomName AS "Street/@CustomName"

,AddressValue AS "Street/text()"

,StandardName AS "Street_Number/@StandardName"

,CustomName AS "Street_Number/@CustomName"

,AddressValue AS "Street_Number/text()"

,StandardName AS "PostalCode/@StandardName"

,CustomName AS "PostalCode/@CustomName"

,AddressValue AS "PostalCode/text()"

,StandardName AS "City/@StandardName"

,CustomName AS "City/@CustomName"

,AddressValue AS "City/text()"

,StandardName AS "State/@StandardName"

,CustomName AS "State/@CustomName"

,AddressValue AS "State/text()"

,StandardName AS "Country/@StandardName"

,CustomName AS "Country/@CustomName"

,AddressValue AS "Country/text()"

FROM SampleData

FOR XML PATH('Customer'), ELEMENTS XSINIL

Expected output:

'<Customer CustomerID="427">

<Addresses AddressID="0">

<Address CustomName="Address"></Address>

<CityTownLocality CustomName="City/Town/Tahasil">Pune</CityTownLocality>

<FlatNFloorNumber CustomName="Flat And Floor Number">A6</FlatNFloorNumber>

<HBSA_Name CustomName="House/Apartment Name"></HBSA_Name>

<HBSA_Number CustomName="House/Apartment Number"></HBSA_Number>

<Street CustomName="Street Name">WestAvenue</Street>

<Street_Number CustomName="Street Number">123</Street_Number>

<PostalCode CustomName="Post Code">pune</PostalCode>

<City>Pune</City>

<State>Mh</State>

<Country>India</Country>

</Addresses>

</Customer>'

actual output:

<Customer xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" CustomerID="427" AddressID="0">

<Address StandardName="Address" CustomName="Address">pune</Address>

<CityTownLocality StandardName="Address" CustomName="Address">pune</CityTownLocality>

<FlatNFloorNumber StandardName="Address" CustomName="Address">pune</FlatNFloorNumber>

<HBSA_Name StandardName="Address" CustomName="Address">pune</HBSA_Name>

<HBSA_Number StandardName="Address" CustomName="Address">pune</HBSA_Number>

<Street StandardName="Address" CustomName="Address">pune</Street>

<Street_Number StandardName="Address" CustomName="Address">pune</Street_Number>

<PostalCode StandardName="Address" CustomName="Address">pune</PostalCode>

<City StandardName="Address" CustomName="Address">pune</City>

<State StandardName="Address" CustomName="Address">pune</State>

<Country StandardName="Address" CustomName="Address">pune</Country>

</Customer>

Can someone please help me in tweaking the query to generate expected output.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

VSSGeorge SSCrazy Eights Points: 8146 More actions · Answer 1

VSSGeorge

SSCrazy Eights

Points: 8146

December 7, 2010 at 11:35 pm

#1259920

Hi

Pls go through the link,

http://www.sql-programmers.com/Blog/tabid/153/EntryId/10/XML-Data-Type-in-SQL-Server-2005.aspx

VSSGeorge SSCrazy Eights Points: 8146 More actions · Answer 2

VSSGeorge

SSCrazy Eights

Points: 8146

December 7, 2010 at 11:49 pm

#1259922

Detailed tutorials are here:

http://beyondrelational.com/blogs/jacob/archive/2009/06/12/for-xml-tutorials.aspx

Shaun-884394 Ten Centuries Points: 1096 More actions · Answer 3

Shaun-884394

Ten Centuries

Points: 1096

December 7, 2010 at 11:53 pm

#1259923

George thanks for the first link, it will help me in querying for xml datatypes.

Also thanks for the second link, will try it out and post my query here.

Shaun-884394 Ten Centuries Points: 1096 More actions · Answer 4

I am trying hard but am not able to go further.

To start with i am first trying to get the below output, will drill down for more later

</Customer>

</Customer>

</Customers>

I used the following query:

SELECT

1 AS Tag,

NULL AS Parent,

NULL AS 'Customers!1!',

NULL AS 'Customer!2!CustomerID',

NULL AS 'Address!3!AddressID'

UNION ALL

SELECT DISTINCT

2 AS Tag,

1 AS Parent,

NULL,

CustomerID,

NULL

FROM SampleData

UNION ALL

SELECT DISTINCT

3 AS Tag,

2 AS Parent,

NULL,

CustomerID,

AddressID

FROM SampleData

FOR XML EXPLICIT

but the output i got is:

</Customer>

</Customers>

Somebody please help...

VSSGeorge SSCrazy Eights Points: 8146 More actions · Answer 5

VSSGeorge

SSCrazy Eights

Points: 8146

December 8, 2010 at 4:59 am

#1260010

Try this one:

SELECT CustomerID , AddressID

FROM SampleData

FOR XML RAW('Customers'), ELEMENTS, ROOT('Customers')

I'm not quite sure abt the result.