Prise en charge du XML dans SQL Server

Hinault Romaric DONFACK

Vous découvrirez tout au long de cet article les fonctionnalités introduites dans SQL Server pour la prise en charge du XML. ?

Commentez

- Titre : Prise en charge du XML dans SQL Server

- Auteur : Hinault Romaric DONFACK

- Parution : 1 juin 2010

- Licence : Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright ® 2010 Hinault Romaric DONFACK. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. Prérequis

- II. Introduction

- III. Construction des données XML à partir des données relationnelles : La clause FOR XML

- III-A. Arguments optionnels

- III-B. Arguments obligatoires

- III-B-1. FOR XML RAW

- III-B-2. FOR XML AUTO

- III-B-3. FOR XML EXPLICIT

- III-B-4. FOR XML PATH

- IV. Le type de données XML

- IV-A. Arguments justifiant le stockage des données XML dans les bases de données relationnelles

- IV-B. Manipulation du type de données XML

- IV-C. Indexation d'une colonne XML

- IV-C-1. Index primaire

- IV-C-2. Index secondaire

- IV-D. XML Typé

- IV-E. Limite des types de données XML

- V. Langage de requête XML (XQuery)

- V-A. Query()

- V-B. Value()

- V-C. Exist()

- V-D. Nodes()

- V-E. Modify()

- VI. Manipulation XML côté client

- VII. Amélioration de la prise en charge XML dans SQL Server 2008

- VII-A. Amélioration de la validation de schéma XML

- VII-A-1. Support de la validation « molle »

- VII-A-2. Support Complet de la validation Date, Time et DateTime

- VII-A-3. Support de l'union et des types liste

- VII-B. Amélioration du Support de XQuery

- VIII. Conclusion

- IX. Remerciements

I - Prérequis

Des connaissances de base sur le Langage XML, le langage de Requêtes Xpath et sur le SGBD SQL Server 2005 sont nécessaires pour la bonne compréhension de cet article.

II - Introduction

Le langage XML (eXtensible Markup Language) a été largement adopté en tant que format d'échange de données entre différents systèmes, plates-formes et organisations. Le langage XML a pour principaux avantages : l'indépendance de la plate-forme pour la représentation des données ; il permet d'échanger des informations entre des systèmes hétérogènes et faiblement couplés. Le langage XML est donc un modèle de données adapté à toute combinaison de données structurées, non structurées ou semi-structurées.

Depuis plusieurs années les principaux systèmes de bases de données relationnelles assurent la prise en charge du langage XML. SQL Server intègre la prise en charge du XML depuis la version 2000, grâce notamment à l'introduction de la clause For XML, et à partir de la version 2005 la prise en charge complète du traitement des données XML. Des données XML peuvent être stockées de façon native dans une colonne de type XML.

Vous découvrirez tout au long de cet article les fonctionnalités introduites dans SQL Server pour la prise en charge du XML.

III - Construction des données XML à partir des données relationnelles : La clause FOR XML

La clause FOR XML, introduite dans les versions de SQL Server 2000 et améliorée dans SQL Server 2005, permet de publier et traiter les données sous un format XML. L'introduction de FOR XML dans une requête Select permet de récupérer le résultat d'une requête SQL sous forme de code XML. Elles peuvent s'exécuter directement ou à partir d'une fonction ou procédure stockée définie par l'utilisateur.

La syntaxe de base FOR XML est la suivante :

FOR XML mode
[, XMLDATA][, ELEMENTS][, BINARY BASE64] [, TYPE ]
[, ROOT [ ('RootName') ] ]

III-A - Arguments optionnels

- XMLDATA : cette option permet d'indiquer qu'un schéma XDR (XML-Data Reduced) doit être retourné (le schéma est ajouté au début du document). Cette option est facultative ;

- ELEMENTS : l'option ELEMENTS impose que les colonnes soient renvoyées sous forme de sous-éléments. Sinon, elles sont mappées avec des attributs XML. Cette option n'est prise en charge que dans les modes RAW, AUTO et PATH ;

- BINARY BASE64 : si vous utilisez cette option, vous indiquez que les données binaires doivent être représentées dans un format codé en base64 ;

- TYPE : spécifie que la requête renvoie les résultats sous le type xml ;

- Root : cette option permet de spécifier qu'un seul élément de premier niveau doit être ajouté au code XML résultant. La valeur par défaut de l'élément racine est « root », vous pouvez la modifier si vous le souhaitez.

III-B - Arguments obligatoires

mode définit le mode XML qui sera utilisé pour déterminer la structure des sorties XML pour la requête. Les différents modes sont :

