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.

• VSSGeorge

  SSCrazy Eights

  Points: 8151

  More actions

  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: 8151

  More actions

  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

  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

  December 8, 2010 at 1:04 am

  #1259938

  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

  <Customers>

  <Customer CustomerID="427" />

  <Address AddressID="0" />

  <Address AddressID="1" />

  <Address AddressID="2" />

  </Customer>

  <Customer CustomerID="428">

  <Address AddressID="5" />

  <Address AddressID="6" />

  </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:

  <Customers>

  <Customer CustomerID="427" />

  <Customer CustomerID="428">

  <Address AddressID="0" />

  <Address AddressID="1" />

  <Address AddressID="2" />

  <Address AddressID="5" />

  <Address AddressID="6" />

  </Customer>

  </Customers>

  Somebody please help...

• VSSGeorge

  SSCrazy Eights

  Points: 8151

  More actions

  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.