Select for xml statement to produce excel sheetData format

Question

I am trying to write a sql query that will generate xml in a format I can transform to an excel spreadsheet. So the original sql table contains the following data

id  Run     Name    Outcome
1   Run1    Test1   1
2   Run2    Test2   2

The sql query I have managed to produce so far is as follows

select * from
(
    SELECT * from table1
  ) t
  for xml path('row'), root('sheetData')

This produces the following xml

  <sheetData>
  <row r="1">
    <Run>Run1</Run>
    <Name>Test1</Name>
    <Outcome>1</Outcome>
  </row>
   <row r="2">
    <Run>Run2</Run>
    <Name>Test2</Name>
    <Outcome>2</Outcome>
  </row>
</sheetData>

So what I need to get is the following xml format

  <?xml version="1.0" encoding="UTF-8"?>
<sheetData>
   <row r="1">
      <c r="A1" t="inlineStr">
         <is>
            <t>Run1</t>
         </is>
      </c>
      <c r="B1" t="inlineStr">
         <is>
            <t>Test1</t>
         </is>
      </c>
      <c r="C1" t="inlineStr">
         <is>
            <t>1</t>
         </is>
      </c>       
   </row>
   <row r="2">
      <c r="A2" t="inlineStr">
         <is>
            <t>Run2</t>
         </is>
      </c>
      <c r="B2" t="inlineStr">
         <is>
            <t>Test2</t>
         </is>
      </c>
      <c r="C2" t="inlineStr">
         <is>
            <t>2</t>
         </is>
      </c>       
   </row>
</sheetData>

For now I have resorted to doing an transform of the first xml format to get the desired output, however I was hoping to build a sql query that I can use to generate this directly without having to go through the additional transform. Thanks for your help.

Answer 1

The following will do what you want.

DECLARE @Data TABLE (
    [id] INT,
    [Run] NVARCHAR(20),
    [Name] NVARCHAR(20),
    [Outcome] INT
)

INSERT INTO
    @Data ( [id], [Run], [Name], [Outcome] )
VALUES
    ( 1, 'Run1', 'Test1', 1 ),
    ( 2, 'Run2', 'Test2', 2 )

DECLARE @Xml XML = (
    SELECT
        [Id] AS [@r],
        (SELECT
            'A'+CONVERT(nvarchar(20), [Id]) AS [@r],
            'inlineStr' AS [@t],
            (SELECT
                [Run] AS [t]
            FOR XML PATH('is'), TYPE)
        FOR XML PATH('c'), TYPE),

        (SELECT
            'B'+CONVERT(nvarchar(20), [Id]) AS [@r],
            'inlineStr' AS [@t],
            (SELECT
                [Name] AS [t]
            FOR XML PATH('is'), TYPE)
        FOR XML PATH('c'), TYPE),

        (SELECT
            'C'+CONVERT(nvarchar(20), [Id]) AS [@r],
            'inlineStr' AS [@t],
            (SELECT
                [Outcome] AS [t]
            FOR XML PATH('is'), TYPE)
        FOR XML PATH('c'), TYPE)

    FROM
        @Data
    FOR XML PATH('row'), ROOT('sheetData'), TYPE
)
SELECT
    '<?xml version="1.0" encoding="UTF-8"?>'
    +CONVERT(NVARCHAR(MAX), @Xml)