SQL Server XML: massage xml format outside of SQL or within SQL stored proc?

Question

Hi everyone,

I have read through some excellent responses already dealing with SQL XML output posts, although what I need might not be a) best to do within a stored proc and b) not possible within a stored proc.

My question is this:

I have a simple table; col1, col2, col3, col4 .. for the purpose of an example, they are all just varchar fields.

I am trying to get those columns out as xml in specific formatted way. One post looked very similar to what I needed using the FOR XML PATH statement.. it had:

FOR XML PATH('cell'), ROOT('rows')

although this of course looked partially correct, but I still had the actual fieldnames nested within the tag...

But anyway, what I need is:

<rows>
   <cell row='1'>field 1 contents</cell>
   <cell row='2'>field 2 contents</cell>
   ....
</rows>

So because I need to repeat the tag without the actual field name, I am not sure if I can achieve this?? any ideas???

A bonus extra special thank you if I could also do:

<rows totalcount='xxxx' recordsperpage='yyyy'>

So where xxxx=total count of records from the select, and yyyy=the number of records per page, which of course I probably would either have as a variable or param..

Many thanks for any feedback..

EDIT !!!!!!!!!!! OOPS!

Sorry, the format I am looking for is:

<rows>
  <row id='1'>
       <cell>field 1 contents</cell>
       <cell>field 2 contents</cell>
  </row>
</rows>

Doh! anyway.. some great ideas thus far.. thanks so much!

Answer 1

Probably a more eloquent way to do this, but hey it is XML + T-SQL. This code sample should be a guide to getting the XML format you want:

declare @table table (col1 varchar(50), col2 varchar(50), col3 varchar(50))

insert into @table 
values ('val1', 'val2', 'val3'), ('val4', 'val5', 'val6')

SELECT 1 as '@totalcount', 2 as '@recordsperpage'
,(
    SELECT
    col1 as 'cell/@col1',
    col2 as 'cell/@col2',
    col3 as 'cell'
    FROM @table
    FOR XML PATH(''), TYPE
) 
FOR XML PATH('row')

Answer 2

Your desired output isn't clear to me as to how rows in the result set are delimited in the XML

My attempt

WITH R AS
(
SELECT 'field 1 contents' as [1], 'field 2 contents' as [2], 'field 3 contents' as [3], 'field 4 contents' as [4]
UNION ALL
SELECT 'field 1 contents B' as [1], 'field 2 contents B' as [2], 'field 3 contents B' as [3], 'field 4 contents B' as [4]
)
SELECT cell[@row] , row [text()] FROM 
R  UNPIVOT  (row FOR cell IN 
      ([1], [2], [3], [4])
)AS unpvt
FOR XML PATH('cell'), ROOT('rows'), TYPE 

Which gives

<rows>
<cell row="1">field 1 contents</cell>
<cell row="2">field 2 contents</cell>
<cell row="3">field 3 contents</cell>
<cell row="4">field 4 contents</cell>
<cell row="1">field 1 contents B</cell>
<cell row="2">field 2 contents B</cell>
<cell row="3">field 3 contents B</cell>
<cell row="4">field 4 contents B</cell>
</rows>