使用 FOR XML 从 SQL Server 2005 数据创建 XML

Question

我正在尝试创建一个 Excel XML，我想将其存储在 SQL Server 2005 中的 XML 字段中。我已经做到了这一点：

WITH XMLNAMESPACES (
  'urn:schemas-microsoft-com:office:spreadsheet' as "s",
  'urn:schemas-microsoft-com:office:office' as "o",
  'urn:schemas-microsoft-com:office:excel' as "x"
)
select 'Order' as "@s:Name",
(
    select
        'String' as 's:Cell/s:Data/@s:Type',
        [Order] as 's:Cell/s:Data',
        null as 'tmp',
        'String' as 's:Cell/s:Data/@s:Type',
        [Material] as 's:Cell/s:Data',
        null as 'tmp',
        'String' as 's:Cell/s:Data/@s:Type',
        [Ship-To] as 's:Cell/s:Data'
    from
    (
        select
            'Order' as [Order],
            'Material' as [Material],
            'Ship-To' as [Ship-To]
        union all
        select
            [Order],
            [Material],
            [Ship-To]
        from Orders
        WHERE [Material] IN(1234,5678))
    ) as Temp
    FOR XML PATH('s:Row'), type
) AS 's:Table'
FOR XML PATH('s:Worksheet'), root('s:Workbook')

这是我的输出：

<s:Workbook xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
  <s:Worksheet s:Name="Order">
    <s:Table>
      <s:Row xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
        <s:Cell>
          <s:Data s:Type="String">Order</s:Data>
        </s:Cell>
        <s:Cell>
          <s:Data s:Type="String">Material</s:Data>
        </s:Cell>
        <s:Cell>
          <s:Data s:Type="String">Ship-To</s:Data>
        </s:Cell>
      </s:Row>
      <s:Row xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
        <s:Cell>
          <s:Data s:Type="String">200909</s:Data>
        </s:Cell>
        <s:Cell>
          <s:Data s:Type="String">1234</s:Data>
        </s:Cell>
        <s:Cell>
          <s:Data s:Type="String">US</s:Data>
        </s:Cell>
      </s:Row>
      <s:Row xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:s="urn:schemas-microsoft-com:office:spreadsheet">
        <s:Cell>
          <s:Data s:Type="String">200909</s:Data>
        </s:Cell>
        <s:Cell>
          <s:Data s:Type="String">5678</s:Data>
        </s:Cell>
        <s:Cell>
          <s:Data s:Type="String">ASIA</s:Data>
        </s:Cell>
      </s:Row>
    </s:Table>
  </s:Worksheet>
</s:Workbook>

我想要的是消除 节点。我想摆脱这个： xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:s=" urn:schemas-microsoft-com:office:spreadsheet" 来自

有人知道如何执行此操作吗？

Answer 1

每个 FOR XML 子句都会添加一个命名空间声明。我知道摆脱它们的唯一方法是在一个“for xml”查询中构建整个文档，但这是不可行的。