- RAW ;

- AUTO ;

- EXPLICIT ;

- PATH.

Pour tous les exemples de cette section, nous utiliserons la base de données AdventureWorks. Il est à noter que la base de données AdventureWorks n'est pas installée par défaut avec SQL Server.

III-B-1 - FOR XML RAW

Le mode RAW permet de transformer chaque ligne de la requête en un élément XML contenant l'identificateur générique « row ». Les valeurs non nulles des colonnes sont représentées comme attributs de l'élément « row ». C'est le mode le plus basique, puisqu'il ne permet aucune organisation du format XML de sortie.

Exemple :

SELECT EmployeeID,
BirthDate,
FirstName,
MiddleName,
LastName
FROM HumanResources.Employee E
inner join Person.Contact C
on E.ContactID = C.ContactID
ORDER BY EmployeeID, FirstName
FOR XML RAW

On obtient le résultat suivant :

<row EmployeeID="1" BirthDate="1972-05-15T00:00:00" FirstName="Guy" MiddleName="R" LastName="Gilbert" />
<row EmployeeID="2" BirthDate="1977-06-03T00:00:00" FirstName="Kevin" MiddleName="F" LastName="Brown" />
<row EmployeeID="3" BirthDate="1964-12-13T00:00:00" FirstName="Roberto" LastName="Tamburello" />
<row EmployeeID="4" BirthDate="1965-01-23T00:00:00" FirstName="Rob" LastName="Walters" />

Il est possible de remplacer le nom du nœud par celui de son choix. Pour cela, il suffit alors de mettre entre parenthèses ce nom après l'indicateur FOR XML RAW.

Exemple :

SELECT EmployeeID,
BirthDate,
FirstName,
MiddleName,
LastName
FROM HumanResources.Employee E
inner join Person.Contact C
on E.ContactID = C.ContactID
ORDER BY EmployeeID, FirstName
FOR XML RAW('Employee')

Et l'on obtient le résultat suivant :

<Employee EmployeeID="1" BirthDate="1972-05-15T00:00:00" FirstName="Guy" MiddleName="R" LastName="Gilbert" />
<Employee EmployeeID="2" BirthDate="1977-06-03T00:00:00" FirstName="Kevin" MiddleName="F" LastName="Brown" />
<Employee EmployeeID="3" BirthDate="1964-12-13T00:00:00" FirstName="Roberto" LastName="Tamburello" />
<Employee EmployeeID="4" BirthDate="1965-01-23T00:00:00" FirstName="Rob" LastName="Walters" />

III-B-2 - FOR XML AUTO

Cette option permet de générer un document XML potentiellement hiérarchique, où chaque table de la clause FROM, dont au moins une colonne est spécifiée dans le SELECT, est représentée sous la forme d'un élément XML. Les colonnes sont représentées comme attribut de l'élément.

Exemple :

SELECT EmployeeID,
BirthDate,
FirstName,
MiddleName,
LastName
FROM  HumanResources.Employee E
inner join Person.Contact C
on E.ContactID = C.ContactID
ORDER BY EmployeeID, FirstName
FOR XML AUTO

On obtient le résultat suivant :

<E EmployeeID="1" BirthDate="1972-05-15T00:00:00">
<C FirstName="Guy" MiddleName="R" LastName="Gilbert" />
</E>
<E EmployeeID="2" BirthDate="1977-06-03T00:00:00">
<C FirstName="Kevin" MiddleName="F" LastName="Brown" />
</E>
<E EmployeeID="3" BirthDate="1964-12-13T00:00:00">
<C FirstName="Roberto" LastName="Tamburello" />
</E>
<E EmployeeID="4" BirthDate="1965-01-23T00:00:00">
<C FirstName="Rob" LastName="Walters" />
</E>

Bien que le document XML généré soit plus cohérent que celui avec le mode RAW, il est à noter que ce mode ne laisse pas beaucoup de liberté quant à la création du document.

III-B-3 - FOR XML EXPLICIT

Bien que le mode AUTO soit plus souple que le mode RAW, le mode EXPLICIT offre une plus grande souplesse pour la génération du document XML en contrôlant sa structure. Pour ce faire :

La requête doit, dans un premier temps, générer les deux colonnes de métadonnées suivantes :

- Tag : qui stocke le numéro de l'élément courant de type entier ;

- Parent : qui stocke le numéro de l'élément parent.

Ces deux éléments sont nécessaires pour déterminer la hiérarchie de l'arbre XML généré. La valeur 0 ou NULL dans la colonne Parent indique que l'élément correspondant n'a pas de parent. L'élément est ajouté au document XML en tant qu'élément de niveau supérieur.

