Position of an XML element in SQL Server 2012

Question

Create Table #tmp(ID int IDentity(1,1), XMLData XML)

Insert Into #tmp(XMLData)
Values('<SampleXML>
<Fruit>
<Fruits>Apple</Fruits>
<Fruits>Pineapple</Fruits>
</Fruit>
<Fruit>
<Fruits>Grapes</Fruits>
<Fruits>Melon</Fruits>
</Fruit>
</SampleXML>')

SELECT
ID,
A.x.query('data(.)') as name,
Row_Number() over(order by A.x) as number
FROM #tmp
CROSS APPLY XMLData.nodes('SampleXML/Fruit/Fruits') AS A(x)

This results into the following:

ID  name    number
1   Apple   1
1   Pineapple   2
1   Grapes  3
1   Melon   4

but I do want it to look like this:

ID  name    number
1   Apple   1
1   Pineapple   1
1   Grapes  2
1   Melon   2

I want to know in which "Fruit" element the "Fruits" were found.

Does this answer your question? [Get the position of xml element in SQL Server 2012](https://stackoverflow.com/questions/23836226/get-the-position-of-xml-element-in-sql-server-2012) — Piotr Palka, Dec 10 '19 at 16:06
@Piotr I think this does what i suggested in the first resultset. But i do want to have the second one. Am i right? — N-OW, Dec 10 '19 at 16:41

score 1 · Answer 1 · answered Dec 10 '19 at 16:11

1

You can use following SQL, please be aware that using "OVER XML nodes" is an undocumented and unsupported feature:

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Fruit>
<Fruits>Apple</Fruits>
<Fruits>Pineapple</Fruits>
</Fruit>
<Fruit>
<Fruits>Grapes</Fruits>
<Fruits>Melon</Fruits>
</Fruit>
</SampleXML>'

SELECT
Friuts.col.query('data(.)') as name,
DENSE_RANK() over(order by Friut.col) as number
FROM @MyXML.nodes('SampleXML/Fruit') AS Friut(col)
CROSS APPLY Friut.col.nodes('./Fruits') AS Friuts(col)

answered Dec 10 '19 at 16:11

Piotr Palka

3,086
1
9
17

This does look very good. I changed the question into something from a table with an xml column where i already use a cross apply so i do not rly know how to include the information into this scenario. Sorry for not starting with a table at the beginning. – N-OW Dec 10 '19 at 17:03

Yitzhak Khabinsky · Accepted Answer · 2019-12-10T17:54:54.217

Here is another method by using a Node Order Comparison operator. Here is the link: Node Order Comparison Operators

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE(ID INT IDENTITY PRIMARY KEY, XMLData XML);

INSERT INTO @tbl (XMLData)
VALUES (N'<SampleXML>
    <Fruit>
        <Fruits>Apple</Fruits>
        <Fruits>Pineapple</Fruits>
    </Fruit>
    <Fruit>
        <Fruits>Grapes</Fruits>
        <Fruits>Melon</Fruits>
    </Fruit>
</SampleXML>');
-- DDL and sample data population, end

-- Method #1
-- by Piotr, adjusted for a table and optimized
SELECT Friuts.col.value('(./text())[1]', 'VARCHAR(30)') as [name],
    DENSE_RANK() OVER(ORDER BY Friut.col) as number
FROM @tbl AS tbl
    CROSS APPLY tbl.XMLData.nodes('/SampleXML/Fruit') AS Friut(col)
    CROSS APPLY Friut.col.nodes('./Fruits') AS Friuts(col);

-- Method #2
-- by using a Node Order Comparison operator
SELECT ID
    , col.value('let $n := . return count(../../*[. << $n])', 'INT') AS pos
    , col.value('(./text())[1]', 'VARCHAR(30)') as [name]
FROM @tbl AS tbl
    CROSS APPLY tbl.XMLData.nodes('/SampleXML/Fruit/Fruits') AS tab(col);

Output

+----+-----+-----------+
| ID | pos |   name    |
+----+-----+-----------+
|  1 |   1 | Apple     |
|  1 |   1 | Pineapple |
|  1 |   2 | Grapes    |
|  1 |   2 | Melon     |
+----+-----+-----------+

That does look great. What would you consider for a good performance? — N-OW, Dec 10 '19 at 17:50
I would go for the officially documented way. I would suggest to test both methods with your real table and XML in it. — Yitzhak Khabinsky, Dec 10 '19 at 18:01

Position of an XML element in SQL Server 2012

2 Answers2

Linked