0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459

2 Answers2

0

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
JamieD77
  • 13,796
  • 1
  • 17
  • 27
  • Thanks a lot JamieD77!! That worked for me!!. Now I have to apply it on a table with 24 columns. It will be an "UNION ALL festival" statement :-/ – AcaSurf AcaMaster Jul 24 '15 at 19:32
0

I manage to found a much better aproach using the FOR XML PATH mode:

DECLARE @T AS TABLE (
  col1 VARCHAR(20),
  col2 VARCHAR(20),
  col3 VARCHAR(20)
);

INSERT INTO @T VALUES
  ('Row1 Col1','Row1 Col2','Row1 Col3'),
  ('Row2 Col1',NULL,'Row2 Col3'),
  ('Row3 Col1','Row3 Col2',NULL);

SELECT
  '1'      as 'Cell/@Index',
  's1'     as 'Cell/@StyleID',
  'String' as 'Cell/Data/@Type',
  col1     as 'Cell/Data',
  '',
  '2'      as 'Cell/@Index',
  's2'     as 'Cell/@StyleID',
  'String' as 'Cell/Data/@Type',
  col2     as 'Cell/Data',
  '',
  '3'      as 'Cell/@Index',
  's3'     as 'Cell/@StyleID',
  'String' as 'Cell/Data/@Type',
  col3     as 'Cell/Data'
FROM @T
FOR XML PATH ('Row')

GO

I got the desired result:

<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>
  <Cell Index="3" StyleID="s3">
    <Data Type="String">Row1 Col3</Data>
  </Cell>
</Row>
<Row>
  <Cell Index="1" StyleID="s1">
    <Data Type="String">Row2 Col1</Data>
  </Cell>
  <Cell Index="2" StyleID="s2">
    <Data Type="String" />
  </Cell>
  <Cell Index="3" StyleID="s3">
    <Data Type="String">Row2 Col3</Data>
  </Cell>
</Row>
<Row>
  <Cell Index="1" StyleID="s1">
    <Data Type="String">Row3 Col1</Data>
  </Cell>
  <Cell Index="2" StyleID="s2">
    <Data Type="String">Row3 Col2</Data>
  </Cell>
  <Cell Index="3" StyleID="s3">
    <Data Type="String" />
  </Cell>
</Row>

Adding empty strings (´´) in the sql statement do the trick.

But it is getting better: I do not want to print tags for NULL values. How should I manage this? This is the answer:

DECLARE @T AS TABLE (
  col1 VARCHAR(20),
  col2 VARCHAR(20),
  col3 VARCHAR(20)
);

INSERT INTO @T VALUES
  ('Row1 Col1','Row1 Col2','Row1 Col3'),
  ('Row2 Col1',NULL,'Row2 Col3'),
  ('Row3 Col1','Row3 Col2',NULL);

SELECT
  'Cell/@Index'     = case when col1 is not null then '1'      else NULL end,
  'Cell/@StyleID'   = case when col1 is not null then 's1'     else NULL end,
  'Cell/Data/@Type' = case when col1 is not null then 'String' else NULL end,
  col1              as 'Cell/Data',
  '',
  'Cell/@Index'     = case when col2 is not null then '2'      else NULL end,
  'Cell/@StyleID'   = case when col2 is not null then 's2'     else NULL end,
  'Cell/Data/@Type' = case when col2 is not null then 'String' else NULL end,
  col2              as 'Cell/Data',
  '',
  'Cell/@Index'     = case when col3 is not null then '3'      else NULL end,
  'Cell/@StyleID'   = case when col3 is not null then 's3'     else NULL end,
  'Cell/Data/@Type' = case when col3 is not null then 'String' else NULL end,
  col3              as 'Cell/Data'
FROM @T
FOR XML PATH ('Row')

GO

I got a much better result:

<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>
  <Cell Index="3" StyleID="s3">
    <Data Type="String">Row1 Col3</Data>
  </Cell>
</Row>
<Row>
  <Cell Index="1" StyleID="s1">
    <Data Type="String">Row2 Col1</Data>
  </Cell>
  <Cell Index="3" StyleID="s3">
    <Data Type="String">Row2 Col3</Data>
  </Cell>
</Row>
<Row>
  <Cell Index="1" StyleID="s1">
    <Data Type="String">Row3 Col1</Data>
  </Cell>
  <Cell Index="2" StyleID="s2">
    <Data Type="String">Row3 Col2</Data>
  </Cell>
</Row>

I did it with CASE on SQLS 2008. For SQLS 2012 you can use IIF instead.

Finally, I want to add a namespace indicator on attributes. I did this:

DECLARE @T AS TABLE (
  col1 VARCHAR(20),
  col2 VARCHAR(20),
  col3 VARCHAR(20)
);

INSERT INTO @T VALUES
  ('Row1 Col1','Row1 Col2','Row1 Col3'),
  ('Row2 Col1',NULL,'Row2 Col3'),
  ('Row3 Col1','Row3 Col2',NULL);

WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:spreadsheet' as ss)
SELECT
  'Cell/@ss:Index'     = case when col1 is not null then '1'      else NULL end,
  'Cell/@ss:StyleID'   = case when col1 is not null then 's1'     else NULL end,
  'Cell/Data/@ss:Type' = case when col1 is not null then 'String' else NULL end,
  col1              as 'Cell/Data',
  '',
  'Cell/@ss:Index'     = case when col2 is not null then '2'      else NULL end,
  'Cell/@ss:StyleID'   = case when col2 is not null then 's2'     else NULL end,
  'Cell/Data/@ss:Type' = case when col2 is not null then 'String' else NULL end,
  col2              as 'Cell/Data',
  '',
  'Cell/@ss:Index'     = case when col3 is not null then '3'      else NULL end,
  'Cell/@ss:StyleID'   = case when col3 is not null then 's3'     else NULL end,
  'Cell/Data/@ss:Type' = case when col3 is not null then 'String' else NULL end,
  col3              as 'Cell/Data'
FROM @T
FOR XML PATH ('Row'), ROOT ('Worksheet')

GO

I got a result much closer from the one I was looking for:

<Worksheet xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <Row>
    <Cell ss:Index="1" ss:StyleID="s1">
      <Data ss:Type="String">Row1 Col1</Data>
    </Cell>
    <Cell ss:Index="2" ss:StyleID="s2">
      <Data ss:Type="String">Row1 Col2</Data>
    </Cell>
    <Cell ss:Index="3" ss:StyleID="s3">
      <Data ss:Type="String">Row1 Col3</Data>
    </Cell>
  </Row>
  <Row>
    <Cell ss:Index="1" ss:StyleID="s1">
      <Data ss:Type="String">Row2 Col1</Data>
    </Cell>
    <Cell ss:Index="3" ss:StyleID="s3">
      <Data ss:Type="String">Row2 Col3</Data>
    </Cell>
  </Row>
  <Row>
    <Cell ss:Index="1" ss:StyleID="s1">
      <Data ss:Type="String">Row3 Col1</Data>
    </Cell>
    <Cell ss:Index="2" ss:StyleID="s2">
      <Data ss:Type="String">Row3 Col2</Data>
    </Cell>
  </Row>
</Worksheet>

I have to insert this fragment in the middle of a XML document, so it would be great if I can get rid of both first and last lines in order to concatenate fixed header and footer.

Hope this helps someone else.