能否提取XML列中所有行的Distinct键值集合？

当然可行！你可以借助SQL Server的XML查询能力，自动提取所有<entry>元素里的key属性值并去重，完全不需要预先硬编码已知键值。下面是具体的实现方案：

表结构与XML数据示例 #

你的users表结构如下：

[id] int, [userdetails] nvarchar(max)

userdetails列存储的XML格式示例：

<Attributes>
  <Map>
    <entry key="displayName" value="Administrator"/>
    <entry key="email" value="joe.blow@google.com"/>
    <entry key="firstname" value="Joe"/>
    <entry key="lastname" value="tdehdgthrth"/>
    <entry key="another1" value="rthrth"/>
    <entry key="another7" value="etdsryhntdhetdyh"/>
    <entry key="anotherWhatever" value="6546544"/>
  </Map>
</Attributes>

核心查询代码 #

下面的SQL会遍历所有行的XML数据，提取所有key属性并返回去重后的集合：

WITH XmlData AS (
    -- 将字符串类型的XML转换为可查询的XML类型
    SELECT CAST(userdetails AS XML) AS UserXml
    FROM users
)
SELECT DISTINCT 
    -- 提取每个entry元素的key属性值
    EntryKeys.value('@key', 'nvarchar(100)') AS DistinctKey
FROM XmlData
-- 拆分XML，把每个entry元素转为单独的行
CROSS APPLY UserXml.nodes('Attributes/Map/entry') AS AllEntries(EntryKeys);

代码逻辑说明 #

• CTE转换XML：先把nvarchar(max)类型的userdetails转换成XML类型，这样才能使用SQL Server的XML节点查询方法。
• nodes()拆分节点：nodes('Attributes/Map/entry')会把每一行XML里的所有<entry>元素拆分成独立的行，让我们能逐个访问每个key属性。
• value()提取属性：从每个<entry>元素中提取key属性的值，这里指定返回nvarchar(100)，你可以根据实际键的长度调整类型。
• DISTINCT去重：确保最终结果是所有不重复的键值集合。

测试验证示例 #

如果你想用之前的测试表验证效果，可以运行这段代码：

DECLARE @tbl TABLE(id int, userdetails nvarchar(max))
INSERT @tbl(id,userdetails) 
VALUES(1,'<Attributes> <Map> <entry key="displayName" value="Administrator"/> <entry key="email" value="joe.blow@google.com"/> <entry key="firstname" value="Joe"/> <entry key="lastname" value="Blow"/> </Map> </Attributes>'),
      (2,'<Attributes> <Map> <entry key="displayName" value="Guest"/> <entry key="phone" value="123456789"/> <entry key="another1" value="test"/> </Map> </Attributes>');

WITH XmlData AS (
    SELECT CAST(userdetails AS XML) AS UserXml
    FROM @tbl
)
SELECT DISTINCT 
    EntryKeys.value('@key', 'nvarchar(100)') AS DistinctKey
FROM XmlData
CROSS APPLY UserXml.nodes('Attributes/Map/entry') AS AllEntries(EntryKeys);

执行后会返回所有不重复的键：displayName、email、firstname、lastname、phone、another1。

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