SQL Server 2008 如何从XML中动态提取tagName与tagValue

问题原因说明 #

你当前代码使用local-name(.)获取的是XML节点本身的名称，而你所有存储属性的节点统一命名为property，所以返回的TagName固定为property，并没有拿到你实际需要的key属性值作为标签名，同时现有代码也没有处理MoreDIfficult节点下嵌套的子属性。

方案1：提取根层级属性（保留嵌套结构整体值） #

如果只需要获取<properties>下直接子节点的标签和对应值，嵌套的structure内容作为整体值返回，使用以下代码：

DECLARE @test AS XML='
<properties>
  <property key="FirstProp">Blah</property>
  <property key="SecondProp">Blah2</property>
  <property key="ThirdProp">Blah3</property>
  <property key="Other">80000011b</property>
  <property key="Another">TheEnd</property>
  <property key="MoreDIfficult">
    <structure type="">
      <property key="Id">10100</property>
      <property key="Name">TestName</property>
    </structure>
  </property>
</properties>
';

SELECT
    PROPERTY.N.value('@key', 'varchar(max)') AS TagName
    ,PROPERTY.N.value('.', 'nvarchar(200)') AS TagValue
FROM
(SELECT @test AS Prop) XML
OUTER APPLY XML.Prop.nodes('/properties/property') AS PROPERTY(N);

运行后会返回6行根层级属性，包含MoreDIfficult对应的完整嵌套XML内容。

方案2：递归提取所有层级属性（展开嵌套内容） #

如果需要把嵌套在structure里的子属性也拆分为独立行，动态提取所有层级的标签和对应值（后续新增任意层级的property节点都不需要修改查询语句），使用以下代码：

DECLARE @test AS XML='
<properties>
  <property key="FirstProp">Blah</property>
  <property key="SecondProp">Blah2</property>
  <property key="ThirdProp">Blah3</property>
  <property key="Other">80000011b</property>
  <property key="Another">TheEnd</property>
  <property key="MoreDIfficult">
    <structure type="">
      <property key="Id">10100</property>
      <property key="Name">TestName</property>
    </structure>
  </property>
</properties>
';

SELECT
    PROPERTY.N.value('@key', 'varchar(max)') AS TagName
    ,PROPERTY.N.value('.', 'nvarchar(200)') AS TagValue
    -- 可选字段：区分属性所属层级，0为根层级，1为二级嵌套，以此类推
    --,PROPERTY.N.value('count(ancestor::property)', 'int') AS 层级
FROM
(SELECT @test AS Prop) XML
-- //property 匹配文档中所有property节点，不受层级限制
OUTER APPLY XML.Prop.nodes('//property') AS PROPERTY(N)
-- 可选过滤：只返回纯文本值的属性，过滤掉包含嵌套结构的行
-- WHERE PROPERTY.N.exist('./*') = 0
;

运行后会返回8行属性，包含嵌套的Id、Name两个二级属性。如果开启注释的过滤条件，会自动过滤掉MoreDIfficult这行包含嵌套结构的结果。

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