APSQL

XML et SQL Server

Le format XML est de plus en plus utilisé pour échanger des données entre des sources diverses et variées. Il est donc important pour SQL Server de fournir les outils pour lire des données à ce format mais également produire un résultat au format XML. Ces opérations étaient déjà disponibles dans SQL Server 2000 bien qu'elles aient été améliorées avec SQL Server 2005.

SQL Serve propose également de stocker des informations au format XML directement dans la base. En effet SQL Server permet de définir dans une table relationnelle des colonnes de type XML. Des recherches vont pouvoir être effectuées via XQuery. Comme toute colonne relationnelle, il est possible de définir des index portant sur cette colonne.

Extraire des données au format XML

L'instruction SELECT dispose de l'option FOR XML qui permet de mettre au format XML les données issues de la requête. L'option FOR XML peut être utilisée dans les 4 modes suivants:

  • RAW
  • AUTO
  • EXPLICIT
  • PATH

FOR XML RAW

Avec cette option chaque ligne de résultat est un élément de type row (ligne) qui possède comme attribut les colonnes retournées par la requête.

Exemple utilisation FOR XML RAW

Il est toutefois possible d'influer sur cette mise en forme par défaut en ajoutant la directive ELEMENTS (for xml raw,elements). Ainsi les valeurs issues des différentes colonnes ne sont elles plus présentées comme des attributs de la balise row mais plutot comme la valeur de sous éléments de RAW.

Exemple utilisation for xml raw, elements

L'option TYPE permet quand à elle de récupérer les valeurs directement au format xml. Les données à ce format peuvent alors être enregistrées directement dans une colonne au format xml.

Enfin avec l'option XMLSCHEMA il est possible de récupérer directement un fichier XSD de description de la structure des données au format XML.

FOR XML AUTO

Cette option est sans aucun doute la plus connue car c'est aussi la plus facile d'accès. Les données vont être retournées sur un ou plusieurs niveau en respectant la hiérarchie des données. Cependant dans le cas ou la structure est complexe, il est préférable d'utiliser l'option EXPLICIT. Cependant pour des requêtes simples le résultat est très pratique.

Requete exemple for xml auto

Exemple résultat for xml auto

FOR XML EXPLICIT

Avec ce mode il est possible de contrôler et de structurer comme on le souhaite le document de sortie. Cependant avec le mode explicit, toutes les informations relatives à la structuration XML du résultat doivent être intégrée à la requête SELECT, cette contrainte pèse sur FOR XML EXPLICIT aussi l'option FOR XML PATH peut elle donner un résultat satisfaisant dans de nombreux cas avec l'avantage de conserver une requête d'extraction des données plus simple à écrire.

Pour afficher les informations au format XML, l'option EXPLICIT se base sur les 2 premières colonnes de la requête qui sont Tag et Parent. La colonne Tag contient le numéro (sous forme de valeur entière) de la balise tandis que la colonne Parent contient le numéro de la balise parent. Ces 2 premières colonnes permettent de mettre en place la structure hiérarchique du document XML.

En plus de cette contrainte portant sur les 2 premières colonnes de la requête, le nom des autres colonnes est structuré de façon à pouvoir former correctement le document XML. Le nom des colonnes va permettre de spécifier le nom des balises,  des attributs.

Cette ensemble de contraintes sur la struture du résultat peut rendre la requête SQL parfois complexe à écrire mais offre l'avantage de maitriser complètement le format du résultat.

Le nom des colonnes doit respecter la structure suivante nomBalise! numeroBalise!nomatribut!directive avec la signification suivante

  • nomElement : permet de définir le nom de la balise XML qui va contenir l'information
  • numeroBalise : ce numéro est associée à la valeur contenue dans la colonne Tag et permet ainsi de construire l'exact hiérarchie souhaitée.
  • nomAttribut : permet de spécifier le nom de l'attribut qui va contenir la valeur.
  • directives : permet de spécifier de façon optionnelle des options relatives à la construction du document XML comme par exemple la création de liens à l'intérieur du document.

L'exemple suivant permet de fournir au format xml le nom des différentes, les colonnes et leur type de données.

