recuperar - ¿Cómo consultar los valores Xml y los atributos de la tabla en SQL Server?

recuperar datos xml sql (4)

Tengo una tabla que contiene una columna Xml :

SELECT * FROM Sqm

Una muestra de los datos xml de una fila sería:

En el caso de estos datos, me gustaría:

SqmId id type unit count sum minValue maxValue standardDeviation Value ===== =================================================== ===== ==== ===== ====== ======== ======== ================= ====== 1 TransactionCleanupThread.RecordUsedTransactionShift timer µs 1 21490 73701 73701 NULL 73701 1 TransactionCleanupThread.RefundOldTrans timer µs 1 184487 632704 632704 NULL 632704 1 Database.CreateConnection_SaveContextUserGUID timer µs 2 7562 12928 13006 16 12967 1 Global.CurrentUser timer µs 6 4022464 15 13794345 1642047 2299194 1 Global.CurrentUser_FetchIdentityFromDatabase timer µs 1 4010057 13752614 13752614 NULL 13752614 2 ...

Al final, estaré realizando SUM() , MIN() , MAX() agregación. Pero por ahora estoy tratando de consultar una columna xml.

En pseudo-código, probaría algo como:

SELECT SqmId, Data.query(''/Sqm/Metrics/Metric/@id'') AS id, Data.query(''/Sqm/Metrics/Metric/@type'') AS type, Data.query(''/Sqm/Metrics/Metric/@unit'') AS unit, Data.query(''/Sqm/Metrics/Metric/@sum'') AS sum, Data.query(''/Sqm/Metrics/Metric/@count'') AS count, Data.query(''/Sqm/Metrics/Metric/@minValue'') AS minValue, Data.query(''/Sqm/Metrics/Metric/@maxValue'') AS maxValue, Data.query(''/Sqm/Metrics/Metric/@standardDeviation'') AS standardDeviation, Data.query(''/Sqm/Metrics/Metric'') AS value FROM Sqm

Pero esa consulta SQL no funciona:

Msg 2396, nivel 16, estado 1, línea 2
XQuery [Sqm.data.query ()]: El atributo puede no aparecer fuera de un elemento

He cazado, y es sorprendente cuán mal documentada o ilustrada es la consulta de Xml. La mayoría de los recursos en lugar de consultar una tabla , consulta una variable ; que no estoy haciendo La mayoría de los recursos solo utilizan consultas XML para el filtrado y la selección, en lugar de leer valores. La mayoría de los recursos leen los nodos secundarios codificados (por índice), en lugar de los valores reales.

Recursos relacionados que leo

Actualización: .value en lugar de .query

Intenté usar aleatoriamente .value , en lugar de .query :

SELECT Sqm.SqmId, Data.value(''/Sqm/Metrics/Metric/@id'', ''varchar(max)'') AS id, Data.value(''/Sqm/Metrics/Metric/@type'', ''varchar(max)'') AS type, Data.value(''/Sqm/Metrics/Metric/@unit'', ''varchar(max)'') AS unit, Data.value(''/Sqm/Metrics/Metric/@sum'', ''varchar(max)'') AS sum, Data.value(''/Sqm/Metrics/Metric/@count'', ''varchar(max)'') AS count, Data.value(''/Sqm/Metrics/Metric/@minValue'', ''varchar(max)'') AS minValue, Data.value(''/Sqm/Metrics/Metric/@maxValue'', ''varchar(max)'') AS maxValue, Data.value(''/Sqm/Metrics/Metric/@standardDeviation'', ''varchar(max)'') AS standardDeviation, Data.value(''/Sqm/Metrics/Metric'', ''varchar(max)'') AS value FROM Sqm

Pero eso tampoco funciona:

Msg 2389, nivel 16, estado 1, línea 3 XQuery [Sqm.data.value ()]:
''valor ()'' requiere un singleton (o secuencia vacía), operand encontrado de tipo ''xdt: untypedAtomic *''

En realidad, estás cerca de tu objetivo, solo necesitas utilizar el método nodes() para dividir tus filas y luego obtener valores:

