Generate XML with Same Node Names using FOR XML PATH

  • Article

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 :