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

- Connaissance du langage C#.

- Connaissance du SGBD SQL Server 2005.

- Connaissance du langage XML.

Le langage XML (eXtensible Markup Language) a été largement adopté en tant que format indépendant 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. L'une des principales caractéristiques de Microsoft SQL Server 2005 et de la version suivante est 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, elles peuvent être typées selon une collection de schémas XML (SQL Server permet également le stockage des collections de format XML dans la base de données), ou rester non typées.

Une fois que vos données XML sont stockées, vous pouvez alors y effectuer des opérations telles qu'insertion, modification ou sélection en passant par l'ADO.NET 2.0 XML indexation.

Cet article vous présente de la façon la plus simple possible comment utiliser la colonne de type de données XML dans SQL Server 2005 et comment l'interroger à partir d'ADO.NET.

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.

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.

Beaucoup de développeurs choisissent le plus de 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.

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 de 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 greffer à 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 offre une certaine souplesse dans la manipulation du XML.

Dans le cadre de cet article, nous nous limiterons uniquement aux traitements côté clients avec ADO.net.

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è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 KE, UNIQUE, COLLATE.

Maintenant que nous savons comment manipuler nos données côté base de données penchons nous sur le vif du sujet, à savoir comment accéder et manipuler les données avec ADO.net

VII-A. Ajout des données▲

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Data.SqlTypes;

    class TestInsertXmlData
    {

        static void InsertNewRow(string ConnectionString)
        {
            //Déclaration objet de connexion
            using (SqlConnection SqlCon = new SqlConnection(ConnectionString)) ;
            {
                //Ouverture de la connexion
                SqlCon.Open();
                //Déclaration de la commande
                SqlCommand SqlCmd = new SqlCommand("Insert into XmlTable"
                + "values(@XmlValue,)", SqlCon);
                //Création du paramètre 
                SqlCmd.Parameters.Add("@XmlValue", SqlDbType.Xml);
                //Récuperation de la valeur du paramètre dans un fichier
                XmlReader XmlR = XmlReader.Create("XmlFile.xml");
                //Affection de valeur du XmlR au paramètre 
                SqlCmd.Parameters["@XmlValue"].Value = SqlXml(XmlR);
                //exécution de la requête
                SqlCmd.ExecuteNonQuery();
                Console.WriteLine("Enregistrement effectuer");
            }
        }
    }

VII-B. Lecture des données▲

Une colonne de type XML peut être récupérée, tout comme n'importe quelle colonne (avec SqlDataReader, par exemple), mais si vous voulez travailler avec le contenu de la colonne au format XML vous devez utiliser un XmlDataReader.

La récupération des données peut donc s'effectuer de plusieurs façons et les exemples suivants illustrent chacune d'elles ; dans ces exemples on récupère une ligne contenant une colonne XML de la table Sales.Store de la base de données AdventureWorks. La base de données AdventureWorks n'est pas installée par défaut lorsque vous installez SQL Server 2005.

Exemple 1

La lecture peut être effectuée en utilisant ExecuteXmlReader() qui retourne un élément XML dans un XmlReader.

 

Sélectionnez

using System;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Data.SqlTypes;

    class TestXmlData
    {
        static void ReadXmlData(string ConnectionString )
        {
          using (SqlConnection SqlCon = new SqlConnection(ConnectionString))
          {
              //Ouverture de la connexion
             SqlCon.Open();
              //Déclaration de la commande
              SqlCommand SqlCmd = new SqlCommand("select Demographics from "
              + "Sales.Store WHERE CustomerID = 9", SqlCon);
             //Lecture des données
            XmlReader XmlR = SqlCmd.ExecuteXmlReader();
            XmlR.Read();
              //Affichage du résultat
            Console.WriteLine(XmlR.ReadOuterXml());
          }
        }
    }

Aperçu du résultat obtenu.

Exemple 2

On peut simplement lire le contenu de la colonne comme une chaîne en utilisant ExecuteReader().

 

Sélectionnez

static void ReadXmlData(string ConnectionString)
    {
       using(SqlConnection SqlCon = new SqlConnection(ConnectionString))
       {
           SqlCon.Open();
           SqlCommand SqlCmd = new SqlCommand("select Demographics from " 
              +"Sales.Store WHERE CustomerID = 9", SqlCon);
            SqlDataReader SqlRead = SqlCmd.ExecuteReader();
            SqlRead.Read();
            Console.WriteLine(SqlRead.GetString(0));
        }
    }

Exemple 3

La valeur de la colonne XML peut être lue en utilisant la méthode GetSqlXml de SqlDataReader. La valeur est stockée dans un XmlReader. Notez que vous devez utiliser GetSqlXml plutôt que la méthode GetValue si vous souhaitez définir le contenu d'une variable SqlXml. GetValue retourne la valeur de la colonne XML dans une chaîne.

 

Sélectionnez

static void ReadXmlData(string ConnectionString)
   {
       using (SqlConnection SqlCon = new SqlConnection(ConnectionString))
       {
           SqlCon.Open();
           SqlCommand SqlCmd = new SqlCommand("select Demographics from "
                  + "Sales.Store WHERE CustomerID = 9", SqlCon);
           SqlDataReader SqlRead = SqlCmd.ExecuteReader();
           SqlRead.Read();
           XmlReader XmlR = SqlRead.GetSqlXml(0).CreateReader();
           Console.WriteLine(XmlR.ReadOuterXml());
       }
   }

Exemple 4

Vous pourriez utiliser la méthode GetProviderSpecificValue().

 

Sélectionnez

static void ReadXmlData(string ConnectionString)
    {
       using(SqlConnection SqlCon = new SqlConnection(ConnectionString))
       {
           SqlCon.Open();
           SqlCommand SqlCmd = new SqlCommand("select Demographics from " 
              +"Sales.Store WHERE CustomerID = 9", SqlCon);
            SqlDataReader SqlRead = SqlCmd.ExecuteReader();
            SqlRead.Read();
            Object Ob = SqlRead.GetProviderSpecificValue(0);
            Console.WriteLine(Ob.ToString());
            Console.Read();
        }
    }

Ce qui est étonnant :

 

Sélectionnez

Console.WriteLine(Ob.GetType().ToString());

renvoie le type SqlString. Donc la valeur retournée par cette méthode est une chaîne et non un XmlReader.

ADO.net XML indexation présente les limites suivantes :

- 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 ;

- les requêtes directes sur le contenu d'une colonne de type XML pour la sélection des nœuds spécifiques (via Xpath par exemple) n'est pas possible.

De nos jours de plus en plus les développeurs, dans leurs applications, doivent faire cohabiter des données relationnelles avec des données XML. Dans cet article nous vous avons présenté la colonne de type XML introduite dans SQL server 2005 pour pallier ce problème et comment effectuer des traitements dans vos applications avec ADO.net 2.0 grâce notamment, aux nouvelles fonctionnalités introduites par celui-ci pour le traitement des données XML.

Cet article ne représente qu'une partie de l'iceberg, mais devrait vous permettre d'être plus productif, une fois que vous vous serez familiarisé avec le stockage XML natif dans Sql Server et avec le traitement des données côté client ; dans votre code, vous disposerez d'un riche ensemble de techniques, rendant la conception des applications utilisant les données XML et relationnelles plus simple.

Merci à Philippe Vialatte pour sa relecture et correction technique. Merci à Jacques Jean pour sa relecture et correction orthographique.Merci à toute l'équipe DotNet d’avoir autorisé la publication de cet article.