Voici comment transformer tous les attributs situés à un même niveau de noeud d’un document XML

Dans cet exemple; nous allons simuler un document XML à l’aide d’un bout de texte. Nous aimerions transformer les attributs situés au niveau du noeud « /ArrayOfBedControlResult/BedControlResult » en une représentation tabulaire.
Ceci est grandement facilité par la possibilité de représenter le document en mémoire à l’aide de la procédure sp_xml_preparedocument qui lit le texte XML en entrée, l’analyse à l’aide de MSXML (Msxmlsql.dll), puis fournit le document XML analysé dans un état exploitable (représentation arborescente des différents nÅ“uds du document XML : éléments, attributs, textes, commentaires, etc…).
Dès lors il faut employer dans une requête SQL la clause OPENXML afin de transformer le XML en une table.

Voici un exemple :

-- variables locales 

DECLARE @hdoc int,         -- handle vers document en mémoire 

        @doc varchar(1000) -- document texte simulant le XML 

 

-- assignation XML 

SET @doc =' 

<ArrayOfBedControlResult> 

 

    <BedControlResult> 

 

        <Location>S07Robot</Location> 

        <Name>Callage sur cible X1</Name> 

        <MeasureData>0</MeasureData> 

        <Status>1</Status> 

        <IsCorrected>false</IsCorrected> 

        <Notes>Calib_X1 : OK</Notes>  

 

    </BedControlResult> 

    <BedControlResult> 

 

        <Location>S07Robot</Location> 

        <Name>Callage sur cible X2</Name> 

        <MeasureData>0</MeasureData> 

        <Status>1</Status> 

        <IsCorrected>false</IsCorrected> 

        <Notes>Calib_X2 : OK</Notes> 

 

    </BedControlResult> 

</ArrayOfBedControlResult>' 

 

-- mise en mémoire et préparationdu doc XML 

EXEC sp_xml_preparedocument @hdoc OUTPUT, @doc 

 

-- extraction sour forme tabulaire à la hauteur d'un noeud particulier 

SELECT *  

FROM OPENXML(@hdoc,  

             '/ArrayOfBedControlResult/BedControlResult',  --> noeud visé 

             2)                                            --> méthode utilisée 

     -- description des attributs de la table en retour pour chaque élément du noeud 

     WITH (Location    VARCHAR(32), 

           Name        VARCHAR(32), 

           MeasureData INT, 

           Status      SMALLINT, 

           IsCorrected VARCHAR(5), 

           Notes       VARCHAR(max)) 

 

-- suppression du doc en mémoire            

EXEC sp_xml_removedocument @hdoc 

 

-- résultat :  

Location    Name                    MeasureData Status IsCorrected Notes 

----------- ----------------------- ----------- ------ ----------- --------------- 

S07Robot    Callage sur cible X1    0           1      false       Calib_X1 : OK 

S07Robot    Callage sur cible X2    0           1      false       Calib_X2 : OK

La clause OPENXML utilise 3 arguments et une liste introduite par le mot clez WITH.

Les arguments de OPENXML sont les suivants :
idoc : la handle sur le document XML préparé à l’aide de la procédure sp_xml_preparedocument
rowpattern : le schéma XPATH à partir duquel on souhaite extraire les informations
flag : la méthode d’extraction des données (façon dont de fait le mappage entre les éléments du XML et la nature des attributs SQL en retour)
Ce denier paramètre peut prendre les valeurs suivantes :

• 0 : utilise par défaut le mappage attribute-centric.
• 1 : Utilise le mappage attribute-centric. Peut être combiné avec XML_ELEMENTS. Dans ce cas, le mappage attribute-centric est d’abord appliqué, puis le mappage element-centric est appliqué pour toutes les colonnes qui n’ont pas encore été traitées.
• 2 : Utilise le mappage element-centric. Peut être combiné avec XML_ATTRIBUTES. Dans ce cas, le mappage attribute-centric est d’abord appliqué, puis le mappage element-centric est appliqué pour toutes les colonnes qui n’ont pas encore été traitées.
• 8 : Peut être combiné (à l’aide de l’opérateur logique OR) avec XML_ATTRIBUTES ou XML_ELEMENTS. Dans le contexte d’une extraction, cet indicateur indique que les données consommées ne doivent pas être copiées vers la propriété de dépassement @mp:xmltext.

La liste introduite pas WITH décrit la table résultante.
Sans la clause WITH, le résultat de OPENXML est la représentation brute interne du document.

N’oubliez pas de vider la mémoire en appelant la procédure sp_xml_removedocument avec le handle du document.

—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *