MS SQL查询XML节点值时，如何排除嵌套节点并拆分字段

问题场景 #

导入的XML结构如下：

<xml>
  <data>
    <email>test@mail.com</email>
    <isActive>
      true
      <previous>false</previous>
    </isActive>
  </data>
</xml>

查询isActive时，返回结果是truefalse（包含嵌套的previous节点内容），无法单独获取true，也不知道如何拆分出两个独立字段。

错误原因 #

直接获取isActive节点的value时，SQL会把该节点下所有后代文本节点的内容拼接在一起，包括子节点previous里的文本，因此得到混合结果。

解决方案 #

方法1：修正OPENXML语句 #

要单独获取isActive节点自身的文本，需指定取该节点的直接文本节点（text()），同时可单独提取previous节点的内容：

select
  email,
  isActive,
  isActivePrevious
from
  openxml(@xmlDocument, '/xml/data')  -- 路径需包含根节点<xml>，否则可能匹配不到
  with
  (
    email nvarchar(128) 'email',
    isActive nvarchar(12) 'isActive/text()[1]',  -- 取isActive下的第一个直接文本节点
    isActivePrevious nvarchar(12) 'isActive/previous'  -- 单独提取previous节点内容
  )

方法2：修正XQuery语句 #

同样通过text()定位isActive的直接文本节点，同时提取previous字段，另外原XML中true前后有换行和空格，需用LTRIM(RTRIM())去除空白，避免转换布尔值时出错：

select
  data.col.value('(email)[1]', 'nvarchar(128)') as email,
  LTRIM(RTRIM(data.col.value('(isActive/text())[1]', 'nvarchar(12)'))) as isActive,
  data.col.value('(isActive/previous)[1]', 'nvarchar(12)') as isActivePrevious
from
  @xmlDocument.nodes('/xml/data') as data(col)

布尔值转换补充 #

如果需要将isActive转换为布尔类型，处理完空白后直接转换即可：

CAST(LTRIM(RTRIM(data.col.value('(isActive/text())[1]', 'nvarchar(12)'))) AS BIT) as isActive

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