CODEWITHSUNDEEP
sql-serverxml
N-OW
N-OW

Reputation: 37

Position of an XML element in SQL Server 2012

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.

Upvotes: 0

Views: 606

Answers (2)

Yitzhak Khabinsky
Yitzhak Khabinsky

Reputation: 22321

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

Upvotes: 0

Piotr Palka
Piotr Palka

Reputation: 3169

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)

Upvotes: 1

Related Questions