SQL et XML

J'ai du XML, comment en extraire les données, avec SQL Server bien sûr ??

Exemple de XML : 

<Instances>
    <Instance Caption="nom1" Value="234" />
    <Instance Caption="nom2" Value="42" />
    <Instance Caption="nom3" Value="37" />
</Instances>

Un simple CONVERT en XML, fonctionne si le xml est bien formé. Mais pour en extraire les données je propose : 

WITH N AS (
    SELECT CONVERT(XML, '<Instances>
    <Instance Caption="nom1" Value="234" />
    <Instance Caption="nom2" Value="42" />
    <Instance Caption="nom3" Value="37" />
</Instances>') AS [xml]
)
SELECT m.c.value('@Caption', 'VARCHAR(MAX)') AS Caption
             ,  m.c.value('@Value', 'INT') AS Value
FROM N 
OUTER APPLY N.xml.nodes('Instances/Instance') AS m(c)
;

Ouai pas simple !  j'essaie d'expliquer…

• WITH pour créer une table N avec une colonne [xml]
• N.[xml].nodes (Attention nodes en minuscule obligatoire !!) pour éclater les données xml en données relationnelles (Voir documentation) 
• N.xml.nodes('...') AS m(c) pour créer une table m avec une colonne c. 
• N.xml.nodes('Instances/Instance')  A noter ici "le chemin xml" passé 'Instances/Instance' pour savoir sur quoi itérer.
• OUTTER APPLY pour calculer les lignes à partir du xml (Voir une bonne explication) 
• m.c.value(XQuery, SQLType) pour extraire les valeurs des attributs  (Attention value en minuscule obligatoire !!) (Voir Documentation). A noter l'attribut s'extrait avec un @ devant.

Ce pattern peut s'appliquer de manière systématique. Il peut rendre des services.

Enjoy!