type de données xml et colonnes (SQL Server)

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

Cet article présente les avantages et les limitations du type de données XML dans SQL Server et vous aide à choisir comment stocker des données XML.

Modèle de données relationnelle ou XML

Si vos données sont hautement structurées avec un schéma connu, le modèle relationnel est susceptible de fonctionner le mieux pour le stockage des données. SQL Server fournit les fonctionnalités requises et les outils dont vous avez besoin. En revanche, s'il s'agit de données semi-structurées ou non structurées, ou si la structure est inconnue, mieux vaut envisager la modélisation de ces données.

XML s'avère une solution de choix si vous souhaitez un modèle indépendant des plateformes pour assurer la portabilité des données à l'aide d'un balisage structurel et sémantique. De plus, cette solution semble mieux adaptée si certaines des conditions suivantes sont réunies :

• Vos données sont éparses ou vous ne connaissez pas la structure des données, ou la structure de vos données peut changer considérablement à l’avenir.

• Vos données représentent une hiérarchie d'imbrications, et non des références entre entités, et peuvent être récursives.

• L'ordre est inhérent dans vos données.

• Vous souhaitez interroger les données ou en mettre à jour certaines en fonction de leur structure.

Si aucune de ces conditions n'est remplie, il est préférable d'utiliser le modèle de données relationnel. Par exemple, si vos données sont au format XML, alors que votre application n’utilise la base de données que pour stocker et récupérer les données, une colonne [n]varchar(max) suffit. Le stockage des données dans une colonne XML apporte d'autres avantages : le moteur peut vérifier que les données sont bien formées ou valides, et vous pouvez lancer des requêtes et des mises à jour à granularité fine sur les données XML.

Raisons pour le stockage de données XML dans SQL Server

Voici quelques-unes des raisons d’utiliser des fonctionnalités XML natives dans SQL Server au lieu de gérer vos données XML dans le système de fichiers :

• Vous voulez partager, interroger et modifier vos données XML d'une manière efficace et transactionnelle. Votre application a besoin d'accéder aux données à un niveau très détaillé. Par exemple, vous souhaitez extraire quelques passages d'un document XML, ou insérer une nouvelle section, sans avoir à remplacer l'ensemble du document.

• Vous disposez de données relationnelles et de données XML et souhaitez garantir une parfaite interopérabilité entre elles au sein de votre application.

• Vous cherchez un langage permettant d'interroger et de modifier les données d'applications inter-domaines.

• Vous souhaitez que le serveur vérifie la bonne formation des données et valide éventuellement vos données par rapport à des schémas XML.

• Vous cherchez à indexer les données XML pour optimiser le traitement des requêtes et faciliter la montée en charge, et voulez profiter d'un optimiseur de requête de tout premier ordre.

• Vous voulez pouvoir accéder aux données XML par le biais de SOAP, ADO.NET et OLE DB.

• Vous voulez profiter des fonctions d'administration du serveur de base de données pour gérer vos données XML, en cas de sauvegarde, de récupération et de réplication par exemple.

Si aucune de ces conditions n’est remplie, vous avez intérêt à stocker vos données dans un type d’objet volumineux non-XML, tel que [n]varchar(max) ou varbinary(max).

Options de stockage XML

Les options de stockage pour XML dans SQL Server sont les suivantes :

• Stockage en mode natif dans le type de données xml

  Les données sont stockées dans une représentation interne qui conserve le contenu XML des données. Cette représentation interne inclut des informations à propos de la hiérarchie de relations contenant-contenu, l'ordre des documents et les valeurs d'éléments et d'attributs. Plus précisément, le contenu InfoSet des données XML est préservé. Pour plus d’informations sur InfoSet, consultez http://www.w3.org/TR/xml-infoset. Le contenu InfoSet peut ne pas être une copie identique du texte XML, car les informations suivantes ne sont pas conservées : espaces blancs non significatifs, ordre des attributs, préfixes d’espace de noms et déclaration XML.

  Pour un type de données xml typé, un type de données xml lié à des schémas XML, le jeu d’informations de validation post-schéma (PSVI) ajoute des informations de type dans le jeu d’informations et il est encodé dans la représentation interne. L'analyse s'en trouve considérablement accélérée. Pour plus d’informations, consultez les spécifications XML Schema de W3C sur http://www.w3.org/TR/xmlschema-1 et http://www.w3.org/TR/xmlschema-2.

• Mappage entre stockage XML et relationnel

  À l'aide d'un schéma annoté (AXSD), le code XML est décomposé en colonnes dans une ou plusieurs tables de façon à préserver la fidélité des données au niveau relationnel. La structure hiérarchique est ainsi conservée bien que l'ordre des éléments soit ignoré. Le schéma ne peut pas être récursif.

• Stockage d’objets volumineux, [n]varchar(max) et varbinary(max)

  Une copie conforme des données est stockée. Cela s'avère nécessaire pour des applications spécifiques telles que des documents juridiques. La plupart des applications ne nécessitent pas de copie exacte et sont satisfaites du contenu XML (fidélité InfoSet).

