如何查询SQL Server XML列value节点下嵌套的Boolean和List标签

你之前的XPath表达式不适用于IIQDisabled和accountFlags的原因是：userPrincipalName的取值直接存放在entry节点的value属性中，而IIQDisabled、accountFlags的取值存放在entry节点的子节点结构内，不能通过/@value读取。

SELECT 
TOP 100
  link.id
, linkMap.value('(./entry[@key="userPrincipalName"]/@value)[1]', 'varchar(500)') as userPrincipalName
-- 提取IIQDisabled
, linkMap.value('(./entry[@key="IIQDisabled"]/value/Boolean/text())[1]', 'varchar(10)') as IIQDisabled
, linkMap.value('(./entry[@key="IIQDisabled"]/value/Boolean/text())[1]', 'bit') as IIQDisabled_as_bit
-- 拼接accountFlags所有条目，分隔符可自行调整
, STUFF((
    SELECT ';' + flag.value('text()[1]', 'varchar(200)')
    FROM linkMap.nodes('./entry[@key="accountFlags"]/value/List/String') AS T(flag)
    FOR XML PATH(''), TYPE
).value('.', 'varchar(MAX)'), 1, 1, '') AS accountFlags
FROM 
[identityiq].[spt_link] link    
CROSS APPLY (SELECT CAST(CAST(link.attributes AS varchar(MAX)) AS XML)) linkAttributes(AttributesAsXml)
CROSS APPLY linkAttributes.AttributesAsXml.nodes('/Attributes/Map') linkAttributesMap(linkMap)

• IIQDisabled的XPath定位到entry子节点下的value/Boolean节点的文本内容，空的<Boolean/>节点会返回NULL值。
• 上述accountFlags的拼接写法兼容SQL Server 2016及更低版本，如果你使用SQL Server 2017及更高版本，可以用更简洁的STRING_AGG写法实现拼接：

  , (SELECT STRING_AGG(flag.value('text()[1]', 'varchar(200)'), ';')
      FROM linkMap.nodes('./entry[@key="accountFlags"]/value/List/String') AS T(flag)) AS accountFlags
  

• 代码中的分隔符;可以替换为你需要的其他分隔符。

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