提取子节点数量不同的XML元素属性时遇重复值问题

原始XML数据 #

<?xml version="1.0" encoding="UTF8"?>
<locations>
    <loc loc_id="11703">
        <height type="low">22.19</height>
        <height type="high">24.19</height>
    </loc>
    <loc loc_id="11702">
        <height type="low">22.95</height>
    </loc>
    <loc loc_id="11702">
        <height type="mid">24.4</height>
    </loc>
    <loc loc_id="11702">
        <height type="high">24.95</height>
    </loc>
    <loc loc_id="11701">
        <height type="low">17.65</height>
        <height type="mid">23.99</height>
    </loc>
    <loc loc_id="11700">
        <height type="low">21.49</height>
        <height type="high">23.45</height>
        <height type="mid">23.3</height>
    </loc>
    <loc loc_id="11699">
        <height type="low">21.65</height>
        <height type="high">24.14</height>
    </loc>
</locations>

问题描述 #

提取所有height值时，当<loc>节点包含多个<height>子节点，会得到重复的错误height值。以查询loc_id=11703为例，结果中high类型的height错误显示为low类型的22.19，而非正确的24.19。

原始SQL代码 #

DECLARE @xmlDataHeights XML

SET @xmlDataHeights = (
  SELECT * FROM OPENROWSET (
    BULK 'C:\heights (6).xml', SINGLE_CLOB
  ) AS xmlData
)
SELECT
    ref.value('../@loc_id[1]', 'int') AS LocID ,
    ref.value('@type[1]', 'NVARCHAR (50)') AS type,
    ref.value('../height[1]', 'NVARCHAR (50)') AS height
FROM 
    @xmlDataHeights.nodes('/locations/loc/height') xmlData(ref)
WHERE
    ref.value('../@place_id[1]', 'int') = 11703

当前错误结果 #

LocID   type    height
----------------------
11703   low     22.19
11703   high    22.19

期望正确结果 #

LocID   type    height
----------------------
11703   low     22.19
11703   high    24.19

修正后的SQL代码 #

DECLARE @xmlDataHeights XML

SET @xmlDataHeights = (
  SELECT * FROM OPENROWSET (
    BULK 'C:\heights (6).xml', SINGLE_CLOB
  ) AS xmlData
)
SELECT
    ref.value('../@loc_id[1]', 'int') AS LocID ,
    ref.value('@type[1]', 'NVARCHAR (50)') AS type,
    ref.value('.', 'NVARCHAR (50)') AS height  -- 修改为获取当前节点的文本值
FROM 
    @xmlDataHeights.nodes('/locations/loc/height') xmlData(ref)
WHERE
    ref.value('../@loc_id[1]', 'int') = 11703  -- 修正属性名从place_id改为loc_id

错误原因与修改说明 #

1. height取值错误：原始代码中ref.value('../height[1]', ...)是取当前<height>节点的父节点<loc>下的第一个<height>元素值，因此所有同loc_id的height都会返回第一个值。改为.即可获取当前<height>节点自身的文本内容，对应正确的height值。
2. WHERE条件属性错误：XML中<loc>节点的属性是loc_id，而非place_id，修正后才能正确过滤目标loc_id的记录。

内容的提问来源于stack exchange，提问作者japperez.524