SQLXML 4 et la prise en charge des classes managées SQLXML dans le Framework .NET

Hinault Romaric DONFACK

Ce tutoriel a pour but de vous faire découvrir l'outil SQLXML, les améliorations qu'il apporte pour le traitement côté client du format XML pris en charge dans le moteur de base de données SQL Server. Vous verrez également comment utiliser les classes managées SQLXML pour le traitement avec souplesse dans une application .NET du type de données XML dans SQL Server.
Commentez

- Titre : SQLXML 4 et la prise en charge des classes managées SQLXML dans le Framework .NET

- Auteur : Hinault Romaric DONFACK

- Parution : 6 juillet 2011

- 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 ®  Hinault Romaric. 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. Droits de diffusion permanents accordés à Developpez LLC.

Sommaire

- I. Prérequis

- II. Introduction

- III. Description de SQLXML

- III-A. Quelques fonctionnalités offertes par SQLXML

- IV. XML et SQL Server

- IV-A. SQLXML : Les clauses FOR XML

- IV-A-1. Arguments optionnels

- IV-A-2. Arguments obligatoires

- IV-A-2-a. FOR XML RAW

- IV-A-2-b. FOR XML AUTO

- IV-A-2-c. FOR XML EXPLICIT

- IV-A-2-C. FOR XML PATH

- IV-B. SQXML : le type de données XML

- IV-B-1. Limites des types de données XML

- V. Accès client aux données XML

- VI. Manipulation du XML côté client avec les classes managées SQLXML 4.0 (Prise en charge des classes managées SQLXML dans le Framework .NET).

- VI-A. SQLXML et ADO.NET

- VI-B. Les classes managées SQLXML

- VI-B-1. L'objet SqlXmlCommand

- VI-B-1-a. Propriétés

- VI-B-1-b. Méthodes

- VI-B-1-c. Utilisation de SqlXmlCommand

- VI-B-2. L'objet SqlXmlParameter

- VI-B-2-a. Utilisation de SqlXmlParameter

- VI-B-3. L'objet SqlXmlAdapter

- VI-B-3-a. Utilisation de SqlXmlAdapter

- VI-C. Modification des données avec Xpath

- VI-D. Mise à jour des données avec UpdateGram

- VI-E. Utilisation des modèles SQLXML

- VII. Conclusion

- VIII. Liens

- IX. Remerciements

I - Prérequis

Pour une bonne compréhension de cet article, vous devez avoir des connaissances de base sur le langage XML, le langage de requête Xpath, sur le SGBD SQL Server et également en C#.

Si ce n'est pas le cas, je vous conseille dans un premier temps de lire mes articles sur :

- La prise en charge du XML dans SQL Server ;

- L'utilisation de la colonne de type XML dans SQL Server 2005 avec ADO.NET.

II - Introduction

Le XML est très utilisé comme format d'échange de données entre plateformes différentes. Depuis la version 2000 du système de gestion de base de données SQL Server, Microsoft offre une prise en charge du format XML avec notamment l'introduction de la clause For XML.

Avec les versions 2005 et 2008 de SQL Server, la prise en charge du XML a été beaucoup plus accentuée avec notamment l'introduction de la colonne de type XML et la prise en charge de XQuery. Des données XML peuvent désormais être stockées dans une base de données de façon native dans une colonne de type XML.

Pour permettre aux DBA et aux développeurs d'exploiter avec souplesse les données XML dans SQL Server, Microsoft a publié SQLXML, un composant qui offre des fonctionnalités XML supplémentaires côté client, et améliore les fonctionnalités existantes telles que le chargement XML en masse, les schémas XSD annotés, etc.

Pour terminer la série d'articles que j'ai rédigés sur la prise en charge du XML dans SQL Server et le traitement des données XML côté client avec le framework .NET, je vous invite à découvrir dans cet article les fonctionnalités de SQXML, ainsi que les différentes possibilités qu'offre le composant pour le traitement des données XML côté client dans une application .NET.

III - Description de SQLXML