Par la suite nous aurons des informations sous ce format :

ElementName!TagNumber!AttributeName

Dont la description de chaque partie est la suivante :

- ElementName : identificateur générique obtenu depuis l'élément. Par exemple, si Employee est spécifié en tant que ElementName, le nœud « Employee » est généré ;

- TagNumber : valeur de balise unique affectée à un élément. Permet d'indiquer la profondeur de l'élément ;

- AttributeName : fournit le nom de l'attribut à construire dans l'option ElementName spécifiée.

Exemple :

SELECT 1    as Tag,
NULL as Parent,
EmployeeID as [Employee!1!EmpID],
BirthDate  as [Employee!1!BDate],
NULL       as [Name!2!FName],
NULL       as [Name!2!MName],
NULL       as [Name!2!LName]
FROM   HumanResources.Employee E
inner join Person.Contact C
on E.ContactID = C.ContactID
UNION ALL
SELECT 2 as Tag,
1 as Parent,
EmployeeID,
BirthDate,
FirstName,
MiddleName,
LastName
FROM   HumanResources.Employee E
inner join Person.Contact C
on E.ContactID = C.ContactID
ORDER BY [Employee!1!EmpID],[Name!2!FName]
FOR XML EXPLICIT

Résultat partiel :

<Employee EmpID="1" BDate="1972-05-15T00:00:00">
<Name FName="Guy" MName="R" LName="Gilbert" />
</Employee>
<Employee EmpID="2" BDate="1977-06-03T00:00:00">
<Name FName="Kevin" MName="F" LName="Brown" />
</Employee>
<Employee EmpID="3" BDate="1964-12-13T00:00:00">
<Name FName="Roberto" LName="Tamburello" />
</Employee>
<Employee EmpID="4" BDate="1965-01-23T00:00:00">
<Name FName="Rob" LName="Walters" />
</Employee>

III-B-4 - FOR XML PATH

SQL Server 2005 introduit le mode XML PATH qui permet de pallier la complexité avec XML EXPLICIT. Ce mode permet de combiner les éléments et les attributs de façon simplifiée.

Les noms ou alias de colonnes sont traités en tant qu'expressions XPath et sont utilisés pour établir la structure du document XML retourné.

Les caractères « @ » et « / » sont utilisés pour préciser que la colonne va structurer une balise ou bien un attribut.

Exemple :

SELECT EmployeeID,
BirthDate,
FirstName,
MiddleName,
LastName
FROM   HumanResources.Employee E
inner join Person.Contact C
on E.ContactID = C.ContactID
ORDER BY EmployeeID, FirstName
FOR XML PATH

Résultat obtenu :

<row>
<EmployeeID>1</EmployeeID>
<BirthDate>1972-05-15T00:00:00</BirthDate>
<FirstName>Guy</FirstName>
<MiddleName>R</MiddleName>
<LastName>Gilbert</LastName>
</row>
<row>
<EmployeeID>2</EmployeeID>
<BirthDate>1977-06-03T00:00:00</BirthDate>
<FirstName>Kevin</FirstName>
<MiddleName>F</MiddleName>
<LastName>Brown</LastName>
</row>
<row>
<EmployeeID>3</EmployeeID>
<BirthDate>1964-12-13T00:00:00</BirthDate>
<FirstName>Roberto</FirstName>
<LastName>Tamburello</LastName>
</row>
<row>
<EmployeeID>4</EmployeeID>
<BirthDate>1965-01-23T00:00:00</BirthDate>
<FirstName>Rob</FirstName>
<LastName>Walters</LastName>
</row>

IV - Le type de données XML

SQL Server 2005 introduit un type de données natif appelé XML. L'utilisateur peut créer une table composée d'une ou plusieurs colonnes de type XML en plus des colonnes relationnelles ; les variables et les paramètres XML sont également autorisés. Les valeurs XML sont stockées dans un format interne sous la forme d'un BLOB (objet binaire volumineux) afin de mieux respecter les caractéristiques du modèle XML telles que l'ordre du document et les structures récursives.

IV-A - Arguments justifiant le stockage des données XML dans les bases de données relationnelles

Les applications actuelles nécessitent de manière plus fréquente une combinaison de données relationnelles et de données XML. Je vais essayer de vous donner, dans cette section, quelques raisons justifiant l'intérêt du stockage des données XML natif dans une base de données SQL Server :

- la décomposition du langage XML en tables relationnelles reste onéreuse au moment de l'insertion, du fait de l'analyse XML et des insertions multitables qui prennent trop de temps ;

