How to get the position/index of an ancestor element within the ancestor elements parent

Question

I've got xml that looks like this:

<items>
    <item>
      <itemproperty>true</itemproperty>
      <details>
        <details>
          <ID Type="z" Value="1"/>
          <ID Type="w" Value="2"/>
        </details>
      </Locations>
    </item>
    <item>
      <itemproperty>true</itemproperty>
      <details>
        <details>
          <ID Type="a" Value="3"/>
          <ID Type="b" Value="4"/>
        </details>
      </Locations>
    </item>
</items>

I want to produce output like this:

ItemIndex    DetailIDType     DetailIDValue
1            z                1
1            w                2
2            a                3
2            b                4

I'm querying from the /items/item/details/detail/ID node and experimenting with trying to figure out the position/index of the ancestor item element. I can get the total count of item elements, but am struggling to find the position of the item element. In all cases it is not returning the correct index/position value:

SELECT DISTINCT
    [TestGetItemElementName] = IDs.id.value('fn:local-name(./../../..)', 'varchar(50)')
    ,[TestGetItemElementCount] = IDs.id.value('count(/items/item)', 'int')
    ,[ItemIndexTest1] = IDs.id.value('for $i in ./../../.. return count(/items/item/*[. << $i]) + 1', 'int')
    ,[ItemIndexTest2] = IDs.id.value('for $i in . return count(../*[. << $i]) + 1', 'int')

    ,[DetailIDType] = convert(VARCHAR(256), IDs.id.query('data(./@Type)')) 
    ,[DetailIDValue] = convert(VARCHAR(256), IDs.id.query('data(./@Value)'))
FROM 
    @x.nodes('/Recipients/Recipient/Locations/Location/ID') AS IDs(id)

score 0 · Answer 1 · answered Feb 27 '21 at 06:04

Try to avoid using the parent axis (.. or parent::node()) in XPath queries as it can be a serious performance killer.

You can achieve what you're looking for by using DENSE_RANK() to provide automatic numbering of the item nodes with code such as the following...

declare @x xml = N'<items>
    <item>
      <itemproperty>true</itemproperty>
      <Locations>
        <details>
          <ID Type="z" Value="1"/>
          <ID Type="w" Value="2"/>
        </details>
      </Locations>
    </item>
    <item>
      <itemproperty>true</itemproperty>
      <Locations>
        <details>
          <ID Type="a" Value="3"/>
          <ID Type="b" Value="4"/>
        </details>
      </Locations>
    </item>
</items>';

select
  dense_rank() over (partition by items order by item) as ItemIndex
  ,details.value('@Type', N'nvarchar(256)') as DetailIDType
  ,details.value('@Value', N'nvarchar(256)') as DetailIDValue
from @x.nodes('//items') nodes1(items)
cross apply items.nodes('item') nodes2(item)
cross apply item.nodes('Locations/details/ID') nodes3(details);

Which yields the results:

ItemIndex	DetailIDType	DetailIDValue
1	z	1
1	w	2
2	a	3
2	b	4

Yitzhak Khabinsky · Answer 2 · 2021-02-28T03:02:45.430

I see you tried to use a Node Comparison operator to get the ItemIndex column values. Here is how to do it correctly.

SQL

DECLARE @x XML = 
N'<items>
    <item>
      <itemproperty>true</itemproperty>
      <Locations>
        <details>
          <ID Type="z" Value="1"/>
          <ID Type="w" Value="2"/>
        </details>
      </Locations>
    </item>
    <item>
      <itemproperty>true</itemproperty>
      <Locations>
        <details>
          <ID Type="a" Value="3"/>
          <ID Type="b" Value="4"/>
        </details>
      </Locations>
    </item>
</items>';

SELECT c.value('for $i in . return count(/items/item[. << $i])', 'INT') AS ItemIndex
    , c.value('@Type', 'VARCHAR(20)') as DetailIDType
    , c.value('@Value', 'INT') as DetailIDValue
FROM @x.nodes('/items/item/Locations/details/ID') AS t(c);

Output

+-----------+--------------+---------------+
| ItemIndex | DetailIDType | DetailIDValue |
+-----------+--------------+---------------+
|         1 | z            |             1 |
|         1 | w            |             2 |
|         2 | a            |             3 |
|         2 | b            |             4 |
+-----------+--------------+---------------+

How to get the position/index of an ancestor element within the ancestor elements parent

2 Answers2