FOR XML EXPLICIT - повторяет значения атрибутов

Мне нужно сгенерировать EXCEL Like XML-файл, вставляя данные из таблицы в SQL Server. После некоторых исследований у меня есть следующий скрипт SQL Server, использующий режим FOR XML EXPLICIT:

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

Результат, который я получаю:

<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>

Результат, который я ожидаю:

<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>

Любая помощь будет оценена.

Источник

2 ответа

Решение

Это должно дать вам желаемый результат, но вы не уверены, применимо ли это только к данным теста, которые вы дали.

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

хитрость заключается в том, чтобы получить данные в правильном порядке перед вызовом FOR XML EXPLICIT это должно выглядеть примерно так

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
Источник

Мне удается найти гораздо лучший подход, используя режим FOR XML PATH:

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

Я получил желаемый результат:

<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>

Добавление пустых строк (´´) в оператор sql делает свое дело.

Но это становится лучше: я не хочу печатать теги для значений NULL. Как мне справиться с этим? Это ответ:

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

Я получил гораздо лучший результат:

<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>

Я сделал это с CASE на SQLS 2008. Для SQLS 2012 вы можете использовать вместо IIF.

Наконец, я хочу добавить индикатор пространства имен для атрибутов. Я сделал это:

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

Я получил результат намного ближе к тому, который искал:

<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>

Я должен вставить этот фрагмент в середине XML-документа, поэтому было бы здорово, если бы я мог избавиться как от первой, так и от последней строк, чтобы объединить фиксированный верхний и нижний колонтитулы.

Надеюсь, это поможет кому-то еще.

Источник
Другие вопросы по тегам