我得到了一个 XML 文档，我想通过 SQL 脚本生成它，但我没有做过类似的事情，也找不到任何可以引导我生成最终 XML 的示例我需要(而且我不确定哪种可能的方法更适合我的需要 - EXPLICIT 或 PATH 或者它是否可能)。 我希望有从 SQL 生成 XML 经验的人能够为我指明正确的方向(或者告诉我我正在尝试做的事情是不可能的，我需要通过子查询来完成)。

场景是我从单个表返回产品详细信息(我宁愿不必为我需要的每个值执行子查询)。

我希望能够生成的 xml 看起来像(我无法控制这种格式):

<records>
    <record>
        <fields>
            <field name="id">
                <values>
                    <value>666111</value>
                </values>
            </field>
            <field name="name">
                <values>
                    <value>
                        <![CDATA[My Product Title]]>
                    </value>
                </values>
            </field>
        </fields>
    </record>
    <record>
        ...
    </record>
</records>

我看到的第一个方法是使用 FOR XML PATH

SELECT TOP 2 
    'id' AS "@name",
    p.product_id as [value], 
    p.title
FROM products p 
ORDER BY p.product_id DESC
FOR XML PATH ('field'), ROOT ('fields'), ELEMENTS;

这给了我 XML:

<fields>
  <field name="id">
    <value>20624</value>
    <title>test154</title>
  </field>
  <field name="id">
    <value>20623</value>
    <title>test153</title>
  </field>
</fields>

这给了我所需的 ''，但我无法为下一个元素指定所需的布局。

我还研究了 FOR XML EXPLICIT

SELECT TOP 2
    1 AS Tag, NULL AS Parent,
    p.product_id AS [record!1!product_id!ELEMENT],
    NULL AS [values!2!value!ELEMENT]
FROM products p 
UNION ALL
SELECT TOP 2 
    2, 1,
    p.product_id,
    p.title
FROM products p 
ORDER BY [record!1!product_id!ELEMENT] DESC
FOR XML EXPLICIT;

这给了我以下 XML:

<record>
  <product_id>20624</product_id>
  <values>
    <value>test154</value>
  </values>
</record>
<record>
  <product_id>20623</product_id>
  <values>
    <value>test153</value>
  </values>
</record>

我有点不知所措，无法建立请求或获得正确的东西(我认为我试图在一次查找中做太多事情，这就是我的问题的原因).感谢任何帮助 - 即使它为我提供了一个很好的指南(我发现的唯一一个在示例方面非常差 - 它们没有显示如何构建/更改它们的微妙之处)

最佳答案

这就是您可能正在寻找的查询

中间的 ,'' 是一个技巧，它允许您创建多个具有相同名称的元素，一个在另一个下面...

DECLARE @tbl TABLE(id INT,name VARCHAR(100));
INSERT INTO @tbl VALUES
 (666111,'My Product Title 111')
,(666222,'My Product Title 222');

SELECT 
(
    SELECT  'id' AS [field/@name]
           ,id AS [field/values/value]
           ,''
           ,'name' AS [field/@name]
           ,name AS [field/values/value]
    FOR XML PATH('fields'),TYPE
)
FROM @tbl AS tbl
FOR XML PATH('record'),ROOT('records')

结果

<records>
  <record>
    <fields>
      <field name="id">
        <values>
          <value>666111</value>
        </values>
      </field>
      <field name="name">
        <values>
          <value>My Product Title 111</value>
        </values>
      </field>
    </fields>
  </record>
  <record>
    <fields>
      <field name="id">
        <values>
          <value>666222</value>
        </values>
      </field>
      <field name="name">
        <values>
          <value>My Product Title 222</value>
        </values>
      </field>
    </fields>
  </record>
</records>

更新:据我所知，没有干净的方法来添加CDATA-sections

出于某些原因，Microsoft 的人们认为 CDATA 部分不是必需的。好吧，他们不是，但有时他们仍然需要......

添加CDATA 部分的唯一简洁方法 是使用FOR XML EXPLICIT。另一个解决方法 是放置类似'|' 的内容+ name + '#'(使用两个在您的实际数据中永远不会出现的字符。

然后你可以将结果转换为NVARCHAR(MAX)，在字符串基础上替换这些字符。

这会将您的 XML 作为字符串返回

SELECT 
REPLACE(REPLACE(CAST(
(
SELECT 
(
    SELECT  'id' AS [field/@name]
           ,id AS [field/values/value]
           ,''
           ,'name' AS [field/@name]
           ,'|' + name + '#' AS [field/values/value]
    FOR XML PATH('fields'),TYPE
)
FROM @tbl AS tbl
FOR XML PATH('record'),ROOT('records')
) AS NVARCHAR(MAX)),'|','<![CDATA['),'#',']]>')

在您将其转换回 XML 的那一刻，CDATA 消失了 :-(

关于sql-server - SQL Server FOR XML - 基本查询，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/38225789/