Seleccionar valores del campo XML en SQL Server 2008

Solo mirando mi campo XML, mis filas se ven así:

JonJohnson KathyCarter BobBurns 

Tenga en cuenta que estas son tres filas en mi tabla.

Me gustaría devolver un resultado de SQL como una tabla como en

 Jon | Johnson Kathy| Carter Bob | Burns 

¿Qué consulta logrará esto?

Dado que el campo XML se llama ‘xmlField’ …

 SELECT [xmlField].value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName, [xmlField].value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName FROM [myTable] 

Teniendo en cuenta que los datos XML provienen de una tabla ‘tabla’ y se almacenan en una columna ‘campo’: use los métodos XML , extraiga valores con xml.value() , nodos de proyecto con xml.nodes() , use CROSS APPLY para unirse:

 SELECT p.value('(./firstName)[1]', 'VARCHAR(8000)') AS firstName, p.value('(./lastName)[1]', 'VARCHAR(8000)') AS lastName FROM table CROSS APPLY field.nodes('/person') t(p) 

Puede eliminar los nodes() y cross apply si cada campo contiene exactamente un elemento ‘persona’. Si el XML es una variable, seleccione FROM @variable.nodes(...) y no necesita la cross apply .

Esta publicación fue útil para resolver mi problema, que tiene un formato XML un poco diferente … mi XML contiene una lista de claves como el siguiente ejemplo y almaceno el XML en la columna SourceKeys en una tabla llamada DeleteBatch:

 1 2 3 

Crea la tabla y rellena con algunos datos:

 CREATE TABLE dbo.DeleteBatch ( ExecutionKey INT PRIMARY KEY, SourceKeys XML) INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys ) SELECT 1, (CAST('123' AS XML)) INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys ) SELECT 2, (CAST('100101' AS XML)) 

Aquí está mi SQL para seleccionar las claves del XML:

 SELECT ExecutionKey, p.value('.', 'int') AS [Key] FROM dbo.DeleteBatch CROSS APPLY SourceKeys.nodes('/k') t(p) 

Aquí están los resultados de la consulta …

 Clave ExecutionKey
 1 1
 1 2
 1 3
 2 100
 2 101

Esto puede responder su pregunta:

 select cast(xmlField as xml) xmlField into tmp from ( select 'JonJohnson' xmlField union select 'KathyCarter' union select 'BobBurns' ) tb SELECT xmlField.value('(person/firstName)[1]', 'nvarchar(max)') as FirstName ,xmlField.value('(person/lastName)[1]', 'nvarchar(max)') as LastName FROM tmp drop table tmp 

Caray. Este fue un hilo muy útil para descubrir.

Todavía encontré algunas de estas sugerencias confusas. Cada vez que usaba value con [1] en la cadena, solo recuperaba el primer valor. Y algunas sugerencias recomendaron el uso de cross apply que (en mis pruebas) acaba de traer demasiados datos.

Entonces, aquí está mi ejemplo simple de cómo xml un objeto xml , luego lees sus valores en una tabla.

 DECLARE @str nvarchar(2000) SET @str = '' SET @str = @str + '' SET @str = @str + ' ' SET @str = @str + ' Mike' SET @str = @str + ' Gledhill' SET @str = @str + ' 31' SET @str = @str + ' ' SET @str = @str + ' ' SET @str = @str + ' Mark' SET @str = @str + ' Stevens' SET @str = @str + ' 42' SET @str = @str + ' ' SET @str = @str + ' ' SET @str = @str + ' Sarah' SET @str = @str + ' Brown' SET @str = @str + ' 23' SET @str = @str + ' ' SET @str = @str + '' DECLARE @xml xml SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML) -- Iterate through each of the "users\user" records in our XML SELECT x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName', x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName', x.Rec.query('./age').value('.', 'int') AS 'Age' FROM @xml.nodes('/users/user') as x(Rec) 

Y aquí está la salida:

enter image description here

Es una syntax extraña, pero con un ejemplo decente, es bastante fácil de agregar a sus propias funciones de SQL Server.

Hablando de eso, esta es la respuesta correcta a esta pregunta.

Suponiendo que tiene sus datos xml en una variable @xml de tipo xml (como se demostró en mi ejemplo anterior), así es como devolvería las tres filas de datos del xml citado en la pregunta:

 SELECT x.Rec.query('./firstName').value('.', 'nvarchar(2000)') AS 'FirstName', x.Rec.query('./lastName').value('.', 'nvarchar(2000)') AS 'LastName' FROM @xml.nodes('/person') as x(Rec) 

enter image description here

Si puede envolver su XML en un elemento raíz, digamos que la siguiente es su solución:

 DECLARE @PersonsXml XML = 'JonJohnson KathyCarter BobBurns' SELECT b.value('(./firstName/text())[1]','nvarchar(max)') as FirstName, b.value('(./lastName/text())[1]','nvarchar(max)') as LastName FROM @PersonsXml.nodes('/persons/person') AS a(b) 

enter image description here

 SELECT cast(xmlField as xml).value('(/person//firstName/node())[1]', 'nvarchar(max)') as FirstName, cast(xmlField as xml).value('(/person//lastName/node())[1]', 'nvarchar(max)') as LastName FROM [myTable] 

/ * Este ejemplo usa una variable XML con un esquema * /

 IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'OrderingAfternoonTea') BEGIN DROP XML SCHEMA COLLECTION dbo.OrderingAfternoonTea END GO CREATE XML SCHEMA COLLECTION dbo.OrderingAfternoonTea AS N'< ?xml version="1.0" encoding="UTF-16" ?>            ' ; GO DECLARE @potsOfTea int; DECLARE @cakes int; DECLARE @fruitedSconesWithCream int; DECLARE @jams nvarchar(128); DECLARE @RequestMsg NVARCHAR(2048); DECLARE @RequestXml XML(dbo.OrderingAfternoonTea); set @potsOfTea = 5; set @cakes = 7; set @fruitedSconesWithCream = 25; set @jams = N'medlar jelly, quince and mulberry'; SELECT @RequestMsg = N'< ?xml version="1.0" encoding="utf-16" ?>  ' + CAST(@potsOfTea as NVARCHAR(20)) + ' ' + CAST(@cakes as NVARCHAR(20)) + ' ' + CAST(@fruitedSconesWithCream as NVARCHAR(20)) + ' ' + @jams + ' '; SELECT @RequestXml = CAST(CAST(@RequestMsg AS VARBINARY(MAX)) AS XML) ; with xmlnamespaces('http://Tfor2.com/schemas/actions/orderAfternoonTea' as tea) select cast( x.Rec.value('.[1]/@schemaVersion','nvarchar(20)') as bigint ) as schemaVersion, cast( x.Rec.query('./tea:potsOfTea') .value('.','nvarchar(20)') as bigint ) as potsOfTea, cast( x.Rec.query('./tea:cakes') .value('.','nvarchar(20)') as bigint ) as cakes, cast( x.Rec.query('./tea:fruitedSconesWithCream') .value('.','nvarchar(20)') as bigint ) as fruitedSconesWithCream, x.Rec.query('./tea:jams').value('.','nvarchar(50)') as jams from @RequestXml.nodes('/tea:afternoonTeaOrder') as x(Rec); select @RequestXml.query('/*')