OPENXML (SQL Server)

OPENXML, un mot clé Transact-SQL, fournit un ensemble de lignes sur des documents XML en mémoire similaires à une table ou à une vue. OPENXML autorise l’accès aux données XML comme s’il s’agit d’un ensemble de lignes relationnel. Pour ce faire, il fournit une vue d’ensemble de lignes de la représentation interne d’un document XML. Les enregistrements de l’ensemble de lignes peuvent être stockés dans des tables de base de données.

OPENXML peut être utilisé dans les instructions SELECT et SELECT INTO où les fournisseurs d’ensembles de lignes, une vue ou OPENROWSET peuvent apparaître comme source. Pour plus d’informations sur la syntaxe d’OPENXML, consultez OPENXML (Transact-SQL).

Pour écrire des requêtes sur un document XML à l’aide d’OPENXML, vous devez d’abord appeler sp_xml_preparedocument. Cela analyse le document XML et retourne un handle au document analysé prêt à être consommé. Le document XML analysé est une représentation en arborescence du modèle d'objet de document (DOM) des différents nœuds. Le handle de document est transmis à OPENXML. OPENXML fournit ensuite une vue d’ensemble de lignes du document, en fonction des paramètres passés à celui-ci.

La représentation interne d’un document XML doit être supprimée de la mémoire en appelant la procédure stockée système sp_xml_removedocument pour libérer la mémoire.

L’illustration suivante présente le processus.

Notez que pour comprendre OPENXML, la connaissance des requêtes XPath et une compréhension du code XML est requise. Pour plus d’informations sur la prise en charge de XPath dans SQL Server, consultez Utilisation de requêtes XPath dans SQLXML 4.0.

Exemple :

L’exemple suivant montre l’utilisation de OPENXML dans une instruction INSERT et une instruction SELECT. L’exemple de document XML contient les éléments <Customers> et <Orders>.

Tout d’abord, la sp_xml_preparedocument procédure stockée analyse le document XML. Le document analysé est une arborescence des nœuds (éléments, attributs, texte et commentaires) dans le document XML. OPENXML fait ensuite référence à ce document XML analysé et fournit une vue d’ensemble de lignes de toutes ou parties de ce document XML. Une INSERT instruction utilisant OPENXML peut insérer des données d’un ensemble de lignes de ce type dans une table de base de données. Plusieurs OPENXML appels peuvent être utilisés pour fournir une vue d’ensemble de lignes de différentes parties du document XML et les traiter, par exemple, en les insérant dans différentes tables. Ce processus est également appelé déchiquetage XML dans des tables.

Dans l’exemple suivant, un document XML est déchiqueté de manière à ce que les éléments <Customers> soient stockés dans la table Customers et que les éléments <Orders> soient stockés dans la table Orders en utilisant deux instructions INSERT. L’exemple montre également une SELECT instruction avec OPENXML qui récupère CustomerID et OrderDate à partir du document XML. La dernière étape du processus consiste à appeler sp_xml_removedocument. Cette opération est effectuée pour libérer la mémoire allouée pour contenir la représentation d’arborescence XML interne qui a été créée pendant la phase d’analyse.

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20), 
                CompanyName varchar(20));
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max); -- or xml type
SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>';
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@docHandle, N'/ROOT/Customers') 
  WITH Customers;
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@docHandle, N'//Orders') 
  WITH Orders;
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle; 

L’illustration suivante montre l’arborescence XML analysée du document XML précédent créé à l’aide de sp_xml_preparedocument.

Paramètres OPENXML

Les paramètres d’OPENXML sont les suivants :

• Handle de document XML (idoc)

• Expression XPath pour identifier les nœuds à mapper aux lignes (rowpattern)

• Description de l’ensemble de lignes à générer

• Mappage entre les colonnes d’ensemble de lignes et les nœuds XML

Gestionnaire de document XML (idoc)

L'identifiant de document est retourné par la sp_xml_preparedocument procédure d'emmagasinage.

Expression XPath pour identifier les nœuds à traiter (rowpattern)

