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
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Will it be Ok to add to June Guru Contributions contest?

  • Ya sure Naomi N ....

  • 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 (4 items)