locked
retreiving the data from table to xml format RRS feed

  • Question

  • Hi Guys,

    Please help me.

    DECLARE @E XML

    CREATE TABLE #c

    (

    id int

    )

    insert into #C values(1)

    insert into #C values(2)

     

    When I select the data from the above temp table, the data should come as shown in the below xml format.

    <E>

    <CS>

    <C>1</C>

    <C>2</C>

    </CS>

    </E>

    Thanks,

    Anilkumar


    • Edited by Anilkumar P Sunday, January 24, 2016 12:52 PM Correct the question
    Sunday, January 24, 2016 12:23 PM

Answers

  • DECLARE @c TABLE ( id INT );
    
    INSERT INTO @c VALUES ( 1 ), ( 2 );
    
    SELECT	(
    		SELECT	id AS C
    		FROM	@c
    		FOR XML PATH(''), TYPE
    	) AS CS
    FOR XML PATH(''), ROOT('E');

    • Marked as answer by Anilkumar P Sunday, January 24, 2016 1:03 PM
    Sunday, January 24, 2016 12:59 PM

All replies

  • How do you select from table #c?
    It is very important. In this way you get the reuslt in XML or other format
    For getting XML try the following code:

    SELECT col1 as c1 FROM #C FOR XML PATH (''), ROOT('E')


    Thanks in advance, Ciprian LUPU


    Sunday, January 24, 2016 12:42 PM
  • select id as c from #c
    FOR XML PATH (''),ROOT('e')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, January 24, 2016 12:42 PM
    Answerer
  • Take a look at the FOR XML PATH clause:

    DECLARE @c TABLE ( id INT );
    
    INSERT INTO @c VALUES ( 1 ), ( 2 );
    
    SELECT	id AS C
    FROM	@c
    FOR XML PATH(''), ROOT('E');

    Sunday, January 24, 2016 12:46 PM
  • ... or you can put simply FOR XML AUTO at the end of SELECT query.

    Something like in example:

    SELECT * FROM #C
    FOR XML AUTO


    Thanks in advance, Ciprian LUPU

    Sunday, January 24, 2016 12:47 PM
  • Hello,

    Thank you for the reply.

    SET @E = ( SELECT ( SELECT C.id FOR XML PATH('C') ) FROM #C AS C FOR XML PATH(''),ROOT('E') ) SELECT @E

    Above is how we try to write query to fetch the data in the form of xml.

    Thanks,

    Anilkumar

    Sunday, January 24, 2016 12:48 PM
  • select id as c from #c
    FOR XML PATH (''),ROOT('e')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Hi,

    Thank you for your reply.

    If I write the query which is given, the, I will get the result as below

    <E>

    <c>1</c>

    <c>2</c>

    </E>

    But what I am expecting would be as below.

    <E>

    <columns>

    <c>1</c>

    <c>2</c>

    </columns>

    </E>

    Sorry, I will edit my post.

    Thanks,

    Anilkumar


    • Edited by Anilkumar P Sunday, January 24, 2016 12:56 PM
    Sunday, January 24, 2016 12:51 PM
  • ... or you can put simply FOR XML AUTO at the end of SELECT query.

    Something like in example:

    SELECT * FROM #C
    FOR XML AUTO


    Thanks in advance, Ciprian LUPU

    But what I am expecting would be as below.

    <E>

    <columns>

    <c>1</c>

    <c>2</c>

    </columns>

    </E>



    • Edited by Anilkumar P Sunday, January 24, 2016 12:55 PM
    Sunday, January 24, 2016 12:53 PM
  • Take a look at the FOR XML PATH clause:

    DECLARE @c TABLE ( id INT );
    
    INSERT INTO @c VALUES ( 1 ), ( 2 );
    
    SELECT	id AS C
    FROM	@c
    FOR XML PATH(''), ROOT('E');

    But what I am expecting would be as below.

    <E>

    <columns>

    <c>1</c>

    <c>2</c>

    </columns>

    </E>



    • Edited by Anilkumar P Sunday, January 24, 2016 12:55 PM
    Sunday, January 24, 2016 12:53 PM
  • DECLARE @c TABLE ( id INT );
    
    INSERT INTO @c VALUES ( 1 ), ( 2 );
    
    SELECT	(
    		SELECT	id AS C
    		FROM	@c
    		FOR XML PATH(''), TYPE
    	) AS CS
    FOR XML PATH(''), ROOT('E');

    • Marked as answer by Anilkumar P Sunday, January 24, 2016 1:03 PM
    Sunday, January 24, 2016 12:59 PM
  • DECLARE @c TABLE ( id INT );
    
    INSERT INTO @c VALUES ( 1 ), ( 2 );
    
    SELECT	(
    		SELECT	id AS C
    		FROM	@c
    		FOR XML PATH(''), TYPE
    	) AS CS
    FOR XML PATH(''), ROOT('E');


    Thanks a Lot Stefan. It Worked. If you got any blogs of yours regarding XML. Please share the links. Thanks again.
    Sunday, January 24, 2016 1:03 PM
  • Hi Anilkumar P,

    The following script gives you the same result. If the column name specified is a wildcard character (*), the content of that column is inserted as if there is no column name specified.

    DECLARE @c TABLE ( id INT );
    
    INSERT INTO @c VALUES ( 1 ), ( 2 );
              
    SELECT (
    		SELECT id "*"
    		FROM @c
    		FOR XML PATH('C'), TYPE
    		)
    FOR XML PATH('CS'), ROOT('E')

    Sam Zha
    TechNet Community Support

    Monday, January 25, 2016 7:38 AM