select s.SqmId, m.c.value(''@id'', ''varchar(max)'') as id, m.c.value(''@type'', ''varchar(max)'') as type, m.c.value(''@unit'', ''varchar(max)'') as unit, m.c.value(''@sum'', ''varchar(max)'') as [sum], m.c.value(''@count'', ''varchar(max)'') as [count], m.c.value(''@minValue'', ''varchar(max)'') as minValue, m.c.value(''@maxValue'', ''varchar(max)'') as maxValue, m.c.value(''.'', ''nvarchar(max)'') as Value, m.c.value(''(text())[1]'', ''nvarchar(max)'') as Value2 from sqm as s outer apply s.data.nodes(''Sqm/Metrics/Metric'') as m(c)

demostración fiddle sql

He estado tratando de hacer algo muy similar pero no usando los nodos. Sin embargo, mi estructura xml es un poco diferente.

Lo tienes así:

<Metrics> <Metric id="TransactionCleanupThread.RefundOldTrans" type="timer" ...>

Si fuera así en su lugar:

<Metrics> <Metric> <id>TransactionCleanupThread.RefundOldTrans</id> <type>timer</type> . . .

Entonces, simplemente podría usar esta declaración SQL.

SELECT Sqm.SqmId, Data.value(''/Sqm/Metrics/Metric/id)[1]'', ''varchar(max)'') as id, Data.value(''/Sqm/Metrics/Metric/type)[1]'', ''varchar(max)'') AS type, Data.value(''/Sqm/Metrics/Metric/unit)[1]'', ''varchar(max)'') AS unit, Data.value(''/Sqm/Metrics/Metric/sum)[1]'', ''varchar(max)'') AS sum, Data.value(''/Sqm/Metrics/Metric/count)[1]'', ''varchar(max)'') AS count, Data.value(''/Sqm/Metrics/Metric/minValue)[1]'', ''varchar(max)'') AS minValue, Data.value(''/Sqm/Metrics/Metric/maxValue)[1]'', ''varchar(max)'') AS maxValue, Data.value(''/Sqm/Metrics/Metric/stdDeviation)[1]'', ''varchar(max)'') AS stdDeviation, FROM Sqm

Para mí, esto es mucho menos confuso que usar la aplicación externa o la aplicación cruzada.

¡Espero que esto ayude a alguien más a buscar una solución más simple!

No entiendo por qué algunas personas sugieren usar cross apply o outer apply para convertir el xml en una tabla de valores. Para mí, eso acaba de traer demasiados datos.

Aquí está mi ejemplo de cómo crearía un objeto xml , luego conviértalo en una tabla.

(He agregado espacios en mi cadena xml, solo para que sea más fácil de leer).

DECLARE @str nvarchar(2000) SET @str = '''' SET @str = @str + ''<users>'' SET @str = @str + '' <user>'' SET @str = @str + '' <firstName>Mike</firstName>'' SET @str = @str + '' <lastName>Gledhill</lastName>'' SET @str = @str + '' <age>31</age>'' SET @str = @str + '' </user>'' SET @str = @str + '' <user>'' SET @str = @str + '' <firstName>Mark</firstName>'' SET @str = @str + '' <lastName>Stevens</lastName>'' SET @str = @str + '' <age>42</age>'' SET @str = @str + '' </user>'' SET @str = @str + '' <user>'' SET @str = @str + '' <firstName>Sarah</firstName>'' SET @str = @str + '' <lastName>Brown</lastName>'' SET @str = @str + '' <age>23</age>'' SET @str = @str + '' </user>'' SET @str = @str + ''</users>'' 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:

use value lugar de query (debe especificar el índice del nodo para devolver en XQuery, así como pasar el tipo de datos sql para devolver como el segundo parámetro):

select xt.Id , x.m.value( ''@id[1]'', ''varchar(max)'' ) MetricId from XmlTest xt cross apply xt.XmlData.nodes( ''/Sqm/Metrics/Metric'' ) x(m)