如何在SQL Server 2016中用XQuery查询指定格式的XML？

嘿，我来帮你搞定SQL Server 2016里用XQuery查询这段XML的问题！首先你的XML带有默认命名空间qm_system_resultset，这是XQuery查询的关键——必须先绑定命名空间，才能正确定位到XML节点。下面给你一步步的实用示例：

SQL Server里用WITH XMLNAMESPACES语句来处理默认命名空间，你可以直接指定DEFAULT，不用额外前缀，写法更简洁：

WITH XMLNAMESPACES(DEFAULT 'qm_system_resultset')
-- 后续查询写在这里

假设你的XML数据存在表YourTable的XmlData字段里，要提取第一个<result>节点的各个字段，写法如下：

WITH XMLNAMESPACES(DEFAULT 'qm_system_resultset')
SELECT
    XmlData.value('(/resultset/result/result_id)[1]', 'varchar(10)') AS ResultID,
    XmlData.value('(/resultset/result/exception)[1]', 'varchar(3)') AS ExceptionFlag,
    XmlData.value('(/resultset/result/recurring_count)[1]', 'int') AS RecurringCount,
    XmlData.value('(/resultset/result/exception_expiration)[1]', 'date') AS ExceptionExpiration
FROM YourTable;

• value()方法用来提取单个节点的值，[1]表示取第一个匹配的节点（如果XML只有一个<result>，这个索引也可以省略，但加上更严谨）；
• 第二个参数是返回值的SQL数据类型，要和XML节点内容匹配，比如recurring_count是数字就用int，日期用date。

如果你的XML里有多个<result>节点，想要把每个节点转成一行关系型数据，就用nodes()方法配合CROSS APPLY：

WITH XMLNAMESPACES(DEFAULT 'qm_system_resultset')
SELECT
    ResultNode.value('(result_id)[1]', 'varchar(10)') AS ResultID,
    ResultNode.value('(exception)[1]', 'varchar(3)') AS ExceptionFlag,
    ResultNode.value('(defect)[1]', 'varchar(3)') AS DefectFlag,
    ResultNode.value('(unresolved)[1]', 'varchar(3)') AS UnresolvedFlag
FROM YourTable
CROSS APPLY XmlData.nodes('/resultset/result') AS Nodes(ResultNode);

nodes()会把每个<result>节点拆成独立的行，然后我们从每个节点里提取字段即可。

比如要找result_id为F5或者exception为YES的记录，推荐用XQuery内过滤的方式，性能更优：

WITH XMLNAMESPACES(DEFAULT 'qm_system_resultset')
SELECT
    ResultNode.value('(result_id)[1]', 'varchar(10)') AS ResultID,
    ResultNode.value('(exception)[1]', 'varchar(3)') AS ExceptionFlag
FROM YourTable
CROSS APPLY XmlData.nodes('/resultset/result[result_id="F5"]') AS Nodes(ResultNode);

这种方式在拆分节点前就完成了过滤，数据量大的时候比在WHERE子句过滤效率更高。

你的XML里有<exception_approval />和<comments />这种空节点，提取时可以用ISNULL给空值设置默认值，让结果更友好：

WITH XMLNAMESPACES(DEFAULT 'qm_system_resultset')
SELECT
    ISNULL(ResultNode.value('(exception_approval)[1]', 'varchar(50)'), '未设置') AS ExceptionApproval,
    ISNULL(ResultNode.value('(comments)[1]', 'varchar(MAX)'), '') AS Comments
FROM YourTable
CROSS APPLY XmlData.nodes('/resultset/result') AS Nodes(ResultNode);

内容的提问来源于stack exchange，提问作者Aura