Solución:
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 un ‘campo’ de columna: use los métodos XML, extraiga valores con xml.value(), proyecto de nodos con xml.nodes(), usar CROSS APPLY unir:
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)
Puedes deshacerte del nodes() y cross apply si cada campo contiene exactamente un elemento ‘persona’. Si el XML es una variable, selecciona FROM @variable.nodes(...) y no necesitas el 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:
<k>1</k>
<k>2</k>
<k>3</k>
Cree la tabla y rellénela con algunos datos:
CREATE TABLE dbo.DeleteBatch (
ExecutionKey INT PRIMARY KEY,
SourceKeys XML)
INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 1,
(CAST('<k>1</k><k>2</k><k>3</k>' AS XML))
INSERT INTO dbo.DeleteBatch ( ExecutionKey, SourceKeys )
SELECT 2,
(CAST('<k>100</k><k>101</k>' 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 …
ExecutionKey Key 1 1 1 2 1 3 2 100 2 101