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