SQXML a été publié avec la version 2000 de SQL Server. SQL Server 2005 a introduit SQLXML 4.0 qui offre des mises à jour supplémentaires afin de gérer les nouvelles fonctionnalités introduites par cette version du SGBD. Avec SQL Server 2008, le service Pack 1 de ce composant a été publié pour la prise en charge des améliorations XML dans SQL Server 2008.

Le composant est disponible gratuitement et est téléchargeable indépendamment.

III-A - Quelques fonctionnalités offertes par SQLXML

Le composant SQLXML offre une multitude de fonctionnalités permettant de traiter facilement et avec souplesse les données XML. Parmi ces fonctionnalités nous pouvons citer entre autres :

- prise en charge de SQL Server Native Client : SQLXML 4.0 prend en charge les fournisseurs OLEDB et SQL Server native client ;

- prise en charge des données introduites dans SQL Server 2005 - 2008 : SQLXML 4.0 prend en charge le nouveau type de données XML introduit par SQL Server 2005 ainsi que les types Date, Time, DateTime2 et DateTimeOffset introduits pas SQL Server 2008 qui seront activés par celui-ci sous la forme de types scalaires intégrés lors d'une utilisation avec le fournisseur OLE DB SQL Server 2008 Native Client ;

- SQLXML 4.0 et MSXML : SQLXML 4.0 n'installe pas MSXML. SQLXML 4.0 utilise MSXML 6.0, qui est installé avec SQL Server 2005 ou SQL Server 2008 ;

- prise en charge de l'interface ISAPI SQLXML 3.0 et des types de données introduits dans SQL Server 2005 : la prise en charge de l'interface ISAPI SQLXML 3.0 a été supprimée dans SQXML4.0. Dans le cas où votre solution nécessite des fonctionnalités améliorées de typage de données introduites par SQL Sever 2005, notamment le type de données XML, vous devez utiliser une autre solution comme par exemple les classes managées SQLXML ;

- migration pour FOR XML côté client : en raison des modifications apportées au moteur d'exécution, SQL Server 2005 et SQL Server 2008 peuvent retourner des valeurs dans les métadonnées pour une table de base différente de celle retournée par la requête FOR XML exécutée sous SQL Server 2000. Si cela se produit, la mise en forme côté client des résultats de la requête FOR XML aura une sortie différente selon la version sur laquelle la requête est exécutée ;

- les classes managées SQLXML : SQLXML 4.0 offre les classes managées SQLXML pour le traitement facile des données XML dans une application .NET.

Si une requête FOR XML est exécutée côté client à l'aide de SQLXML 3.0 sur une colonne de type de données XML, les données dans les résultats reviendront sous la forme d'une chaîne entièrement décomposée. Dans SQLXML 4.0, si SQL Server Native Client (SQLNCLI10) est spécifié en tant que fournisseur, les données seront retournées sous forme de données XML.

IV - XML et SQL Server

Dans cette session, nous allons effectuer une brève présentation de la prise en charge du XML dans SQL Server.

IV-A - SQLXML : Les clauses 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') ] ]

IV-A-1 - 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.

IV-A-2 - 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.

IV-A-2-a - 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" />

IV-A-2-b - 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 attributs 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.

IV-A-2-c - 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>

IV-A-2-C - 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-B - SQXML : le type de données XML

Beaucoup de développeurs choisissent le plus souvent de stocker leurs données au format XML directement dans des colonnes de type VarChar ou Text, cette approche présente plusieurs inconvénients tels que :

- la non-validation des données ;

- l'incapacité des requêtes sur des nœuds spécifiques ;

- la modification des valeurs spécifiques ;

- le stockage des données qui ne respecte pas le schéma spécifié.

En gardant tout cela à l'esprit, SQL Server 2005 introduit un type de données natif appelé XML de première classe, tout comme INT ou VARCHAR. 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. L'utilisation peut être faite comme avec n'importe quel type de données standard de SQL Server : type de colonnes, variables ou paramètres dans une fonction ou procédure stockée.

Vous pouvez créer une table comportant une colonne à l'aide de l'instruction usuelle CREATE TABLE

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

