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)

Esto da como resultado lo siguiente:

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

Pero sí quiero que se vea así:

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

Quiero saber en qué elemento de "Fruta" se encontraron las "Frutas".

0
N-OW 10 dic. 2019 a las 18:45

2 respuestas

La mejor respuesta

Aquí hay otro método mediante el uso de un operador de comparación de orden de nodo . Aquí está el enlace: Operadores de comparación de órdenes de nodo

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);

Salida

+----+-----+-----------+
| ID | pos |   name    |
+----+-----+-----------+
|  1 |   1 | Apple     |
|  1 |   1 | Pineapple |
|  1 |   2 | Grapes    |
|  1 |   2 | Melon     |
+----+-----+-----------+
0
Yitzhak Khabinsky 10 dic. 2019 a las 17:54

Puede usar el siguiente SQL, tenga en cuenta que el uso de "nodos XML OVER" es una característica no documentada y no compatible:

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)
1
Piotr 10 dic. 2019 a las 16:11