FOR XML EXPLICIT - repeats attributes values

Question

I need to generate an EXCEL Like XML file inserting data from a table in SQL Server. After some research I have the following SQL Server script using the FOR XML EXPLICIT mode:

DECLARE @T AS TABLE (col1 VARCHAR(20), col2 VARCHAR(20));
INSERT INTO @T VALUES('Row1 Col1', 'Row1 Col2');
INSERT INTO @T VALUES('Row2 Col1', 'Row2 Col2');

SELECT	1			as 'Tag'
		,NULL		as 'Parent'
		,NULL		as 'Row!1'
		,NULL		as 'Cell!2'
		,NULL		as 'Cell!2!Index'
		,NULL		as 'Cell!2!StyleID'
		,NULL		as 'Data!3'
		,NULL		as 'Data!3!Type'
		,NULL		as 'Cell!2'
		,NULL		as 'Cell!2!Index'
		,NULL		as 'Cell!2!StyleID'
		,NULL		as 'Data!3'
		,NULL		as 'Data!3!Type'
		,ROW_NUMBER() OVER (ORDER BY col1) as 'Row!1!A!HIDE'
		,1			as 'Row!1!B!HIDE'
FROM @T
UNION ALL
SELECT	2
		,1
		,NULL
		,NULL
		,'1'
		,'s1'
		,NULL
		,NULL
		,NULL
		,'2'
		,'s2'
		,NULL
		,NULL
		,ROW_NUMBER() OVER (ORDER BY col1)
		,2
FROM @T
UNION ALL
SELECT	3
		,2
		,NULL
		,NULL
		,NULL
		,NULL
		,col1
		,'String'
		,NULL
		,NULL
		,NULL
		,col2
		,'String'
		,ROW_NUMBER() OVER (ORDER BY col1)
		,3
FROM @T
ORDER BY 14, 15
FOR XML EXPLICIT

GO

The result I get is:

<Row>
  <Cell Index="1" StyleID="s1" Index="2" StyleID="s2">
    <Data Type="String" Type="String">Row1 Col1Row1 Col2</Data>
  </Cell>
</Row>
<Row>
  <Cell Index="1" StyleID="s1" Index="2" StyleID="s2">
    <Data Type="String" Type="String">Row2 Col1Row2 Col2</Data>
  </Cell>
</Row>

The result I expect is:

<Row>
  <Cell Index="1" StyleID="s1">
    <Data Type="String">Row1 Col1</Data>
  </Cell>
  <Cell Index="2" StyleID="s2">
    <Data Type="String">Row1 Col2</Data>
  </Cell>
</Row>
<Row>
  <Cell Index="1" StyleID="s1">
    <Data Type="String">Row2 Col1</Data>
  </Cell>
  <Cell Index="2" StyleID="s2">
    <Data Type="String">Row2 Col2</Data>
  </Cell>
</Row>

Any help would be appreciated.

Answer 1

this should get you the result you want but not sure if this is just applicable to the test data you gave.

SELECT
    1 AS 'Tag',
    NULL AS 'Parent',
    NULL AS 'Row!1',
    NULL AS 'Cell!2',
    NULL AS 'Cell!2!Index',
    NULL AS 'Cell!2!StyleID',
    NULL AS 'Data!3',
    NULL AS 'Data!3!Type',
    ROW_NUMBER() OVER (ORDER BY col1) AS 'Row!1!A!HIDE',
    1 AS 'Row!1!B!HIDE'
FROM
    @T
UNION ALL
SELECT
    2,
    1,
    NULL,
    NULL,
    '1',
    's1',
    NULL,
    NULL,
    ROW_NUMBER() OVER (ORDER BY col1),
    2
FROM
    @T
UNION ALL
SELECT
    3,
    2,
    NULL,
    NULL,
    NULL,
    NULL,
    col1,
    'String',
    ROW_NUMBER() OVER (ORDER BY col1),
    2
FROM
    @T
UNION ALL
SELECT
    2,
    1,
    NULL,
    NULL,
    '2',
    's2',
    NULL,
    NULL,
    ROW_NUMBER() OVER (ORDER BY col1),
    3
FROM
    @T
UNION ALL    
SELECT
    3,
    2,
    NULL,
    NULL,
    NULL,
    NULL,
    col2,
    'String',
    ROW_NUMBER() OVER (ORDER BY col1),
    3
FROM
    @T
ORDER BY
    9,
    10
FOR   
    XML EXPLICIT 

GO

the trick is to getting the data in the correct order before calling FOR XML EXPLICIT it should look similar to this

Tag Parent  Row!1  Cell!2  Cell!2!Index  Cell!2!StyleID  Data!3     Data!3!Type  Row!1!A!HIDE  Row!1!B!HIDE
1   NULL    NULL   NULL    NULL          NULL            NULL       NULL         1             1
2   1       NULL   NULL    1             s1              NULL       NULL         1             2
3   2       NULL   NULL    NULL          NULL            Row1 Col1  String       1             2
2   1       NULL   NULL    2             s2              NULL       NULL         1             3
3   2       NULL   NULL    NULL          NULL            Row1 Col2  String       1             3
1   NULL    NULL   NULL    NULL          NULL            NULL       NULL         2             1
2   1       NULL   NULL    1             s1              NULL       NULL         2             2
3   2       NULL   NULL    NULL          NULL            Row2 Col1  String       2             2
2   1       NULL   NULL    2             s2              NULL       NULL         2             3
3   2       NULL   NULL    NULL          NULL            Row2 Col2  String       2             3