MS SQL中如何直接查询varchar列内的XML数据（无需变量）

我们有存储在User表Settings（VARCHAR类型）列中的XML数据，结构如下：

<UserSettings>
   <ActiveStaff>
    <int>2063</int>
    <int>2062</int>
    <int>5</int>
    <int>10</int>
    <int>2064</int>
   </ActiveStaff>
</UserSettings>

用XML变量的写法能正常提取节点值，但直接在子查询里转换后调用nodes会报错——这是因为子查询返回的XML列没有别名，SQL Server无法识别要调用nodes的对象。下面是两种无需变量的可行方案：

方案1：用CROSS APPLY关联转换后的XML #

通过两次CROSS APPLY，先把VARCHAR转成XML并命名，再提取节点：

SELECT T.c.value('.', 'int') AS StaffID
FROM [User]
CROSS APPLY (SELECT CAST(CAST(Settings AS NTEXT) AS XML) AS XmlData) AS XmlSource
CROSS APPLY XmlSource.XmlData.nodes('//UserSettings/ActiveStaff/int') T(c)
WHERE User_id = 3

方案2：给子查询的XML列加别名 #

在子查询里给转换后的XML列指定别名，再基于这个别名调用nodes：

SELECT T.c.value('.', 'int') AS StaffID
FROM (
    SELECT CAST(CAST(Settings AS NTEXT) AS XML) AS UserSettingsXml
    FROM [User]
    WHERE User_id = 3
) AS XmlData
CROSS APPLY XmlData.UserSettingsXml.nodes('//UserSettings/ActiveStaff/int') T(c)

在WHERE条件中使用XML查询的示例 #

如果要筛选提取出的节点值，比如只保留StaffID为5的记录，可以这样写（避免重复调用value提升效率）：

SELECT u.User_id, StaffList.StaffID
FROM [User] u
CROSS APPLY (SELECT CAST(CAST(u.Settings AS NTEXT) AS XML) AS XmlData) AS XmlSource
CROSS APPLY (
    SELECT c.value('.', 'int') AS StaffID
    FROM XmlSource.XmlData.nodes('//UserSettings/ActiveStaff/int') T(c)
) AS StaffList
WHERE u.User_id = 3
AND StaffList.StaffID = 5

注意：User是SQL Server的保留字，建议用方括号[User]包裹表名避免语法冲突。

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