SQL Server FOR XML - 基本查询-解网

问：

我得到了一个我想通过 SQL 脚本生成的 XML 文档，我没有做过这样的事情，也无法找到任何可以引导我能够生成我需要的最终 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>

我有点迷失在能够建立请求或获得正确的东西方面（我认为我试图在一次查找中做太多事情，这就是我问题的原因）。任何帮助都是值得赞赏的 - 即使它为我指出了一个很好的指南（我发现的唯一一个在示例方面非常糟糕 - 它们没有显示如何构建/更改它们的微妙之处）

sql-server for-xml-explicit

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>

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

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

添加部分的唯一干净方法是使用 .另一种解决方法是放置类似（使用两个字符，永远不会出现在您的实际数据中。CDATAFOR 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['),'#',']]>')

在你把它扔回CDATA的那一刻，CDATA已经消失了：-（XML

    declare @t table (id varchar(10))

    insert into @t values ('1')
    insert into @t values ('2')

    select (
            select 
                    t.id 'fields/field/@id'
                    , t.id 'fields/field/name'
                from @t t
                for xml path(''), type
        ) 'records/record'
        for xml path('')

SELECT TOP 2
(
    SELECT  
        (SELECT 'id' AS [field/@id],
        product_id [field/values/value]
        FOR XML PATH(''), TYPE),
        (SELECT 'title' AS [field/@id],
        title [field/values/value]
        FOR XML PATH(''), TYPE)
    FOR XML PATH('fields'), TYPE
)
FROM products 
FOR XML PATH('record'), ROOT('records')

因为这使我可以更轻松地操作输出。

感谢@xdd，尤其是@Shnugo的回答！最终的解决方案是基于 @Shnugo 的建议，只是避免了将额外的空白行放入的技巧。

嗨，是的，这也有效。有一点是我更喜欢“技巧”而不是这个子选择的一点：当涉及命名空间时，每个子选择都会重复添加命名空间声明。这可能会极大地破坏生成的 XML。而且 - 但这当然是个人意见 - 我认为空元素技巧最好阅读并更接近目标。无论如何，祝您编码愉快！

上一个：在 SQL Server 中以 XML 数据类型存储存储过程的文本

下一个：SQL Server 2000 和 SQL Server 2014 在使用 for xml 显式返回结果时返回的不同值

SQL Server FOR XML - 基本查询

SQL Server FOR XML - Basic query

评论

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

评论

评论

评论

SQL Server FOR XML - 基本查询

SQL Server FOR XML - Basic query

评论

更新：据我所知，没有干净的方法来添加-sectionsCDATA

评论

评论

评论

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