Prise en charge du XML dans SQL Server

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

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▲

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

<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" />

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')

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

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

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

III-B-3. FOR XML EXPLICIT▲

ElementName!TagNumber!AttributeName

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

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

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

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

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.

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.

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▲

CREATE PRIMARY XML INDEX 
Index_Name ON Table_Name (Column_Name)

CREATE PRIMARY XML INDEX 
Index_XmlTable ON XmlTable (XmlCol)

IV-C-2. Index secondaire▲

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

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

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

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.

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

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

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

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>

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

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 »▲

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

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

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

<PossibleSizeTypes> XL L M </ PossibleSizeTypes>

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

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

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>