- on peut se retrouver avec plusieurs fichiers XML à un moment donné, et la gestion de ceux-ci devient alors un peu complexe ;

- le stockage des données XML dans les colonnes de type VARCHAR(MAX) ou TEXT présentent les inconvénients suivants :
. non-validation des données,
. l'incapacité des requêtes sur des nœuds spécifiques,
. modifications des valeurs spécifiques… ;

- le souhait de garantir une parfaite interopérabilité entre vos données relationnelles et XML au sein d'une application ;

- profiter des fonctions d'administration du serveur de base de données pour gérer vos données XML.

IV-B - Manipulation du type de données XML

Le type de données XML est un type de données de haut niveau de première classe, tout comme INT ou VARCHAR. Il fonctionne comme les autres types, et plusieurs méthodes ont été prévues pour interroger et mettre à jour les données stockées dans une colonne ou variable XML.

La création d'une table comportant une ou plusieurs colonnes de type XML est très simple et se fait grâce à l'instruction usuelle CREATE TABLE.

Exemple :

CREATE TABLE XmlTable (pk INT PRIMARY KEY, XmlCol XML not null)

Nous venons de créer une table XmlTable avec une colonne pk comme clé primaire et une colonne XmlCol de type XML. Vous pouvez également créer une table avec plusieurs colonnes de type XML, avec ou sans clé primaire.

Le stockage d'un document XML dans une table peut se faire de plusieurs façons. La méthode la plus simple est la suivante :

