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
Leave a Comment
  • Please add 5 and 6 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Naomi  N edited Revision 2. Comment: Added last line about contest

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

  • 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
  • Naomi  N edited Original. Comment: Minor grammar corrections

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

  • Ya sure Naomi N ....

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

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

  • 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

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