Tabulariser un XML générique avec SQL Server

Laisser une réponse

Le présent article vous propose de savoir comment tabulariser un XML
générique sans pour autant connaître le nom des balises XML en
utilisant une requête SQL avec XPath et XQuery…

Tour repose en effet sur deux éléments du langage XPath :

  • la fonction local-name qui permet de retourner le nom d’une balise.
  • le caractère générique * qui retourne les noeuds de l’axe qui
    ont un nom.

Démonstration

Création d’une table pour stockage de notre XML :

USE tempdb;
GO

CREATE TABLE T_XML
(XML_ID       INT IDENTITY PRIMARY KEY,
 XML_DATA     XML);
GO

Insertion d’un document XML doté de 2 niveaux d’arborescence :

INSERT INTO T_XML
VALUES (
'<config>
   <recovery_interval_min>0</recovery_interval_min>
   <fill_factor_percent>100</fill_factor_percent>
   <nested_triggers>0</nested_triggers>
   <server_trigger_recursion>0</server_trigger_recursion>
   <show_advanced_options>1</show_advanced_options>
   <priority_boost>0</priority_boost>
   <affinity_mask>119</affinity_mask>
   <cost_threshold_for_parallelism>5</cost_threshold_for_parallelism>
   <max_degree_of_parallelism>0</max_degree_of_parallelism>
   <max_server_memory_MB>8192</max_server_memory_MB>
   <query_governor_cost_limit>0</query_governor_cost_limit>
   <backup_compression_default>0</backup_compression_default>
   <optimize_for_ad_hoc_workloads>0</optimize_for_ad_hoc_workloads>
   <Agent_XPs>1</Agent_XPs>
   <SQL_Mail_XPs>0</SQL_Mail_XPs>
   <Database_Mail_XPs>1</Database_Mail_XPs>
   <SMO_and_DMO_XPs>1</SMO_and_DMO_XPs>
   <Ole_Automation_Procedures>1</Ole_Automation_Procedures>
   <xp_cmdshell>1</xp_cmdshell>
   <Ad_Hoc_Distributed_Queries>0</Ad_Hoc_Distributed_Queries>
</config>'
);

Et voici maintenant la requête pour ce faire :

SELECT X.XML_ID,
       Txml.XMLdata.VALUE('fn:local-name(.)', 'NVARCHAR(256)') AS BALISE,
       Txml.XMLdata.VALUE('.', 'NVARCHAR(256)') AS VALEUR
FROM   T_XML AS X
       OUTER APPLY X.XML_DATA.nodes('config/*') AS Txml(XMLdata);

Ce qui donne :

XML_ID      BALISE                               VALEUR
----------- ------------------------------------ ----------
1           recovery_interval_min                0
1           fill_factor_percent                  100
1           nested_triggers                      0
1           server_trigger_recursion             0
1           show_advanced_options                1
1           priority_boost                       0
1           affinity_mask                        119
1           cost_threshold_for_parallelism       5
1           max_degree_of_parallelism            0
1           max_server_memory_MB                 8192
1           query_governor_cost_limit            0
1           backup_compression_default           0
1           optimize_for_ad_hoc_workloads        0
1           Agent_XPs                            1
1           SQL_Mail_XPs                         0
1           Database_Mail_XPs                    1
1           SMO_and_DMO_XPs                      1
1           Ole_Automation_Procedures            1
1           xp_cmdshell                          1
1           Ad_Hoc_Distributed_Queries           0

Si votre XML comporte plusieurs niveaux, deux solutions…

1 – Atteindre tous les noeuds à tous les niveaux

Exemple

TRUNCATE TABLE T_XML;
GO
INSERT INTO T_XML
VALUES '
<network>
   <protocole>IP</protocole>
   <ipv4>
      <ip1>192</ip1>
      <ip2>168</ip2>
      <ip3>0  </ip3>
      <ip4>44 </ip4>
   </ipv4>
</network>');

Et dans ce cas, la requête doit atteindre tous les niveau à l’aide de //* :

SELECT X.XML_ID,
       Txml.XMLdata.VALUE('fn:local-name(.)', 'NVARCHAR(256)') AS BALISE,
       Txml.XMLdata.VALUE('.', 'NVARCHAR(256)') AS VALEUR
FROM   T_XML AS X
       OUTER APPLY X.XML_DATA.nodes('//*') AS Txml(XMLdata)

Mais notez que les niveaux non feuille « concatène » tous les informations

XML_ID      BALISE         VALEUR
----------- -------------- ------------------
1           network        IP1921680  44
1           protocole      IP
1           ipv4           1921680  44
1           ip1            192
1           ip2            168
1           ip3            0
1           ip4            44

2 РOn peut obtenir chaque niveau s̩par̩ment :
a – premier niveau

SELECT X.XML_ID,
       Txml.XMLdata.VALUE('fn:local-name(.)', 'NVARCHAR(64)') AS BALISE,
       Txml.XMLdata.VALUE('.', 'NVARCHAR(256)') AS VALEUR
FROM   T_XML AS X
       OUTER APPLY X.XML_DATA.nodes('*') AS Txml(XMLdata);

b – second niveau

SELECT X.XML_ID,
       Txml.XMLdata.VALUE('fn:local-name(.)', 'NVARCHAR(64)') AS BALISE,
       Txml.XMLdata.VALUE('.', 'NVARCHAR(256)') AS VALEUR
FROM   T_XML AS X
       OUTER APPLY X.XML_DATA.nodes('*/*') AS Txml(XMLdata);

d – 3e niveau

SELECT X.XML_ID,
       Txml.XMLdata.VALUE('fn:local-name(.)', 'NVARCHAR(64)') AS BALISE,
       Txml.XMLdata.VALUE('.', 'NVARCHAR(256)') AS VALEUR
FROM   T_XML AS X
       OUTER APPLY X.XML_DATA.nodes('*/*/*') AS Txml(XMLdata);

Les résultats sont :

XML_ID      BALISE          VALEUR
----------- --------------- ------------------
1           network         IP1921680  44

XML_ID      BALISE          VALEUR
----------- --------------- ------------------
1           protocole       IP
1           ipv4            1921680  44

XML_ID      BALISE          VALEUR
----------- --------------- ------------------
1           ip1             192
1           ip2             168
1           ip3             0
1           ip4             44

CQFD !

Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
Moste  Valuable  Professionnal  Microsoft  SQL Server
Société SQLspot  :  modélisation, conseil, formation,
optimisation,  audit,  tuning,  administration  SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.

L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR

MVP Microsoft SQL Server

Laisser un commentaire