INSERT INTO XmlTable
values(1,'<?xml version="1.0" encoding="utf-8"?>
<Employees>
<Employee EmployeeID="1">
<FirstName>Guy</FirstName>
<LastName>Gilbert</LastName>
<BirthDate>1972-05-15T00:00:00</BirthDate>
</Employee>
<Employee EmployeeID="2">
<FirstName>Kevin</FirstName>
<LastName>Brown</LastName>
<BirthDate>1977-06-03T00:00:00</BirthDate>
</Employee>
</Employees>')

Nous venons, avec l'instruction précédente, d'insérer une nouvelle ligne dans notre table avec la valeur 1 pour la colonne pk et un document « Employees » pour la colonne XmlCol. Avec cette méthode on recopie intégralement le contenu du fichier XML dans la requête ; donc méthode à proscrire dans le cas où vous avez plusieurs fichiers. Il est à noter que le document « Employees » fourni en tant que chaîne est converti implicitement en type de données XML et sa construction fait l'objet d'une vérification pendant l'insertion.

La méthode suivante consiste à fournir l'emplacement du fichier XML à SQL Server, qui se charge de lire le fichier et copier son contenu dans la colonne XML.

INSERT INTO XmlTable
SELECT 2, XmlCol
FROM    (SELECT * FROM OPENROWSET
(BULK 'C:\xmlfile.xml',
SINGLE_BLOB) AS XmlCol) AS D(XmlCol)

SQL Server lit le fichier C:\xmlfile.xml comme un BLOB, grâce à la méthode OPENROWSET. Ensuite, une nouvelle ligne est insérée avec, comme clé, la valeur 2 et le BLOB comme document XML.

IV-C - Indexation d'une colonne XML

Les données XML sont stockées sous forme d'objet binaire volumineux (BLOB) pouvant atteindre chacun 2 Go dans votre base de données. De ce fait, les requêtes qui effectuent une recherche dans une colonne XML peuvent s'avérer très longues, car les BLOB seront décomposés par la requête. Pour pallier cela, SQL Server propose un index principal et trois index secondaires vous permettant d'accélérer vos requêtes.

Un index XML ne peut être créé sur une vue. L'index principal peut être créé si et seulement si un index cluster sur la clé primaire de la table de base existe.

IV-C-1 - Index primaire

L'index XML primaire est une représentation fragmentée et persistante des objets BLOB XML inclus dans la colonne de type XML. L'index XML primaire crée plusieurs lignes de données pour chacun des objets BLOB XML de la colonne, chaque ligne dans l'index XML primaire correspond à un nœud se trouvant dans l'objet BLOB XML.

L'index XML primaire indexe toutes les balises, valeurs et chemins d'accès rencontrés dans les documents XML d'une colonne XML.

La syntaxe pour la création d'un index primaire est la suivante :

CREATE PRIMARY XML INDEX
Index_Name ON Table_Name (Column_Name)

Exemple :

CREATE PRIMARY XML INDEX
Index_XmlTable ON XmlTable (XmlCol)

Un index XML primaire permettra d'accélérer la plupart des requêtes sur une colonne XML. C'est nécessaire d'en créer un chaque fois que vous avez une colonne XML qui contient de grandes quantités de données.

IV-C-2 - Index secondaire

En plus de l'index primaire, vous pouvez créer trois autres index secondaires. La création de l'index secondaire n'est possible que si l'index primaire a été créé. L'index XML secondaire aide pour les requêtes qui spécifient les expressions de chemin sur les colonnes XML (sélection des valeurs spécifiques dans la colonne XML).

Il existe trois types d'index secondaires qui sont les suivants :

- l'index secondaire PATH: Indique au moteur XML de créer un index sur la structure du document. Il est identifié par les mots clés FOR PATH ;

- l'index secondaire PROPERTY : Cet index profite aux recherches des valeurs de propriété dans une instance XML. Il est identifié par les mots clés FOR PROPERTY ;

- l'index secondaire VALUE : cet index profite aux requêtes où une valeur de nœud est connue, mais son chemin n'est pas spécifié de manière précise dans la requête (utile pour la recherche du contenu). Il est identifié par les mots clés FOR VALUE.

La syntaxe pour la création d'un index secondaire est la suivante :

CREATE XML INDEX Index_Name
ON Table_name (Column_name)
USING XML INDEX xml_index_name
FOR {VALUE|PATH|PROPERTY}

Exemple :

CREATE XML INDEX Index_XmlTable_Property
ON XmlTable (XmlCol)
USING XML INDEX Index_XmlTable
FOR PROPERTY

IV-D - XML Typé

SQL Server 2005 fournit des collections de schémas XML afin de permettre la gestion de schémas W3C XML en tant que métadonnées. Un type de données XML peut être associé à une collection de schémas XML pour que les contraintes de schéma soient appliquées sur les instances XML.

Si vous disposez de schémas XML dans une collection de schémas XML qui décrit vos données XML, vous pouvez associer la collection de schémas XML à la colonne XML pour obtenir du XML typé. Les schémas XML permettent de valider les données, d'effectuer des vérifications et d'optimiser le stockage et le traitement des requêtes.

Vous pouvez créer un schéma comme suit :

CREATE XML SCHEMA COLLECTION EmpXMLSchemaCollection AS
N'<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:attribute name="EmployeeID" type="xs:integer" />
<xs:element name="FirstName" type="xs:string"/>
<xs:element name="LastName" type="xs:string"/>
<xs:element name="BirthDate" type="xs:dateTime" />
<xs:element name="Employee">
<xs:complexType mixed="true">
<xs:sequence>
<xs:element ref="FirstName"/>
<xs:element ref="LastName" />
<xs:element ref="BirthDate" />
</xs:sequence>
<xs:attribute ref="EmployeeID" />
</xs:complexType>
</xs:element>
<xs:element name="Employees">
<xs:complexType>
<xs:sequence>
<xs:element ref="Employee"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>';

Et la syntaxe pour la création de la table doit être modifiée comme suit :

CREATE TABLE XmlTable (pk INT PRIMARY KEY, XmlCol XML(CONTENT EmpXMLSchemaCollection))

IV-E - Limite des types de données XML

Les types de données XML présentent les quelques limites suivantes :

- la conversion en text et en ntext n'est pas prise en charge ;

- un type de données XML ne peut pas être utilisé dans une instruction GROUP BY (tri et comparaison des types de données XML impossibles) ;

- ils ne peuvent pas être utilisés en tant que paramètre d'une fonction scalaire intégrée autre que ISNULL, COALESCE et DATALENGTH ;

- ils ne prennent pas en charge les contraintes de colonne et de table suivantes : PRIMARY KEY/ FOREIGN KEY, UNIQUE, COLLATE.

V - Langage de requête XML (XQuery)

XQuery est un langage qui permet l'interrogation et la modification des données XML stockées dans une colonne de type XML. XQuery est basé sur le langage de requête XPATH 2.0 et est, en quelque sorte, au XML ce que le langage SQL représente pour les bases de données relationnelles. XQuery est développé par le Worldwide Web Consortium (W3C) en partenariat avec tous les fournisseurs de base de données, notamment Microsoft. Vous pouvez trouver les spécifications du langage XQuery à l'adresse suivante. http://www.w3.org/TR/xquery/

Transact-SQL supporte un sous ensemble de méthodes fourni par XQuery vous permettant de manipuler les types de données XML dans SQL Server. Nous nous limiterons, dans cette partie, à présenter quelques fonctionnalités de XQuery (sans toutefois entrer dans les détails) supportées par SQL Server.

XQuery dans SQL Server se résume essentiellement au sein de cinq méthodes permettant de manipuler avec souplesse le XML. Il s'agit de :

- Query() : pour l'extraction des parties d'un document XML ;

- Value() : pour l'extraction d'une valeur d'un document XML ;

- Exist() : pour la vérification de l'existence d'un nœud ou d'une valeur spécifique dans un document XML ;

- Nodes() : pour fragmenter le document XML en nœuds correspondant au chemin Xpath spécifié ;

- Modify() : pour la modification d'un fragment du document XML.

V-A - Query()

Cette méthode permet d'extraire des fragments d'un document XML; le résultat retourné est du XML non typé.

La syntaxe est la suivante :

query('XQuery')

L'argument XQuery est une expression XQuery qui interroge les nœuds XML dans un document XML.

Exemple :

select XmlCol.query('/Employees/Employee[@EmployeeID="1"]')
from
XmlTable

L'exemple ci-dessus sélectionne, dans le document « Employees », le nœud enfant « Employee » dont l'attribut @EmployeeID= « 1 ». Ce qui donne comme résultat pour notre cas :

<Employee EmployeeID="1">
<FirstName>Guy</FirstName>
<LastName>Gilbert</LastName>
<BirthDate>1972-05-15T00:00:00</BirthDate>
</Employee>

Pour récupérer la valeur d'un nœud, vous pouvez utiliser la fonction d'atomisation data.

Exemple :

select XmlCol.query('data(/Employees/Employee[@EmployeeID="1"]/FirstName)')
as FirstName
from
XmlTable

Qui retourne comme résultat la valeur du nœud enfant « FirstName » du nœud « Employee » dont l'attribut @EmployeeID = « 1 ». Ce qui donne :
Guy.

Dans le cas où vous utilisez une colonne « XML Typé », il est obligatoire de préciser le namespace dans la requête. Elle doit alors s'écrire comme suit :

select XmlCol.query('declare namespace ns="EmpXMLSchemaCollection";
/ns:Employees/ns:Employee[@ns:EmployeeID="1"]')
from
XmlTable

V-B - Value()

Cette méthode permet d'extraire une valeur de nœud d'un document XML et de la retourner dans un type SQL passé en paramètre.

La syntaxe est la suivante :

value ('XQuery', 'SQLType')

L'argument XQuery est une expression XQuery qui interroge les nœuds XML dans un document XML. L'argument XQuery doit retourner au plus une valeur.

L'argument SQLType est le type SQL qui est retourné.

Exemple :

select XmlCol.value('(/Employees/Employee/FirstName)[2]'
,'VarChar(120)') as FirstName
from
XmlTable

Il faut noter ici la présence de [2] qui va retourner la deuxième ligne du résultat. Ce qui donne : Kevin

V-C - Exist()

Cette méthode permet de vérifier l'existence d'une valeur ou d'un nœud dans un document XML. Le résultat retourné est un bit (0 ou 1).

Syntaxe :

exist ('XQuery')

Exemple :

select XmlCol.exist('(/Employees/Employee/FirstName)[2]')
as Result
from XmlTable

L'exemple ci-dessus vérifie l'existence de l'élément « Firstname » dans le deuxième nœud enfant de l'élément « Employees ». Ce qui donne: 1

V-D - Nodes()

Cette méthode permet de fragmenter le document XML d'origine en plusieurs lignes correspondant à la requête XQuery spécifiée. Node s'utilise dans la clause FROM. Les mots clés CROSS APPLY doivent figurer dans la requête.

Syntaxe :

nodes (XQuery) as Table(Column)

L'argument XQuery est une expression XQuery qui interroge les nœuds XML dans un document XML. L'argument XQuery doit retourner au plus une valeur.

L'argument Table(column) est le nom de table et de colonne des lignes obtenues.

Exemple :

SELECT T.C.query('.') as result
FROM XmlTable
CROSS APPLY XmlCol.nodes('/Employees/Employee') T(C);

Dans cet exemple, la méthode query renvoie l'élément de contexte et son contenu. Ce qui donne :

<Employee EmployeeID="1"><FirstName>Guy</FirstName><LastName>Gilbert</LastName><BirthDate>1972-05-15T00:00:00</BirthDate></Employee>
<Employee EmployeeID="2"><FirstName>Kevin</FirstName><LastName>Brown</LastName><BirthDate>19770603T00:00:00</BirthDate></Employee>

V-E - Modify()

Cette méthode est l'unique méthode permettant de modifier le contenu d'un document XML. Elle permet l'insertion, la mise à jour et la suppression des nœuds d'un document XML.

Syntaxe :

modify (XML_DML)

L'argument XML_DML est une expression XML_DML (Data Manipulation Language) utilisée pour la mise à jour. XML_DML est une extension du langage XQuery, créée par Microsoft.

Exemples

Modification d'une valeur de nœud :

UPDATE XmlTable
SET XmlCol.modify('
replace value of (/Employees/Employee/FirstName/text())[1]
with "John"')

L'exemple ci-dessus modifie la valeur du nœud « FirstName » du premier nœud enfant du document « Employees »

Insertion d'un nœud :

UPDATE XmlTable
SET XmlCol.modify('insert <Employee EmployeeID="3">
<FirstName>Steve</FirstName>
<LastName>Marley</LastName>
<BirthDate>1973-08-10T00:00:00</BirthDate>
</Employee> as last
into   (/Employees)[1]
')

L'exemple ci-dessus insère un nouveau nœud enfant « Employee » à la dernière position dans le document « Employees »

Suppression d'un nœud :

UPDATE XmlTable
SET XmlCol.modify('delete /Employees/Employee[1]')

L'exemple ci-dessus supprime le premier nœud enfant du document « Employees ».

VI - Manipulation XML côté client

Nous avons décrit, dans les sections précédentes, la façon dont SQL Server prend en charge le format XML. La question que l'on peut se poser maintenant est de savoir comment accéder à ses données côté client et les traiter ?

L'accès aux données XML côté client peut s'effectuer de plusieurs manières :

- l'accès au client SQL natif à l'aide de ODBC et OLE DB produit le type de données XML sous la forme de chaîne Unicode ;

- l'accès managé via ADO.NET dans le .NET Framework V2.0 produit des données XML sous la forme d'une nouvelle classe intitulée SqlXml. Il prend en charge une méthode appelée CreateReader() qui retourne une instance XmlReader afin de lire le code XML retourné ;

Voir : Utilisation de la colonne de type XML dans SQL Server 2005 avec ADO.net XML indexation

- la prise en charge XML côté client avec XQuery. Vous pouvez charger directement des données dans un document XML sur la couche intermédiaire à l'aide de la classe XqueryCommand ;

- l'accès via les classes managées SQLXML. Il existe un composant SQLXML greffé à SQL Server qui offre des fonctionnalités XML supplémentaires côté client. Les classes managées SQLXML sont prises en charge dans le .net Framework et offrent une certaine souplesse dans la manipulation du XML.

VII - Amélioration de la prise en charge XML dans SQL Server 2008

La prise en charge du XML dans la version 2008 de SQL Server repose sur la version 2005 (aucune nouvelle fonctionnalité n'a été intégrée). Mais quelques améliorations ont été apportées à celui-ci.

Les principales Améliorations XML dans SQL Server 2008 sont les suivantes :

- amélioration de la validation de schéma XML ;

- amélioration du Support de XQuery.

VII-A - Amélioration de la validation de schéma XML

SQL Server 2005 soutient un large ensemble de collections de schémas XML et a couvert la plupart des scénarios de validation XML. SQL Server 2008 étend ce support et inclut des schémas additionnels pour la validation. Il s'agit de :

- support de la validation « molle » ;

- support Complet de la validation Date, Time et DateTime ;

- support de l'union et des types liste.

VII-A-1 - Support de la validation « molle »

Pour augmenter la flexibilité d'un schéma XSD, les composants de « joker » sont souvent utilisés. D'habitude, on le fait en utilisant any, anyAttribute, anyType. Les composants de « joker » permettent d'ajouter les contenus qu'on ne connaît pas au moment de la création du schéma. SQL Server 2005 a des options pour sauter la validation ou exécuter une validation stricte de tels éléments. SQL Server 2008 supporte la validation « molle », qui valide seulement des éléments et des attributs pour lesquelles les déclarations de schéma sont disponibles.

VII-A-2 - Support Complet de la validation Date, Time et DateTime

Dans SQL Server 2005, pour un type DateTime, vous deviez fournir un fuseau horaire. Cependant, les informations de fuseau horaire pour vos données n'étaient pas conservées. Ces limites ont été supprimées dans SQL Server 2008, de telle sorte que vous pouvez omettre les informations de fuseau horaire lorsque vous stockez une valeur de type DateTime et, si vous les renseignez, celles-ci sont sauvegardées.

VII-A-3 - Support de l'union et des types liste

Avec SQL Server 2005, il est possible, dans un schéma XML, de définir une liste de valeurs possibles pour les éléments et attributs comme suit :

<xs:simpleType id="SizeListType">
<xs:list>
<xs:simpleType >
<xs:restriction base="xs:string">
<xs:enumeration value="XL" />
<xs:enumeration value="L" />
<xs:enumeration value="S" />
</xs:restriction>
</xs:simpleType>
</xs:list>
</xs:simpleType>

Pour une entreprise qui fait dans la vente des vêtements, par exemple, cette déclaration de schéma va permettre de créer un élément qui répertorie tous les formats dans lesquels un vêtement peut être acheté comme une liste de valeurs séparées par des espaces comme l'illustre l'exemple suivant :

<PossibleSizeTypes> XL L M </ PossibleSizeTypes>

Pour une entreprise qui, en plus des vêtements vend des chaussures, il serait nécessaire de créer plusieurs types liste et de multiples schémas XML avec SQL Server 2005. SQL Server 2008 intègre le support des types « union » qui permet de fusionner plusieurs définitions de type liste et les restrictions en un seul type.

Exemple

CREATE XML SCHEMA COLLECTION ProductSizeSchema AS
N'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:simpleType name="productSizeType">
<xs:union>
<xs:simpleType>
<xs:list>
<xs:simpleType>
<xs:restriction base="xs:integer">
<xs:enumeration value="24"/>
<xs:enumeration value="32"/>
<xs:enumeration value="44"/>
<xs:enumeration value="52"/>
</xs:restriction>
</xs:simpleType>
</xs:list>
</xs:simpleType>
<xs:simpleType>
<xs:list>
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:enumeration value="XL"/>
<xs:enumeration value="L"/>
<xs:enumeration value="S"/>
</xs:restriction>
</xs:simpleType>
</xs:list>
</xs:simpleType>
</xs:union>
</xs:simpleType>
</xs:schema>'

Le schéma « ProductSizeSchema » définit un type « ProductSizeType » qui est un type « union ». Ainsi, tout élément de type « ProductSizeType » peut contenir l'un ou l'autre type de liste, comme l'illustre l'exemple suivant :

<Products>
<Product>
<ProductName>Ballet Shoes</ProductName>
<PossibleSizeTypes>24 32 44</PossibleSizeTypes>
</Product>
<ProductName>teenager schirt</ProductName>
<PossibleSizeTypes>XL L S</PossibleSizeTypes>
</Product>
</Products>

VII-B - Amélioration du Support de XQuery

SQL Server 2008 intègre le support pour la clause « let » qui est utilisée pour assigner des valeurs aux variables dans une expression XQuery.

Exemple :

declare @XmlVar Xml
Set @XmlVar = '
<Facture>
<Facture>
<Client>Guy Gilbert</Client>
<Produits>
<Produit Id="1" Quantite="1" Prix="1.5">
<Produit Id="3" Quantite="2" Prix="2">
</Produits>
</Facture>
<Facture>
<Client>Kevin Brown</Client>
<Produits>
<Produit Id="1" Quantite="1" Prix="1.5">
<Produit Id="3" Quantite="2" Prix="2">
<Produit Id="4" Quantite="2" Prix="1.9">
</Produits>
</Facture>
<Facture>
<Client>Rob Walters</Client>
<Produits>
<Produit Id="1" Quantite="1" Prix="1.5">
</Produits>
</Facture>
</Facture>'
Select @XmlVar.query(
'<Ordres>
{
for $Facture in /Factures/Facture
let $count := count($Facture/Produits/Produit)
order by $count
return
<Ordre>
{$Facture/Client}
<NbProduit>{$count}</NbProduit>
</Ordre>
}
</Ordres>')

L'exemple ci-dessous retourne le résultat suivant :

<Ordres>
<Ordre>
<Client>Rob Walters<Client>
<NbProduit>1</NbProduit>
</Ordre>
<Ordre>
<Client>Guy Gilbert<Client>
<NbProduit>2</NbProduit>
</Ordre>
<Ordre>
<Client>Kevin Brown<Client>
<NbProduit>3</NbProduit>
</Ordre>
</Ordres>

VIII - Conclusion

Cet article vous a présenté les fonctionnalités introduites dans SQL Server depuis la version 2000 jusqu'à la version actuelle pour la prise en charge du XML au sein du moteur de données de SQL Server. Vous pouvez désormais générer des données XML à partir de données relationnelles présentes dans votre base de données, stocker directement vos données XML sous leur forme native, les interroger et y appliquer avec souplesse des modifications.

IX - Remerciements

Je tiens à remercier mikedavem et ced pour leurs relectures et remarques pour l'amélioration de cet article.