L’expression XPath spécifiée en tant que rowpattern identifie un ensemble de nœuds dans le document XML. Chaque nœud identifié par rowpattern correspond à une seule ligne de l’ensemble de lignes généré par OPENXML.

Les nœuds identifiés par l’expression XPath peuvent être n’importe quel nœud XML dans le document XML. Si rowpattern identifie un ensemble d’éléments dans le document XML, il existe une ligne dans l’ensemble de lignes pour chaque nœud d’élément identifié. Par exemple, si rowpattern se termine par un attribut, une ligne est créée pour chaque nœud d’attribut sélectionné par rowpattern.

Description de l’ensemble de lignes à générer

Un schéma d’ensemble de lignes est utilisé par OPENXML pour générer l’ensemble de lignes résultant. Vous pouvez utiliser les options suivantes lors de la spécification d’un schéma d’ensemble de lignes.

Utilisation du format de tableau edge

Vous devez utiliser le format de tableau de bord pour spécifier un schéma d’ensemble de lignes. N’utilisez pas la clause WITH.

Lorsque vous effectuez cette opération, OPENXML retourne un ensemble de lignes au format de tableau de bord. Il s’agit d’une table de bord, car chaque bord de l’arborescence de documents XML analysée est mappé à une ligne de l’ensemble de lignes.

Les tables edge représentent dans une seule table la structure de document XML affinée. Cette structure inclut les noms d’éléments et d’attributs, la hiérarchie de documents, les espaces de noms et les instructions de traitement. Le format de tableau de bord vous permet d’obtenir des informations supplémentaires qui ne sont pas exposées par le biais des métapropriétés. Pour plus d’informations sur les métapropriétés, consultez Spécifier des métapropriétés dans OPENXML.

Les informations supplémentaires fournies par une table de périphérie vous permettent de stocker et d’interroger le type de données d’un élément et d’un attribut, ainsi que le type de nœud, ainsi que de stocker et d’interroger des informations sur la structure de document XML. Avec ces informations supplémentaires, il peut également être possible de créer votre propre système de gestion des documents XML.

À l’aide d’une table de périphérie, vous pouvez écrire des procédures stockées qui prennent des documents XML en entrée blob (binary large object), produisent la table de bord, puis extrayez et analysez le document à un niveau plus détaillé. Ce niveau détaillé peut inclure la recherche de la hiérarchie de documents, des noms d’éléments et d’attributs, des espaces de noms et des instructions de traitement.

La table de bord peut également servir de format de stockage pour les documents XML lorsque le mappage à d’autres formats relationnels n’est pas logique et qu’un champ ntext ne fournit pas suffisamment d’informations structurelles.

Dans les situations où vous pouvez utiliser un analyseur XML pour examiner un document XML, vous pouvez utiliser une table de bord à la place pour obtenir les mêmes informations.

Le tableau suivant décrit la structure de la table d'arêtes.

Utilisation de la clause WITH pour spécifier une table existante

Vous pouvez utiliser la clause WITH pour spécifier le nom d’une table existante. Pour ce faire, spécifiez simplement un nom de table existant dont le schéma peut être utilisé par OPENXML pour générer l’ensemble de lignes.

Utilisation de la clause WITH pour spécifier un schéma

Vous pouvez utiliser la clause WITH pour spécifier un schéma complet. Lors de la spécification du schéma d’ensemble de lignes, vous spécifiez les noms des colonnes, leurs types de données et leur mappage au document XML.

Vous pouvez spécifier le modèle de colonne à l’aide du paramètre ColPattern dans schemaDeclaration. Le modèle de colonne spécifié est utilisé pour mapper une colonne d’ensemble de lignes au nœud XML identifié par rowpattern et est également utilisé pour déterminer le type de mappage.

Si ColPattern n’est pas spécifié pour une colonne, la colonne d’ensemble de lignes est mappée au nœud XML portant le même nom, en fonction du mappage spécifié par le paramètre d’indicateurs . Toutefois, si ColPattern est spécifié dans le cadre de la spécification de schéma dans la clause WITH, il remplace le mappage spécifié dans le paramètre d’indicateurs .