Dans la plupart des cas, vous aurez probablement à combiner ces deux approches. Vous pouvez, par exemple, stocker vos données XML dans une colonne de type de données xml et en promouvoir les propriétés dans des colonnes relationnelles. Vous pouvez également mapper les technologies pour stocker uniquement les parties récursives dans des colonnes de type de données xml et les parties non récursives dans des colonnes non-XML.

Choix de la technologie XML

Le choix de la technologie XML, mode XML natif ou vue XML, dépend généralement des facteurs suivants :

• Options de stockage

  Vos données XML sont peut-être plus adaptées à un stockage d'objet volumineux (manuel de produit, par exemple) ou à un stockage en colonnes relationnelles (article converti en XML, par exemple). Chaque option de stockage préserve la fidélité du document à sa manière.

• Interrogation des données par requête

  Vous trouverez peut-être qu'une option de stockage convient mieux qu'une autre en fonction de la nature de vos requêtes et de la façon dont vous interrogez vos données XML. Les requêtes à granularité fine sur des données XML, évaluation de prédicat sur des nœuds XML par exemple, ne sont prises en charge qu'à des degrés divers dans les deux options de stockage.

• Indexation des données XML

  Vous cherchez peut-être à indexer les données XML de manière à optimiser les performances des requêtes XML. Les options d'indexation varient avec les options de stockage. Il vous appartient de choisir la solution la mieux adaptée à l'optimisation de votre charge de travail.

• Modification des données

  Certaines charges de travail impliquent la modification des données XML à un degré de granularité assez fin. Par exemple, cela peut inclure l’ajout d’une nouvelle section dans un document, tandis que d’autres charges de travail, telles que le contenu web, ne le font pas. La prise en charge d'un langage de modification des données peut jouer un rôle essentiel pour votre application.

• Prise en charge des schémas

  Vos données XML peuvent être décrites par un schéma qui peut (ou non) être un document de schéma XML. La prise en charge de code XML associé à un schéma dépend de la technologie XML adoptée.

Qui dit choix différents, dit aussi performances différentes.

Stockage XML natif

Vous pouvez stocker vos données XML dans une colonne de type de données xml sur le serveur. C'est une solution de choix si vous vous trouvez dans les conditions suivantes :

• Vous cherchez un moyen simple de stocker vos données XML sur le serveur et voulez, en parallèle, préserver l'ordre et la structure du document.

• Vous disposez ou non d'un schéma pour vos données XML.

• Vous voulez pouvoir interroger et modifier vos données XML.

• Vous voulez indexer les données XML pour accélérer le traitement des requêtes.

• Votre application a besoin d'affichages catalogue système pour gérer vos données XML et vos schémas XML.

Le stockage XML natif est utile lorsque les documents XML présentent des structures différentes ou suivent des schémas différents ou complexes beaucoup trop difficiles à mapper à des structures relationnelles.

Exemple : Modéliser des données XML à l’aide du type de données XML

Prenons l'exemple d'un manuel de produit au format XML. Chaque rubrique fait l'objet d'un chapitre distinct, lui-même composé de plusieurs sections. Une section peut contenir des sous-sections. L’élément <section> est donc un élément récursif. Les manuels de produit regroupent un gros volume de données diverses : contenu, diagrammes, explications techniques ; les données sont semi-structurées. Les utilisateurs veulent pouvoir lancer une recherche contextuelle sur les rubriques qui les intéressent, par exemple rechercher la section consacrée aux « index cluster » dans le chapitre sur l'« indexation », et s'informer des quantités techniques.

Une colonne de type de données xml constitue un modèle de stockage approprié pour vos documents XML. Vous conservez ainsi le contenu InfoSet de vos données XML. L'indexation de la colonne XML permet d'optimiser les performances des requêtes.

Exemple : Conserver des copies exactes des données XML

Supposons, par exemple, que la législation en vigueur vous oblige à conserver des copies textuelles conformes de vos documents XML. Il pourrait s'agir notamment de documents signés, de documents juridiques ou d'ordres boursiers. Vous pouvez stocker vos documents dans une colonne [n]varchar(max) .

Pour l’interrogation, convertissez les données en type de données xml au moment de l’exécution et exécutez XQuery dessus. La conversion lors de l'exécution peut s'avérer onéreuse, surtout si le document est volumineux. Si vous exécutez fréquemment des requêtes, vous pouvez stocker les documents de façon redondante dans une colonne de type de données xml , puis indexer cette dernière quand vous retournez des copies conformes à partir de la colonne [n]varchar(max) .

La colonne XML peut être une colonne calculée, basée sur la colonne [n]varchar(max) . Toutefois, vous ne pouvez pas créer d’index XML sur une colonne XML calculée, ni un index XML ne peut-il être généré sur [n]varchar(max) ou sur des colonnes varbinary(max).

Technologie de vue XML

En définissant un mappage entre vos schémas XML et les tables d’une base de données, vous créez une vue XML de vos données persistantes. Le chargement en masse XML peut servir à remplir les tables sous-jacentes d'après la vue XML. Vous pouvez aussi interroger la vue XML en utilisant XPath version 1.0 ; la requête est traduite en requêtes SQL portant sur les tables. De même, les mises à jour peuvent se propager à ces tables.