Le résultat est alors le suivant

FOR XML PATH

Le mode PATH à pour objectif de combiner les modes auto, dans lequel ne nombre de réglages est limité avec le mode explicit, qui est très complet mais également relativement lourd à utiliser. Le mode PATH utilise la nom de colonnes pour établir la structure des documents. Le nom des colonnes doivent utiliser les caractères @ et / pour préciser que la colonne va structurer une balise ou bien un attribut.

L'exemple suivant illustre les possibilités offertes par cette instruction.

Intégrer des données au format XML

Dans le cas où l'on dispose d'un document au format xml, il est toute à fait possible d'effectuer des requêtes Transact SQL dessus et de croiser ces données avec celles contenue dans la base de données. Le document XML doit être préparé à l'ai de la procédure stockée sp_xml_preparedocument. Ceci afin de charger en mémoire la structure du document xml. Bien entendu, il est nécessaire d'exécuter dès que possible sp_xml_removedocument afin de libérer la zone de mémoire utilisée. Le document XML ainsi chargé en mémoire peut être manipulé par l'intermédiaire de la méthode OPENXML. Cette méthode s'appuie sur les requêtes au format XPath pour extraire les données.

Les résultats extraits par OPENXML sont positionnés dans une table de bord. La requête SQL à utiliser la structure de cette table de bord pour effectuer des restrictions et des projection afin de ne conserver que l'information souhaitée.

Ces différentes méthodes sont illustrées dans l'exemple présenté ci dessous. Le document xml contient la liste des différentes régions de la France Métropolitaine. La méthode OPENXML permet de ne visualiser que les informations présentes au niveau de la balise region. Les colonnes text et nodetype de la table de bord vont être utilisées afin de n'afficher que le nom des régions.

 

Stocker des données au format XML

Il est possible dans SQL Server de stocker des données au format XML. Mais contrairement aux moteurs de base de données qui proposent de stocker les informations XML en dehors de la structure relationnelle classique, SQL Server propose de stocker ces données à l'intérieur même de la structure relationnelle. En effet SQL Server propose le type de donnée xml. Il est possible de définir des colonnes sur ce type comme il est possible de définir des colonnes qui vont contenir une valeur de type numérique, chaine de caractère, date, .... En incluant des données de type xml directement dans la structure des tables, cela permet de lier directement les informations entre elles mais cela permet également une gestion et une extraction plus efficaces des données.

Les données stockées dans une colonne de type xml peuvent être extraites de la base à l'aide d'une simple requête de type SELECT. Bien entendu pour rechercher des données à l'intérieur même d'une colonne de type XML il est nécessaire de s'appuyer sur les méthodes proposées par SQL Server et qui utilisent la syntaxe de type XPath pour sélectionner l'information. 

Afin d'améliorer la manipulation des données au format xml et pour permettre d'obtenir une exécution plus véloce des différentes requêtes, SQL Server propose de poser un ou plusieurs index sur les colonnes de type xml.  Même si la philosophie est la même que celle des index définis sur des colonnes de type caractère, numérique, image, ... la mise en pratique est différente. L'indexation de nœud à l'intérieur du document xml, s'effectue deux étapes. Premièrement, un index dit primaire est défini sur la colonne de type xml. Lorsque ce type d'indes est défini, il est possible de définir un ou plusieurs index dits secondaire.

Ces index sont utilisés de façon automatique par l'optimiseur de requêtes. Il n'est pas nécessaire de spécifier que l'on souhaite l'utilisation de tel ou tel index lors de l'exécution de la requête. C'est l'optimiseur de requête qui effectue le choix le plus opportun en fonction de la pertinence de l'index, du volume de données manipulé, ....

Création d'une table avec une colonne de type xml

Lors de la création d'une table, il possible d'utiliser le type xml pour créer une table qui va contenir des données de type xml. Par exemple le script suivant permet de créer une table munie d'une colonne de type xml:

