SQL Server FOR XML 在生成同名节点时出现显式错误

SQL Server FOR XML explicit error in generating same name nodes

提问人：Nimble Fungus 提问时间：12/4/2015 更新时间：4/8/2016 访问量：69

问：

我正在尝试使用XML Explicit生成下面的XML结构

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
<Worksheet ss:Name="1">
    <Table>
        <Row>
            <Cell>
                <Data ss:Type="String">231548</Data>
            </Cell>
            <Cell>
                <Data ss:Type="String">2014-11-03T20:03:30</Data>
            </Cell>
        </Row>
    </Table>
</Worksheet>

我能够生成

 <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  <Worksheet ss:Name="1">
    <Table>
      <Row>
        <Cell>
          <Data ss:Type="String">231548</Data>
        </Cell>
      </Row>
    </Table>
  </Worksheet>
</Workbook>

我无法生成上述结构。 我用于生成结构的代码如下。

SELECT 
    Tag, 
    Parent,
    [Workbook!1!xmlns],
    [Workbook!1!xmlns:ss],
    [Worksheet!2!ss:Name],
    [Table!3!],
    [Row!4!],
    [Cell!5!],
    [Data!6!ss:Type],
    [Data!6!]
FROM ( 
SELECT top 5
    1 AS Tag,
    0 AS Parent,
    0 as SORT,
    'urn:schemas-microsoft-com:office:spreadsheet' AS [Workbook!1!xmlns],
    'urn:schemas-microsoft-com:office:spreadsheet' AS [Workbook!1!xmlns:ss],
    NULL AS [Worksheet!2!ss:Name],
    NULL AS [Table!3!],
    NULL AS [Row!4!],
    NULL AS [Cell!5!],
    NULL AS [Data!6!ss:Type],
    NULL AS [Data!6!]

UNION ALL
SELECT top 5
    2 AS Tag,
    1 AS Parent,
    AuditID * 100  as SORT,
    NULL ,
    NULL ,
    Null,
    NULL ,
    NULL ,
    NULL ,
    NULL,
    NULL 
From TempAudits
UNION ALL
SELECT top 5
    3 AS Tag,
    2 AS Parent,
    AuditID * 100+1  as SORT,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL,
    NULL 
From TempAudits
UNION ALL
SELECT top 5
    4 AS Tag,
    3 AS Parent,
    AuditID * 100+2  as SORT,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL ,
    NULL,
    NULL 
    From TempAudits
UNION ALL
SELECT top 5
    5 AS Tag,
    4 AS Parent,
    AuditID * 100+3  as SORT,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL
FROM TempAudits
UNION ALL
SELECT top 5
    6 AS Tag,
    5 AS Parent,
    AuditID * 100+4  as SORT,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    NULL,
    'String' AS TYPE,
    AuditID AS Data
FROM TempAudits) Aa
Order by Sort
FOR XML EXPLICIT

任何人都可以帮助我。 任何帮助将不胜感激。 我正在使用此代码生成一个可以在 excel 中打开的 xml。

sql sql-server t-sql for-xml-explicit

评论

2赞 marc_s 12/4/2015
看看 FOR XML PATH，它通常比显式模式容易得多，并且几乎可以做任何事情
0赞 Nimble Fungus 12/5/2015
@marc_s 感谢您的建议。我也在尝试。你能帮我解决上述问题吗？
0赞 Nimble Fungus 12/7/2015
@marc_s你能帮我生成相同的XML吗 用于XML路径。
0赞 marc_s 12/7/2015
如果您可以提供带有表结构和一些示例数据的 SQL Fiddle - 是的，可能
0赞 Nimble Fungus 12/7/2015
@marc_s 感谢您抽出时间帮助我。我在 [link] 中创建了表和示例条目 sqlfiddle.com/#!9/126210 我正在尝试的 XML 结构我已将其粘贴到 [link] pastebin.com/uxDubTTh 。

答：

0赞 DaveFoyf 4/8/2016 #1

假设您确实希望使用 XML Explicit 来生成此输出，则可以为 Cell 和 Data 添加另外两个标记，并添加两个后续的联合查询。

编辑：我已将 1 添加到标记 2 的 Worksheet！2！ss：Name 列中，以确保输出符合您的要求。

SELECT 
    Tag, 
    Parent,
    [Workbook!1!xmlns],
    [Workbook!1!xmlns:ss],
    [Worksheet!2!ss:Name],
    [Table!3!],
    [Row!4!],
    [Cell!5!],
    [Data!6!ss:Type],
    [Data!6!],
    [Cell!7!],
    [Data!8!ss:Type],
    [Data!8!]

FROM 
( 
    SELECT top 5
        1 AS Tag,
        0 AS Parent,
        0 as SORT,
        'urn:schemas-microsoft-com:office:spreadsheet' AS [Workbook!1!xmlns],
        'urn:schemas-microsoft-com:office:spreadsheet' AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]

    UNION ALL
    SELECT top 5
        2 AS Tag,
        1 AS Parent,
        AuditID * 100  as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        1 AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
    From TempAudits

    UNION ALL
    SELECT top 5
        3 AS Tag,
        2 AS Parent,
        AuditID * 100+1  as SORT,
         NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
    From TempAudits

    UNION ALL
    SELECT top 5
        4 AS Tag,
        3 AS Parent,
        AuditID * 100+2  as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
        From TempAudits

    UNION ALL
    SELECT top 5
        5 AS Tag,
        4 AS Parent,
        AuditID * 100 + 3  as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
    FROM TempAudits

    UNION ALL
    SELECT top 5
        6 AS Tag,
        5 AS Parent,
        AuditID * 100 + 4 as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        'String' AS [Data!6!ss:Type],
        AuditID AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
    FROM 
    TempAudits

    UNION ALL
    SELECT top 5
       7 AS Tag,
        4 AS Parent,
        AuditID * 100 + 5 as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        NULL AS [Data!8!ss:Type],
        NULL AS [Data!8!]
    FROM TempAudits

    UNION ALL
    SELECT top 5
        8 AS Tag,
        7 AS Parent,
        AuditID * 100 + 6 as SORT,
        NULL AS [Workbook!1!xmlns],
        NULL AS [Workbook!1!xmlns:ss],
        NULL AS [Worksheet!2!ss:Name],
        NULL AS [Table!3!],
        NULL AS [Row!4!],
        NULL AS [Cell!5!],
        NULL AS [Data!6!ss:Type],
        NULL AS [Data!6!],
        NULL AS [Cell!7!],
        'String' AS [Data!8!ss:Type],
        AuditDate AS [Data!8!]
    FROM 
    TempAudits 
) Aa
Order by Sort
FOR XML EXPLICIT

上一个：使用 FOR XML EXPLICIT 时删除 xmlns=“” 名称空间声明

下一个：XML 显式按父标记分组不起作用