For XML returning All column names as same element

Question

I have a requirement to return xml as follow

<row id="1">
<cell>1</cell>
<cell>setup/pagemst</cell>
<cell>Page Master</cell>
</row>
<row id="2">
<cell>2</cell>
<cell>setup/modules</cell>
<cell>Module Master</cell>
</row>

I used the following Query but it does not work

select 
pageid id,pgurl cell,pgname cell
from m_pages
for xml raw

The same column names for all columns works fine in oracle but not in SQL Server 2005. Any Ideas ?

Thanks in advance deb

Answer 1

Use the FOR XML PATH syntax available in SQL Server 2005 and newer - try something like this:

DECLARE @table TABLE (PageID INT, PageUrl VARCHAR(50), PageName VARCHAR(50))

INSERT INTO @table VALUES(1, 'setup/pagemst', 'Page Master'),(2, 'setup/modules', 'Module Master')

select 
    PageID AS '@id',
    PageID AS 'cell',
    '',
    PageUrl AS 'cell',
    '',
    PageName AS 'cell'
from @table
FOR XML PATH('row')

Gives me the output in the end:

<row id="1">
  <cell>1</cell>
  <cell>setup/pagemst</cell>
  <cell>Page Master</cell>
</row>
<row id="2">
  <cell>2</cell>
  <cell>setup/modules</cell>
  <cell>Module Master</cell>
</row>

The FOR XML PATH syntax allows you to define what values to return as XML attributes (... AS '@id') or XML elements. By adding "empty" lines between the elements, you prevent the output from being merged / concatenated into a single <cell>....</cell> XML element