Les colonnes de types xml, peuvent être associées à un schéma XSD afin de garantir la bonne structure des informations. Dans le cas ou la colonne s'appuie sur un schéma, il est nécessaire d'enregistrer le schéma dans la base par l'intermédiaire de l'instruction CREATE XML SCHEMA COLLECTION puis de passer le nom de ce schéma en paramètre de la colonne nouvellement crée. Le processus est identique lorsqu'il s'agit de définir une variable de type xml lors de la conception d'un traitement procédural.

Ajouter des données de type xml

Le travail avec les données de types xml pour les opérations d'ajout, de mise à jour et de suppression s'effectue par l'intermédiaire des instructions INSERT, UPDATE et DELETE comme pour toutes les informations stockées dans une base de données relationnelle.

Ainsi par exemple, l'instruction suivante permet d'ajouter des données à la table créée ci dessus.

Les méthodes pour extraire les informations

L'extraction pertinente des informations est opération importante, mais il est moins facile de travailler avec des données au format xml qu'avec des données stockées dans les bases de données relationnelles. Pour être capable de réaliser des extractions précises et simple à écrire, SQL Server propose un jeu de méthodes qui s'appuient sur XQuery. les méthodes proposées par SQL Server sont:

  • query(requêteXQuery): cette méthode retourne des données au format xml non typé et s'appuie sur la requête XQuery passée en paramètre pour extraire des informations.
  • value(requêteXQuery, type): cette méthode retourne une valeur correspondant au type passée en paramètre et représentant la valeur extraite à partir de la requête XQuery passée en paramètre.
  • exist(requêteXQuery): cette méthode retourne une valeur de type bit permettant d'identifier l'existence (1) ou non (0) de données extraite par la requête de type XQuery.
  • modify(instructionXMLDML): cette méthode permet de modifier un noeud du document XML contenu dans une colonne de type xml. ainsi il n'est pas nécessaire de modifier l'ensemble des données.
  • nodes(requêteXQuery): cette méthode permet d'extraire des noeuds depuis une colonne de type xml et stocker le résultat de cette extraction dans une structure de type relationnelle. Cette méthode va permettre de convertir des données actuellement au format xml vers un format relationnel classique.

Ces méthodes sont associées à la colonne de type xml aussi l'utilisation est nomDeLaColonne.méthode(paramètre).

Exemple d'utilisation

A partir de la table articles définies ci dessus, on souhaite, par exemple connaitre la marque d'un article. Cette information est présente dans la colonne descriptif au format xml. La méthode value va permettre d'extraire simplement cette valeur. La requête utilisée sera donc:

Définir des index

Comme pour toutes les autres colonnes définies au niveau des table, il est possible d'indexer les colonnes de type xml. Une même table peut utiliser jusqu'à 249 index XML. Les index XML ne peuvent indexer qu'une seule colonne XML. Il n'est pas possibile de définir des index qui références à la fois des colonnes relationnelle et des colonnes XML.

L'index primaire XML est créé sous la forme d'un index organisé. L'organisation de l'index XML reprend l'organisation définie au niveau de la table  par rapport à la clé primaire.

L'index secondaire XML ne peut être définie au niveau d'une colonne XML si et seulement si un index XML primaire existe déjà au niveau de la colonne.

Pour créer un index primaire XML, il est nécessaire d'utiliser l'instruction CREATE PRIMARY XML INDEX ..... Les index secondaires seront quand à eux créé avec CREATE XML INDEX. L'exemple présenté ci dessous montre la création d'un index XML sur la colonne de type XML puis la création d'un index XML secondaire, pour les valeurs. L'index Secondaire s'appuie sur l'index primaire.

Conclusion

L'intégration du format XML est complète dans SQL Server . En effet le serveur offre la possibilité non seulement d'extraire les informations actuellement stockées dans une structure relationnelle sous la forme de document XML. Mais il est également possible de travailler facilement avec des données au format XML dans la structure relationnelle via la méthode OPENXML.  Enfin les informations au format XML peuvent être directement intégrées dans la base de données à l'aide des colonnes de type XML. Il est possible de définir des index sur ces colonnes afin de rendre la manipulation plus rapide des informations au format XML. Avec ce type de fonctionnalités, SQL Server offre la possibilité de travailler de façon plus efficace avec les informations XML.