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".
2 respuestas
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 |
+----+-----+-----------+
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)
Nuevas preguntas
sql-server
Microsoft SQL Server es un sistema de gestión de bases de datos relacionales (RDBMS). Use esta etiqueta para todas las ediciones de SQL Server, incluidas Compact, Express, Azure, Fast-track, APS (anteriormente PDW) y Azure SQL DW. No use esta etiqueta para otros tipos de DBMS (MySQL, PostgreSQL, Oracle, etc.). No utilice esta etiqueta para problemas de software y desarrollo móvil, a menos que esté directamente relacionada con la base de datos.
En Español