Comment interroger les valeurs et les attributs Xml à partir d'une table dans SQL Server?

Question

J'ai une table qui contient une colonne Xml :

SELECT * 
FROM Sqm

Un exemple des données xml d'une ligne serait :

    73701
    632704
    12967
    2299194
    13752614

Dans le cas de ces données, je voudrais :

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      ...

À la fin, je vais en fait effectuer des agrégations avec SUM(), MIN(), MAX(). Mais pour l'instant, j'essaie simplement d'effectuer une requête sur une colonne xml.

En pseudo-code, j'essaierais quelque chose comme :

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

Mais cette requête SQL ne fonctionne pas :

Msg 2396, Niveau 16, État 1, Ligne 2
XQuery [Sqm.data.query()] : L'attribut ne peut pas apparaître en dehors d'un élément

J'ai cherché, et il est incroyable de voir à quel point la consultation de Xml est mal documentée ou illustrée. La plupart des ressources, plutôt que de requêter une table, requêtent une variable ; ce que je ne fais pas. La plupart des ressources utilisent la consultation xml pour le filtrage et la sélection, plutôt que pour la lecture des valeurs. La plupart des ressources lisent des nœuds enfants codés en dur (par index), plutôt que des valeurs réelles.

Ressources connexes que j'ai lues

• https://stackoverflow.com/questions/966441/xml-query-in-sql-server-2008
• SQL Server query xml attribute for an element value
• SQL querying XML attributes
• SQL Server 2005 XQuery and XML-DML - Part 1
• BOL: XML Support in Microsoft SQL Server 2005
• Querying XML in SQL Server
• Basic SQL Server XML Querying
• BOL: query() Method (xml Data Type)
• XML Workshop V - Reading Values from XML Columns
• SQL SERVER – Introduction to Discovering XML Data Type Methods – A Primer

Mise à jour : .value plutôt que .query

J'ai essayé aléatoirement d'utiliser .value, à la place 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

Mais cela ne fonctionne pas non plus :

Msg 2389, Niveau 16, État 1, Ligne 3 XQuery [Sqm.data.value()] :
'value()' exige un singleton (ou une séquence vide), trouvé un opérande de type 'xdt:untypedAtomic *'

Answer 1

En fait, vous êtes proche de votre objectif, il vous suffit d'utiliser la méthode nodes() pour diviser vos lignes et ensuite obtenir les valeurs :

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)

<a href="http://sqlfiddle.com/#!3/263bc/5">démonstration sql fiddle</a>

Answer 2

J'ai essayé de faire quelque chose de très similaire mais sans utiliser les nœuds. Cependant, ma structure XML est un peu différente.

Vous l'avez comme ceci:

Si c'était comme ceci à la place:

        TransactionCleanupThread.RefundOldTrans
        timer
        .
        .
        .

Alors vous pourriez simplement utiliser cette instruction 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

Pour moi, c'est beaucoup moins confus que d'utiliser la clause outer apply ou cross apply.

J'espère que cela aidera quelqu'un d'autre à la recherche d'une solution plus simple!

Answer 3

Utilisez valeur au lieu de requête (doit spécifier l'index du nœud à retourner dans la XQuery ainsi que passer le type de données SQL à retourner en tant que deuxième paramètre) :

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

Answer 4

Je ne comprends pas pourquoi certaines personnes suggèrent d'utiliser cross apply ou outer apply pour convertir le xml en une table de valeurs. Pour moi, cela ramenait simplement trop de données.

Voici mon exemple de comment vous créeriez un objet xml, puis le transformeriez en une table.

(J'ai ajouté des espaces dans ma chaîne xml, juste pour faciliter la lecture.)

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) 

-- Iterer à travers chaque enregistrement "users\user" dans notre 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)

Et voici la sortie: