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.