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

recuperar datos xml sql

Question

Tengo una tabla que contiene una columna Xml :

SELECT * 
FROM Sqm

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

<Sqm version="1.2">
  <Metrics>
    <Metric id="TransactionCleanupThread.RecordUsedTransactionShift" type="timer" unit="µs" count="1" sum="21490"   average="21490"   minValue="73701"    maxValue="73701"                               >73701</Metric>
    <Metric id="TransactionCleanupThread.RefundOldTrans"             type="timer" unit="µs" count="1" sum="184487"  average="184487"  minValue="632704"   maxValue="632704"                              >632704</Metric>
    <Metric id="Database.CreateConnection_SaveContextUserGUID"       type="timer" unit="µs" count="2" sum="7562"    average="3781"    minValue="12928"    maxValue="13006"    standardDeviation="16"     >12967</Metric>
    <Metric id="Global.CurrentUser"                                  type="timer" unit="µs" count="6" sum="4022464" average="670411"  minValue="15"       maxValue="13794345" standardDeviation="1642047">2299194</Metric>
    <Metric id="Global.CurrentUser_FetchIdentityFromDatabase"        type="timer" unit="µs" count="1" sum="4010057" average="4010057" minValue="13752614" maxValue="13752614"                            >13752614</Metric>
  </Metrics>
</Sqm>

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

• https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008
• SQL Server query xml attribute para un valor de elemento
• SQL que consulta atributos XML
• SQL Server 2005 XQuery y XML-DML - Parte 1
• BOL: Soporte XML en Microsoft SQL Server 2005
• Consultando XML en SQL Server
• Consulta básica de SQL Server XML
• BOL: método query () (tipo de datos xml)
• XML Workshop V - Lectura de valores de columnas XML
• SQL SERVER - Introducción al descubrimiento de métodos de tipos de datos XML - A Primer

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 *''

Answer 1

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

Answer 2

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!

Answer 3

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:

Answer 4

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)