none
Custom Xml and Sql Server 2008

    Dotaz

  •  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.


    30. dubna 2012 4:22

Všechny reakce

  • 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');
    

    30. dubna 2012 9:40
  • 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 ).
    30. dubna 2012 10:38
    Přispěvatel
  • Thanku very much...............................

    V!K@S

    30. dubna 2012 10:58
  • Indeed, visual verification is error prone :)
    30. dubna 2012 11:57
  • 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/

    14. května 2012 13:24