Les vues XML sont utiles dans les cas suivants :

• Vous voulez avoir un modèle de programmation orienté XML en utilisant des vues XML des données relationnelles existantes.

• Vous avez un schéma (XSD, XDR) pour vos données XML qu'un partenaire externe vous a fourni.

• L’ordre n’est pas important dans vos données, ou vos données de table de requête ne sont pas récursives, ou la profondeur maximale de récursivité est connue à l’avance.

• Vous voulez interroger et modifier les données via la vue XML en utilisant XPath version 1.0.

• Vous voulez charger en masse les données XML, puis les répartir dans les tables sous-jacentes à l'aide de la vue XML.

Il pourrait s'agir de données relationnelles exposées au format XML pour l'échange des données et les services Web, et de données XML avec un schéma fixe. Pour plus d’informations, consultez

Exemple : Modéliser des données à l’aide d’un schéma XML annoté (AXSD)

Partons du principe que vous disposez de données relationnelles (clients, commandes et articles) et que vous voulez les gérer sous forme XML. Définissez une vue XML en utilisant AXSD sur les données relationnelles. La vue XML vous permet de charger en masse les données XML dans vos tables, puis d'interroger et de mettre à jour les données relationnelles à l'aide de la vue XML. Ce modèle s'avère très utile si vous avez à échanger des données contenant des balises XML avec d'autres applications alors que les applications SQL fonctionnent sans interruption.

Modèle hybride

Bien souvent, la modélisation des données fait intervenir à la fois des colonnes relationnelles et des colonnes de type xml . Certaines valeurs de vos données XML peuvent être stockées dans des colonnes relationnelles alors que le reste (ou la totalité) des valeurs XML sont stockées dans une colonne XML. Vous obtenez ainsi de meilleures performances puisque vous avez une meilleure maîtrise des index créés sur les colonnes relationnelles et sur les caractéristiques des verrous.

Les valeurs à stocker dans les colonnes relationnelles dépendent de votre charge de travail. Par exemple, si vous récupérez toutes les valeurs XML basées sur l’expression de chemin d’accès, /Customer/@CustIdpromouvoir la valeur de l’attribut CustId dans une colonne relationnelle et l’indexation qu’il peut générer des performances de requête plus rapides. En revanche, si vos données XML sont largement décomposées en colonnes relationnelles, le coût de réassemblage peut être important.

Pour des données XML très structurées, par exemple, le contenu d'une table a été converti en XML de façon à pouvoir mapper toutes les valeurs aux colonnes relationnelles, et éventuellement faire appel aux vues XML.

Granularité des données XML

La granularité des données XML stockées dans une colonne XML est importante pour le verrouillage et, dans une moindre mesure, il est également important pour les mises à jour. SQL Server utilise le même mécanisme de verrouillage pour les données XML et non XML. Par conséquent, le verrouillage au niveau de la ligne verrouille toutes les instances XML de la ligne. En cas de grosse granularité, le verrouillage des grosses instances XML pendant les mises à jour provoque une baisse de la capacité de traitement dans un scénario multi-utilisateur. En cas de décomposition fine, l'encapsulation des objets est perdue et le coût de réassemblage augmente.

Pour le bien de la conception, il est essentiel de trouver un juste équilibre entre les exigences de la modélisation des données et les critères de verrouillage et de mise à jour. Toutefois, dans SQL Server, la taille des instances XML stockées réelles n’est pas aussi critique.

Par exemple, les mises à jour d'une instance XML s'effectuent à l'aide d'une nouvelle prise en charge des mises à jour partielles d'objets blob et d'index au cours desquelles l'instance XML stockée est comparée à sa version mise à jour. En cas de mises à jour partielles d'objets blob, une comparaison différentielle des deux instances XML a lieu et seules les différences sont mises à jour. En cas de mises à jour partielles d'index, seules les lignes devant être changées dans l'index XML sont modifiées.

Limitations du type de données xml

Notez les limitations générales suivantes applicables au type de données xml :

• La représentation stockée des instances de type de données xml ne peut pas dépasser 2 Go.

• Il ne peut pas être utilisé comme sous-type d’une instance sql_variant .

• Elle ne prend pas en charge la conversion ou la conversion en texte ou ntext. Utilisez varchar(max) ou nvarchar(max) à la place.

• Il ne peut pas être comparé ou trié. Cela signifie qu’un type de données xml ne peut pas être utilisé dans une instruction GROUP BY.

• Il ne peut pas être utilisé comme paramètre pour toutes les fonctions scalaires intégrées autres que ISNULL, COALESCE et DATALENGTH.

• Il ne peut pas être utilisé comme colonne clé dans un index. En revanche, il peut être inclus en tant que donnée dans un index cluster ou ajouté explicitement à un index non-cluster à l'aide du mot clé INCLUDE lors de la création d'un index non-cluster.

• Les éléments XML peuvent avoir jusqu’à 128 niveaux d'imbrication.

Voir aussi

• Exemples d'importation et d'exportation en bloc de documents XML (SQL Server)