Créer des variables et des colonnes de type de données XML

  • Article

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Le type de données xml est un type de données intégré dans SQL Server et est un peu similaire à d’autres types intégrés tels que int et varchar. À l’image des autres types intégrés, vous pouvez utiliser le type de données xml comme type de colonne lorsque vous créez une table en tant que type de variable, de paramètre, de retour de fonction ou dans CAST et CONVERT.

Créer des colonnes et des variables

Pour créer une colonne de type xml dans le cadre d’une table, utilisez une instruction CREATE TABLE , comme illustré dans l’exemple suivant :

CREATE TABLE T1(Col1 int primary key, Col2 xml);

Vous pouvez utiliser une DECLARE statement pour créer une variable de type xml , comme le montre l’exemple suivant.

DECLARE @x xml;

Créez une variable xml typée en spécifiant une collection de schémas XML, comme illustré dans l'exemple suivant.

DECLARE @x xml (Sales.StoreSurveySchemaCollection)

Pour passer un paramètre de type xml à une procédure stockée, utilisez une instruction CREATE PROCEDURE , comme illustré dans l’exemple suivant.

CREATE PROCEDURE SampleProc(@XmlDoc xml) AS ...

Vous pouvez utiliser XQuery pour interroger des instances XML stockées dans des colonnes, paramètres ou variables. Vous pouvez également utiliser le langage de manipulation de données XML pour appliquer des mises à jour aux instances XML. Étant donné que la norme XQuery n’a pas défini XQuery DML au moment du développement, SQL Server introduit des extensions de langage de modification de données XML dans XQuery. Ces extensions vous permettent de réaliser des opérations d'insertion, de mise à jour et de suppression.

Affectation des valeurs par défaut

Dans une table, vous pouvez affecter une instance XML par défaut à une colonne de type xml . Vous pouvez fournir le XML par défaut de deux manières : en utilisant une constante XML ou en utilisant un cast explicite au type xml .

Pour fournir le XML par défaut en tant que constante XML, utilisez la syntaxe comme illustré dans l'exemple suivant. La chaîne est implicitement CAST en type xml .

CREATE TABLE T (XmlColumn xml default N'<element1/><element2/>')

Pour fournir le XML par défaut en tant que CAST explicite au type xml, utilisez la syntaxe comme illustré dans l'exemple suivant.

CREATE TABLE T (XmlColumn xml
                  default CAST(N'<element1/><element2/>' AS xml))

SQL Server prend également en charge les contraintes NULL et NOT NULL sur les colonnes de type xml . Par exemple :

CREATE TABLE T (XmlColumn xml NOT NULL)

Spécifier des contraintes

Quand vous créez des colonnes de type xml , vous pouvez définir des contraintes de niveau colonne ou de niveau table. Utilisez les contraintes dans les cas suivants :

  • Vos règles métier ne peuvent pas être exprimées dans des schémas XML. Par exemple, l'adresse de livraison d'un fleuriste doit se trouver à 80 km du magasin. Cela peut faire l'objet d'une contrainte dans la colonne XML. La contrainte peut impliquer des méthodes de type de données xml .

  • Votre contrainte implique d'autres colonnes XML ou non XML de la table. Vous pourriez, par exemple, vouloir absolument que l'ID d'un client (/Customer/@CustId) figurant dans une instance XML corresponde à la valeur d'une colonne relationnelle CustomerID.

Vous pouvez spécifier des contraintes pour les colonnes de type de données xml typées ou non typées. Toutefois, vous ne pouvez pas utiliser les méthodes de type de données XML lorsque vous spécifiez des contraintes.

Le type de données xml ne prend pas en charge les contraintes de colonne et de table suivantes :

  • PRIMARY KEY/ FOREIGN KEY

  • UNIQUE

  • COLLATE

    XML fournit son propre encodage. Les classements ne s'appliquent qu'aux types chaîne. Le type de données xml n’est pas un type de chaîne. Toutefois, il possède une représentation chaîne et permet la conversion en provenance et en direction des types de données chaîne.

  • RULE

Une alternative à l’utilisation de contraintes consiste à créer une fonction wrapper définie par l’utilisateur permettant d’inclure la méthode de type de données xml et à spécifier cette fonction dans la contrainte de validation, comme le montre l’exemple ci-dessous.

Dans l'exemple suivant, la contrainte sur Col2 spécifie que chaque instance XML stockée dans cette colonne doit posséder un élément <ProductDescription> doté d'un attribut ProductID . Cette contrainte est appliquée par cette fonction définie par l'utilisateur :

CREATE FUNCTION my_udf(@var xml) returns bit
AS BEGIN
RETURN @var.exist('/ProductDescription/@ProductID')
END;
GO

La exist() méthode du type de données xml retourne 1 si l’élément <ProductDescription> de l’instance contient l’attribut ProductID . Sinon, 0est retourné.

Maintenant, vous pouvez créer une table dotée d'une contrainte de niveau colonne, comme suit :

CREATE TABLE T (
    Col1 int primary key,
    Col2 xml check(dbo.my_udf(Col2) = 1));
GO

L'insertion suivante réussit :

INSERT INTO T values(1,'<ProductDescription ProductID="1" />');

La contrainte fait échouer l'insertion suivante :

INSERT INTO T values(1,'<Product />');

Même table ou autre

Une colonne de type xml peut être créée dans une table qui contient d’autres colonnes relationnelles ou dans une table distincte dotée d’une relation de clé étrangère avec une table principale.

Créez une colonne de type xml dans la même table si l’une des conditions suivantes est remplie :

  • Votre application effectue une récupération de données sur la colonne XML et ne nécessite pas d’index XML sur la colonne XML.

  • Vous voulez créer un index XML sur la colonne de type xml et la clé primaire de la table principale est identique à sa clé de clustering. Pour plus d’informations, consultez Index XML (SQL Server).

Créez la colonne de type xml dans une table distincte si les conditions suivantes sont remplies :

  • Vous souhaitez créer un index XML sur la colonne de type de données XML , mais la clé primaire de la table principale est différente de sa clé de clustering, ou la table principale n’a pas de clé primaire, ou la table principale est un tas (aucune clé de clustering). Cela peut se produire si la table principale existe déjà.

  • Vous ne souhaitez pas que les analyses de table ralentissent en raison de la présence de la colonne XML dans la table. La quantité d'espace utilisée varie selon que le stockage se fait en ligne ou hors ligne.