Une table peut être également créée avec une ou plusieurs colonnes XML et avec ou sans clé primaire.

Si dans une collection de schémas XML qui décrit vos données, vous disposez de schémas de ce type, vous pouvez associer la collection de schémas à 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 MyCollection AS
N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema elementFormDefault="unqualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="SomeElement">
<xsd:complexType mixed="false">
<xsd:sequence>
<xsd:element name="ChildElement"
type="xsd:string"/>
<xsd:element name="SecondChildElement"
type="xsd:string"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>';

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

CREATE TABLE XmlTable (pk INT PRIMARY KEY, xCol XML(CONTENT MyCollection))

Bien que le type de données XML soit un type de données de haut niveau, 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.

IV-B-1 - Limites des types de données XML

Les types de données XML présentent les 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 XML impossibles) ;

- ils ne peuvent pas être utilisés en tant que paramètres 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 KE, UNIQUE, COLLATE.

V - Accès client aux données XML

Les clients peuvent accéder aux données XML dans le serveur de plusieurs manières.

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

2. 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é.

3. 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.

4. 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.

Nous verrons dans cet article le traitement côté client avec les classes managées SQLXML.

VI - Manipulation du XML côté client avec les classes managées SQLXML 4.0 (Prise en charge des classes managées SQLXML dans le Framework .NET).

SQLXML 4.0 offre des fonctionnalités qui vous permettent d'écrire des applications pour accéder aux données XML d'une instance de SQL Server, intégrer ces données à l'environnement Microsoft .NET, les traiter et renvoyer les mises à jour à SQL Server.

Les classes managées SQLXML exposent les fonctionnalités de SQLXML 4.0 dans le framework .NET. Avec les classes managées SQLXML, vous pouvez donc écrire une application C# ou VB.NET permettant d'accéder aux données XML à partir d'une instance SQL Server.

Vous pouvez écrire des requêtes pour sélectionner, supprimer, mettre à jour les données en utilisant purement du XML, FOR XML, SQLXML Templates, Xpath, DiffGram (retour des mises à jour) et les transformations XSLT.

L'ensemble de ces fonctionnalités sont prises en charge dans le framework .NET au sein de l'espace de nom : Microsoft.Data.SqlXml.

VI-A - SQLXML et ADO.NET

Bien que ADO.NET dans sa version 2.0 a permis de charger les données en temps que XML et sérialiser leur contenu sous la forme d'une nouvelle classe SQLXML, il présente toujours des limites comparé à SQLXML :

- il n'est pas possible de faire la mise en forme XML côté client ;

- on ne peut pas charger un code XML avec un schéma trop complexe dans un DataSet ;

- impossible de faire des requêtes Xpath sur du contenu XML.

SQLXML, en somme, est un ensemble de composants qui ont été créés pour permettre aux développeurs de travailler avec souplesse et facilité sur du contenu XML dans SQL Server plutôt qu'avec ADO.NET.

VI-B - Les classes managées SQLXML

Les modèles objets des classes managées SQLXML sont mis en œuvre dans l'assembly Microsoft.Data.SqlXml(couramment sauvegarder dans le répertoire "C:\Program Files\SQLXML 4.0\bin").

Pour utiliser les classes managées SQLXML dans une application .NET, vous devez au préalable ajouter une référence à l'espace de nom Microsoft.Data.SqlXml.

Les classes qui sont disponibles sont les suivantes :

- SqlXmlCommand ;

- SqlXmlParameter ;

- SqlXmlAdapter.

VI-B-1 - L'objet SqlXmlCommand

L'objet SqlXmlCommand n'est pas trop différent de l'objet SqlCommand d'ADO.NET. Il permet de travailler avec SQLXML sur SQL Server. Toutefois, dans certains scénarios, la classe SqlXmlCommand est beaucoup plus utile.

La classe SqlXmlCommand a la capacité de retourner les données en tant que flux (ou dans un flux passé en paramètre) ou en tant qu'objet XmlReader. Les requêtes définies ou procédures stockées peuvent être utilisées conjointement avec des templates SQLXML, Xpath et les schémas annotés. Il est également possible d'appliquer des feuilles de style XSL directement à l'objet SqlXmlCommand pour effectuer des transformations.

