Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by (8Microsoft Community Contributo)
  • When: 6 Jun 2013 10:33 AM
  • Last revision by (8Microsoft Community Contributo)
  • When: 8 Sep 2013 4:08 AM
  • Revisions: 6
  • Comments: 7
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  Generate XML with Same Node Names using FOR XML PATH

Generate XML with Same Node Names using FOR XML PATH

Generate XML with Same Node Names using FOR XML PATH

In this post we are going to see how we can generate XML in the below mentioned format from the relational data.

<row>
  <column>1</column>
  <column>1</column>
</row>
<row>
  <column>2</column>
  <column>2</column>
</row>

Here is an example:

--Sample data 
  
DECLARE @Temp TABLE (Id1 INT, Id2 INT)
INSERT @Temp SELECT 1,1
INSERT @Temp SELECT 2,2
SELECT * FROM @Temp
  
--If we mention same alias name for all columns, all column values will be merged
  
SELECT Id1 [column],
       Id2 [column]
FROM @Temp 
FOR XML PATH
  
/**XML result for above query 
<row>
  <column>11</column>
</row>
<row>
  <column>22</column>
</row>
  
 **/
  
--To overcome the above problem 
-- Method 1 :
  
SELECT Id1 [column],
       '',
       Id2 [column]
FROM @Temp 
FOR XML PATH
  
  
-- Method 2 :
  
SELECT Id1 [column],
       NULL,
       Id2 [column]
FROM @Temp 
FOR XML PATH
  
/**XML result for above Method 1 & Method 2 query 
  
<row>
  <column>1</column>
  <column>1</column>
</row>
<row>
  <column>2</column>
  <column>2</column>
</row>
  
**/

This entry participates in the TechNet Guru contributions for June contest.

See also :
http://social.technet.microsoft.com/wiki/contents/articles/17785.sql-server-query-language-transact-sql.aspx
, , , , , , [Edit tags]
Leave a Comment
  • Please add 7 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 7 Jun 2013 8:38 AM

    Naomi  N edited Revision 2. Comment: Added last line about contest

  • 7 Jun 2013 8:35 AM

    Naomi  N edited Revision 1. Comment: Changed casing, added tag

  • 6 Jun 2013 10:46 AM

    Naomi  N edited Original. Comment: Minor grammar corrections

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • Posted by
    on 6 Jun 2013 10:46 AM

    Naomi  N edited Original. Comment: Minor grammar corrections

  • Posted by
    on 6 Jun 2013 10:47 AM

    Will it be Ok to add to June Guru Contributions contest?

  • Posted by
    on 7 Jun 2013 8:22 AM

    Ya sure Naomi N ....

  • Posted by
    on 7 Jun 2013 8:35 AM

    Naomi  N edited Revision 1. Comment: Changed casing, added tag

  • Posted by
    on 7 Jun 2013 8:38 AM

    Naomi  N edited Revision 2. Comment: Added last line about contest

  • Posted by
    on 7 Jun 2013 8:40 AM

    I did it for this article. I believe you recently contributed another article (forgot its title). You may want to do the same with it as I did with this - add to main contributions page, add link at the bottom, add more tags

  • Posted by
    on 7 Jun 2013 10:13 AM

    Naomi - thanks for adding my XML article to the contest , anyways iam not quite sure about adding technet wiki articles to contest ,here is my another post - social.technet.microsoft.com/.../17777.t-sql-remove-leading-and-trailing-zeros.aspx ,kindly add this too if it is eligible to participate in the contest :)

Page 1 of 1 (7 items)