Extracting attributes of elements XML with different numbers of children

I have this XML file:

<?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>

And I’m trying to select all the heights of elements, but I’m getting the same height when the node has 2 or 3 child

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

In the example above I try to get the heights of the location 11703 but it repeats the first value for the high and low type

Current result:

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

What should returned:

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

>Solution :

You should really spend some time familiarizing yourself with XPath expressions. In your FROM clause you’re extracting the height elements and then in a number of places you’re using the parent axis (..) to move up to the enclosing loc elements – but you don’t want to do that to retrieve the value of the current height element – just stay right there:

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

Produces:

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

(Note that I also had to correct the WHERE clause because you gave us an untested query)