SqlXmlCommand peut aussi être utilisé conjointement avec la classe SqlXmlParameter (presque de la même manière qu'avec ADO.NET).

SqlXmlCommand peut-être exploité pour pousser la charge de travail du serveur SQL vers le client. En effet, lorsque vous effectuez par exemple une requête For XML en utilisant la classe SqlCommand, le travail de transformation de la ligne sélectionnée mise au format XML est fait dans le processus SQL Server. Lorsque vous utilisez un objet SqlXmlCommand et For XML, vous avez la possibilité de dire que vous voulez que ce processus se produise dans la couche de données et donc, réduire ainsi la charge sur votre serveur de base de données.

VI-B-1-a - Propriétés

Les propriétés de l'objet SqlXmlCommand sont les suivantes :

Propriété

Définition

Base Path

Chemin d'accès de base (chemin d'accès à un répertoire). Cette propriété est utile pour résoudre un chemin d'accès relatif spécifié pour un fichier XSL (en utilisant la propriété XslPath).

ClientSideXml

Indique que la conversion de l'ensemble de lignes en XML doit se produire sur le client et non pas sur le serveur si sa valeur est « true », sinon la conversion est effectuée côté SQL Server.

CommandStream

Propriété permettant d'exécuter une commande à partir d'un fichier. Seules les valeurs DiffGram, UpdateGram, Templates du CommandType sont prises en compte en cas d'utilisation de CommandStream.

CommandText

Permet de spécifier le texte dans la commande à exécuter.

CommandType

Permet de définir le type de commandes parmi les valeurs du SqlXmlCommandType qui sont les suivantes :

Namespaces

Donne la possibilité d'exécuter des requêtes Xpath avec des espaces de noms.

OutputEncoding

Permet de spécifier le type de codage pour le flux de données retourné lorsque la commande est exécutée. La valeur par défaut est UTF 8.

RootTag

Permet de fournir l'élément racine unique du document XML généré lorsque la commande est exécutée.

SchemaPath

Permet de définir un schéma de mappage pour les requêtes XPath. Cette propriété est similaire à la propriété XslPath.

XslPath

Permet de spécifier le chemin d'accès absolu ou relatif au fichier XSL. Si le chemin d'accès est relatif, le chemin d'accès de base spécifié dans BasePath est utilisé pour résoudre le chemin d'accès relatif. Si aucun chemin d'accès de base n'est spécifié, le chemin d'accès relatif se rapporte au répertoire en cours.

VI-B-1-b - Méthodes

Les méthodes de l'objet SqlXmlCommand sont les suivantes :

Méthode

Définition

Void ClearParameters()

Efface tous les paramètres qui on été créés pour un objet de commande. Utile si l'on souhaite exécuter plusieurs requêtes avec le même objet Commande.

SqlXmlParameter CreateParameter()

Pour la création d'un paramètre SqlXmlParameter.

void ExecuteNonQuery()

Exécute la commande mais ne retourne rien. Utile pour les requêtes de modification et de suppression.

Stream ExecuteStream()

Exécute la commande et retourne le résultat sous la forme d'un objet Stream.

void ExecuteToStream(Stream outputStream)

Similaire à ExecuteStream(), à l'exception que le résultat est chargé dans un objet Stream passé en paramètre dans la procédure.

XmlReader ExecuteXmlReader()

Exécute la commande et retourne le résultat sous la forme d'un objet XmlReader.

VI-B-1-c - Utilisation de SqlXmlCommand

- Chaine de connexion SQLXML 4.0

Le composant SQLXML intègre son propre fournisseur de données OLE DB disponible dans le fichier Sqlxml4.dll.

La chaine de connexion pour un accès à SQL Server en utilisant les classes managées SQLXML est la suivante :

Provider=SQLNCLI;Data Source=Adresse_Serveur\NOM_INSTANCE; userid=USER_ID;password=PWD;Initial Catalog=NOM_BD;"

