1 setup/pagem...">
Clever Geek Handbook

To return XML All column names in one element

I have a requirement to return the xml as follows

<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 doesn't work

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

The same column names for all columns work fine in oracle, but not in SQL Server 2005. Any ideas?

Thanks in advance Deb

+3


source to share


1 answer


Use the syntax FOR XML PATH

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 result at 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 syntax FOR XML PATH

allows you to define which values ​​to return as XML attributes ( ... AS '@id'

) or XML elements. By adding "blank" lines between elements, you prevent the output from being concatenated or concatenated into a single XML element.<cell>....</cell>

+3


source






More articles:


All Articles