Mappage entre les colonnes d’ensemble de lignes et les nœuds XML

Dans l’instruction OPENXML, vous pouvez éventuellement spécifier le type de mappage, tel que centré sur les attributs ou centré sur les éléments, entre les colonnes d’ensemble de lignes et les nœuds XML identifiés par le rowpattern. Ces informations sont utilisées dans la transformation entre les nœuds XML et les colonnes d’ensemble de lignes.

Vous pouvez spécifier le mappage de deux façons, et vous pouvez également spécifier les deux :

• À l’aide du paramètre indicateurs

  Le mappage spécifié par le paramètre d’indicateurs suppose la correspondance de nom dans laquelle les nœuds XML correspondent aux colonnes d’ensemble de lignes correspondantes portant le même nom.

• À l’aide du paramètre ColPattern

  ColPattern, une expression XPath, est spécifié dans le cadre de SchemaDeclaration dans la clause WITH. Le mappage spécifié dans ColPattern remplace le mappage spécifié par le paramètre d’indicateurs .

  ColPattern peut être utilisé pour spécifier le type de mappage, tel que centré sur les attributs ou centré sur les éléments, qui remplace ou améliore le mappage par défaut indiqué par les indicateurs.

  ColPattern est spécifié dans les circonstances suivantes :

  • Le nom de colonne dans l’ensemble de lignes est différent du nom de l’élément ou de l’attribut auquel il est mappé. Dans ce cas, ColPattern est utilisé pour identifier l’élément XML et le nom d’attribut auquel la colonne d’ensemble de lignes est mappée.

  • Vous souhaitez mapper un attribut métaproperty à la colonne. Dans ce cas, ColPattern est utilisé pour identifier la métapropriété à laquelle la colonne d’ensemble de lignes est mappée. Pour plus d’informations sur l’utilisation des métapropriétés, consultez Spécifier des métapropriétés dans OPENXML.

Les indicateurs et les paramètres ColPattern sont facultatifs. Si aucun mappage n’est spécifié, le mappage centré sur les attributs est supposé. Le mappage centré sur les attributs est la valeur par défaut du paramètre d’indicateurs.

Mappage centré sur les attributs

La définition du paramètre d’indicateurs dans OPENXML sur 1 (XML_ATTRIBUTES) spécifie le mappage centré sur les attributs . Si indicateurs contiennent XML_ATTRIBUTES, le jeu de lignes exposé fournit ou consomme des lignes où chaque élément XML est représenté comme une ligne. Les attributs XML sont associés aux attributs définis dans la SchemaDeclaration ou fournis par le nom de table de la clause WITH, selon la correspondance des noms. La correspondance de nom signifie que les attributs XML d’un nom particulier sont stockés dans une colonne de l’ensemble de lignes portant le même nom.

Si le nom de colonne est différent du nom d’attribut auquel il est mappé, ColPattern doit être spécifié.

Si l’attribut XML a un qualificateur d’espace de noms, le nom de colonne dans l’ensemble de lignes doit également avoir le qualificateur.

Mappage centré sur les éléments

La définition du paramètre d’indicateurs dans OPENXML sur 2 (XML_ELEMENTS) spécifie le mappage centré sur les éléments . Il est similaire au mappage centré sur les attributs , à l’exception des différences suivantes :

• La correspondance de nom de l’exemple de mappage, un mappage de colonne à un élément XML portant le même nom choisit les sous-éléments noncomplex, sauf si un modèle au niveau des colonnes est spécifié. Dans le processus de récupération, si le sous-élément est complexe, car il contient des sous-éléments supplémentaires, la colonne a la valeur NULL. Les valeurs d’attribut des sous-éléments sont ensuite ignorées.

• Pour plusieurs sous-éléments portant le même nom, le premier nœud est retourné.

Voir aussi

sp_xml_preparedocument (Transact-SQL)sp_xml_removedocument (Transact-SQL)OPENXML (Transact-SQL)XML Data (SQL Server)