Dans cet exemple, nous allons sélectionner les données contenues dans la colonne de type XML Demographics de la table Sales.Store qui seront chargées dans un XmlReader. Le XmlReader sera retourné en utilisant la méthode ExecuteXmlReader de la classe SqlXmlCommand.

Pour tous les exemples, nous utiliserons la base de données AdventureWorks, qui est téléchargeable sur le site de Microsoft.

public void ReadXmlData(string ConnectionString)
{
DataSet Ds = new DataSet();
SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
SqlCmd.CommandType = SqlXmlCommandType.Sql;
SqlCmd.CommandText = "select Demographics from"
+ "Sales.Store WHERE CustomerID = 9";

XmlReader XmlR = SqlCmd.ExecuteXmlReader();
Ds.ReadXml(XmlR);

dataGridView1.DataSource = Ds.Tables[0];
}

VI-B-2 - L'objet SqlXmlParameter

Les classes managées SQLXML fournissent une classe de paramètre à savoir : SqlXmlParameter (pas très différent du SqlParameter d'ADO.NET). Cette classe est largement utilisée pour passer des paramètres aux procédures stockées ou aux requêtes paramétrées.

L'objet SqlXmlParameter prend en charge les propriétés suivantes :

- Name : nom du paramètre. Des paramètres peuvent être passés aux commandes. L'appel de la méthode CreateParameter de l'objet SqlXmlCommand crée l'objet SqlXmlParameter.

- Value : valeur du paramètre.

Il est à noter que la déclaration et l'utilisation d'un SqlXmlParametre diffèrent un tout petit peu de celles d'un SqlParameter.

La déclaration d'un SqlParameter se fait de la façon suivante :

SqlParameter Param = new SqlParameter();

SqlCmd.Parameters.Add(Param);

Tandis que celle d'un SqlXmlParameter se fait comme suit :

SqlXmlParameter Param;
Param = SqlXmlCmd.CreateParameter();
Param.Value = "Valeur";

SqlXmlCmd, représente ici l'objet SqlXmlCommand qui est utilisé.

VI-B-2-a - Utilisation de SqlXmlParameter

Dans l'exemple ci-dessous, nous allons utiliser une requête paramétrée pour lire les informations sur une personne dans la base AdventureWork.

public void ReadXmlData(string ConnectionString)
{
DataSet Ds = new DataSet();
SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
SqlCmd.RootTag = "Person";
SqlCmd.CommandType = SqlXmlCommandType.Sql;
SqlCmd.CommandText = "SELECT FirstName, MiddleName, LastName FROM Person.Contact"
+ "WHERE LastName=? FOR XML AUTO";
SqlXmlParameter Para;
Para = SqlCmd.CreateParameter();
Para.Value = "Jenkins";
XmlReader XmlR = SqlCmd.ExecuteXmlReader();
Ds.ReadXml(XmlR);
dataGridView1.DataSource = Ds.Tables[0];
}

La ligne de code "SqlCmd.RootTag = "Person"" permettra de bien formater le document XML retourné en l'encapsulant dans un nœud Person.

Les requêtes OLE DB utilisent le point d'interrogation ( ?) pour identifier l'emplacement des paramètres dans la requête.

VI-B-3 - L'objet SqlXmlAdapter

L'objet SqlXmlAdapter est utilisé pour charger des données dans un DataSet avec les résultats d'une requête, apporter des modifications à la base de données au moyen de XMLDiffGram, UpdateGram ou pour afficher des modifications sur la base de données lorsque le DataSet est mis à jour.

Il y'a trois constructions pour initialiser un SqlXmlAdapter à savoir :

- le premier prend un SqlXmlCommand comme argument.

SqlXmlAdapter(SqlXmlCommand SqlXmlCmd)

- le deuxième constructeur prend en paramètres un string spécifiant la requête, ensuite le type de commande spécifié dans le premier paramètre et enfin la chaine de connexion.

SqlXmlAdapter(
string commandText,
SqlXmlCommandType cmdType,
string connectionString
)

- le troisième constructeur prend en paramètres un flux représentant la commande, le type et enfin la chaine de connexion.

SqlXmlAdapter(
Stream commandStream,
SqlXmlCommandType cmdType,
string connectionString
)

Les méthodes prises en charge par cet objet sont les suivantes :

- void Fill(DataSet ds) : remplit le Dataset avec les données XML extraites de SQL Server ;

- void Update(DataSet ds) : applique des mises à jour aux enregistrements dans SQL Server à partir des données dans le Dataset.

VI-B-3-a - Utilisation de SqlXmlAdapter

Dans l'exemple ci-dessous, nous allons utiliser un schéma annoté XSD pour spécifier les données qui seront retournées, et Xpath pour requêter directement sur ces données XML.

Les annotations sont utilisées pour lier les attributs des données XML à des tables et colonnes dans une base de données relationnelle, et préciser les contraintes sur plusieurs tables dans un schéma XSD.

Le schéma annoté que nous utiliserons est le suivant :

<?xml version="1.0" encoding="utf-8" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Production.Product" sql:relation="Production.Product" >
<xsd:complexType>
<xsd:attribute name="Name"
sql:field="Name"
type="xsd:string" />
<xsd:attribute name="ProductNumber"
sql:field="ProductNumber"
type="xsd:string" />
<xsd:attribute name="Color"
sql:field="Color"
type="xsd:string" />
</xsd:complexType>
</xsd:element>
</xsd:schema>

Vous remarquerez par exemple l'annotation sql:relation="Production.Product" qui permet de faire la liaison avec la table de la base de données qui sera utilisée.

Le code C# que nous allons ensuite utiliser va permettre de requêter directement sur le contenu XML avec XPath, et afficher uniquement les produits dont la propriété Color est égale à " Black ".

DataSet Ds = new DataSet();
SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
SqlCmd.CommandText = "Production.Product[@Color='Black']";
SqlCmd.CommandType = SqlXmlCommandType.XPath;
SqlCmd.SchemaPath =  "Production.Product.xsd";
SqlXmlAdapter SqlAdap = new SqlXmlAdapter(SqlCmd);
SqlAdap.Fill(Ds);
dataGridView1.DataSource = Ds.Tables[0];

La ligne permettant de spécifier la requête Xpath est la suivante :

SqlCmd.CommandText = "Production.Product[@Color='Black']";

VI-C - Modification des données avec Xpath

Avec le même schéma annoté que nous avons défini dans la section précédente, nous allons maintenant utiliser Xpath pour faire des opérations de mise à jour sur nos données XML.

Le code C# de mise à jour qui permettra de modifier la colonne Name de la première ligne de données dans le DataSet est le suivant :

DataSet Ds = new DataSet();
SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
SqlCmd.CommandText = "Production.Product[@Color='Black']";
SqlCmd.CommandType = SqlXmlCommandType.XPath;
SqlCmd.SchemaPath = "Production.Product.xsd";
SqlCmd.ClientSideXml = true;
SqlCmd.RootTag = "Production.Product";
SqlXmlAdapter SqlAdap = new SqlXmlAdapter(SqlCmd);
SqlAdap.Fill(Ds);
Ds.Tables[0].Rows[1]["Name"] = "XKT Crankarm";
SqlAdap.Update(Ds);

Dans cet exemple, nous avons choisi de transférer la charge de travail sur le client en insérant la ligne de code suivante :

SqlCmd.ClientSideXml = true;

Ainsi, toutes les transformations XML seront effectuées côté client.

VI-D - Mise à jour des données avec UpdateGram

Le format DiffGram a fait son apparition dans le composant DataSat du Framework .NET.

Le UpdateGram utilise le format DiffGram pour modifier ou insérer des données dans une base de données SQL Server avec SQLXML.

L'utilisation des UpdateGrams s'avère très pratique dans des systèmes distribués et les applications qui ne peuvent pas utiliser ADO.NET, parce que l'on peut par exemple utiliser le protocole HTTP et un serveur Web pour faire des mises à jour d'une base de données SQL Server.

Un UpdateGram peut-être créé comme un message XML et envoyé à une application .NET, qui peut ensuite utiliser le composant SQLXML pour effectuer des mises à jour sur SQL Server.

Dans cette partie, sans toutefois entrer dans les détails sur l'UpdateGram ou le format DiffGram, nous verrons comment créer et utiliser un UpdateGram pour faire une mise à jour d'une table de la BD.

Le fichier XML du UpdateGram que nous allons utiliser est le suivant :

<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram" >
<updg:sync>
<updg:before>
<Production.Product
ProductID="317"
Name="LL Crankarm"
ProductNumber="CA-5965"/>
</updg:before>
<updg:after>
<Production.Product
Name="ndbdhgdhg"
ProductNumber="CA-4652"/>
</updg:after>
</updg:sync>
</ROOT>

Les données qui doivent être modifiées seront spécifiées dans le nœud "updg:before". Ces données doivent être définies comme attributs (le nom de l'attribut est celui de la colonne de la table) d'une balise ayant pour nom celui de la table de la base de données.

Les nouvelles valeurs doivent être spécifiées dans le nœud "updg:after".

Le code C# de mise à jour des données avec UpdateGram est le suivant :

SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
SqlCmd.RootTag = "ROOT";
SqlCmd.CommandStream = new FileStream("DiffGramProductSchema.xml", FileMode.Open, FileAccess.Read);
SqlCmd.CommandType = SqlXmlCommandType.UpdateGram;
SqlCmd.ExecuteNonQuery();

Les codes de mise à jour UpdateGram peuvent également être utilisés pour l'insertion des données dans des colonnes de type de XML.

VI-E - Utilisation des modèles SQLXML

Les modèles SQLXML sont des fichiers XML pouvant contenir des instructions SQL, des requêtes XPath et les déclarations des paramètres.

L'inclusion des requêtes SQL ou XPath dans des fichiers de modèles au lieu de les renseigner en dur dans le code, ou utiliser les procédures stockées, offre une meilleure flexibilité à l'application.

Cela permet également d'avoir une architecture plus souple, de réutiliser les formats de requêtes XML et d'utiliser le protocole HTTP pour des accès de façon sécurisée à la base de données.

Le fichier de modèle XML suivant permet de sélectionner des données dans la table Production.Product, en fonction de la valeur qui sera passée dans le paramètre Color.

<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>
<sql:header>
<sql:param name="Color"/>
</sql:header>
<sql:query>
Select Name, ProductNumber, Color FROM Production.Product
WHERE Name=@Color FOR XML AUTO
</sql:query>
</ROOT>

Notez que les paramètres doivent être contenus dans le nœud " sql:header ". La requête est quant à elle contenue dans le nœud " sql:query ".

L'exemple de code C# utilisant ce modèle est le suivant :

SqlXmlCommand SqlCmd = new SqlXmlCommand(ConnectionString);
SqlCmd.CommandStream = new FileStream("ProductTemplates.xml", FileMode.Open, FileAccess.Read);
SqlCmd.RootTag = "Product";
SqlCmd.CommandType = SqlXmlCommandType.Template;
//définition du paramètre
SqlXmlParameter Para;
Para = SqlCmd.CreateParameter();
Para.Name = "@Color";
Para.Value = "Black";
//Lecture des données
using(Stream st = SqlCmd.ExecuteStream()){
using(StreamReader sr = new StreamReader(st)){
Console.WriteLine(sr.ReadToEnd());
}

}

VII - Conclusion

Le composant SQLXML offre aux développeurs des moyens de traiter avec facilité et souplesse des données XML d'une base de données SQL Server, et permet la création des applications performantes. Nous vous avons présenté dans ce tutoriel comment exploiter une partie des fonctionnalités des classes managées SQXML pour effectuer des traitements des données XML côté client.

VIII - Liens

SQlXML 4 peut être téléchargé sur cette page

Prise en charge du XML dans SQL Server

Utilisation de la colonne de type XML dans SQL Server 2005 avec ADO.NET

IX - Remerciements

Je tiens à remercier ClaudeLELOUP pour sa relecture et correction orthographique.