Custom Xml and Sql Server 2008

Question

 I have created xml from sql server 2008. My Current xml is generated using "FOR XML AUTO"

<Customer Name="john" City="Mumbai">
  <Project Project_Name="pqr" />
</Customer>
<Customer Name="Rocky" City="Delhi">
  <Project Project_Name="abc" />
  <Project Project_Name="lmn" />
</Customer>

But I want the output like

<Customer >
   <name>John</name>
   <city>Mumbai</city>  
   <Projects>
      <project>
         <Project_Name>pqr</Project_Name>
      </project>
   </Projects>
</Customer>
<Customer >
   <name>Rocky</name>
   <city>Delhi</city>  
   <Projects>
      <project>
         <Project_Name>abc</Project_Name>
        <Project_Name>lmn</Project_Name>
      </project>
   </Projects>
</Customer>

So basically i want to convert attributes of the parent element to sub-elements. And want additional customize elements. Please help me.

Thanks in Advance.

Answer 1

E.g.

DECLARE @Customer TABLE
	(
	  [Name] VARCHAR(255) ,
	  City VARCHAR(255)
	);

DECLARE @Project TABLE
	(
	  Project_Name VARCHAR(255) ,
	  Customer_Name VARCHAR(255)
	);

INSERT  INTO @Customer
VALUES  ( 'John', 'Mumbai' ),
		( 'Rocky', 'Delhi' );

INSERT  INTO @Project
VALUES  ( 'pqr', 'John' ),
		( 'abc', 'Rocky' ),
		( 'lmn', 'Rocky' );

SELECT  Name ,
		City ,
		( SELECT    Project_Name
		  FROM      @Project Project
		  WHERE     Project.Customer_Name = Customer.[Name]
		  FOR       XML AUTO , TYPE
		) AS Projects
FROM    @Customer Customer
FOR     XML PATH('Customer');

Answer 2

I would do it using FOR XML PATH, eg

SET NOCOUNT ON

DECLARE @customer TABLE ( Name VARCHAR(20), City VARCHAR(20) )
DECLARE @project TABLE ( Project_Name VARCHAR(20), Customer_Name VARCHAR(20) )

INSERT INTO @customer ( Name, City )
VALUES ( 'John', 'Mumbai' ), ( 'Rocky', 'Delhi' )

INSERT INTO @project ( Project_Name, Customer_Name )
VALUES ( 'pqr', 'John' ), ( 'abc', 'Rocky' ), ( 'lmn', 'Rocky' )

SELECT 
	Name AS "name",
	City AS city,
	(
	SELECT
		p.Project_Name AS "Project_Name"
	FROM @project p
	WHERE c.Name = p.Customer_Name
	FOR XML PATH(''), ROOT('project'), TYPE
	) AS "Projects"
FROM @customer c
FOR XML PATH('Customer'), TYPE

(Stefan, the FOR XML AUTO section of your response returns Project_Name as an attribute.  If you use the ELEMENTS keyword, it wraps each line in an extra project element - so it isn't quite right ).

Answer 3

Thanku very much...............................

---

V!K@S

Answer 4

Indeed, visual verification is error prone :)

Answer 5

ho you might check this out, it may help you.

http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/