OPENXML (SQL Server)

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

OPENXML est un mot-clé Transact-SQL qui fournit un ensemble de lignes sur des documents XML en mémoire, similaire à une table ou à une vue. OPENXML permet l'accès aux données XML comme s'il s'agissait d'un ensemble de lignes relationnelles. Pour cela, il fournit une vue sous forme d'un 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 des instructions SELECT et SELECT INTO, partout où des fournisseurs d'ensemble 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 en utilisant OPENXML, vous devez commencer par appeler sp_xml_preparedocument. Cela permet d'analyser le document XML et de retourner un descripteur au document analysé qui est prêt à être utilisé. Le document analysé est une représentation sous forme d'arborescence de modèle DOM (document model) de différents nœuds dans le document XML. Ce descripteur de document est transmis à OPENXML. OPENXML fournit alors une vue sous forme d'ensemble de lignes du document, en fonction des paramètres transmis.

Pour libérer de la mémoire, vous devez supprimer la représentation interne d’un document XML de la mémoire en appelant la procédure stockée système sp_xml_removedocument .

L’illustration suivante présente le processus.

Notez que pour comprendre le fonctionnement de OPENXML, vous devez connaître les requêtes XPath et les données XML. Pour plus d’informations sur la prise en charge de XPath dans SQL Server, consultez Utilisation des requêtes XPath dans SQLXML 4.0.

Exemple

L'exemple ci-dessous montre l'utilisation de OPENXML dans une instruction INSERT et une instruction SELECT . L'exemple de document XML comprend les éléments <Customers> et <Orders> .

D'abord, la procédure stockée sp_xml_preparedocument analyse le document XML. Le document analysé est une représentation sous forme d'arborescence des différents nœuds du document XML : éléments, attributs, textes, commentaires, et ainsi de suite. OPENXML fait ensuite référence à ce document XML analysé et fournit une vue sous forme d'ensemble de lignes de tout ou partie de ce document XML. Une instruction INSERT utilisant OPENXML peut ainsi insérer des données dans une table de base de données à partir de ce type d'ensemble de lignes. Vous pouvez utiliser plusieurs appels à OPENXML pour fournir une vue d'un ensemble de lignes provenant de différentes parties du document XML puis traiter celles-ci en les insérant par exemple dans différentes tables. Cette opération est également qualifiée de fragmentation de données XML dans des tables.

Dans l'exemple suivant, un document XML est fragmenté de telle sorte que les éléments <Customers> sont stockés dans la table Customers et les éléments <Orders> sont stockés dans la table Orders à l'aide de deux instructions INSERT . Cet exemple illustre également une instruction SELECT associée à OPENXML qui récupère les données des colonnes CustomerID et OrderDate du document XML. Dans la dernière étape du processus, la procédure stockée sp_xml_removedocumentest appelée. Ceci permet en effet de libérer la mémoire allouée à la représentation en arborescence XML interne créée lors de 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 OPENXML comprennent :

• Un descripteur de document XML (idoc)

• Une expression XPath identifiant les nœuds à mapper avec les lignes (rowpattern)

• Une description de l'ensemble de lignes à générer

• Un mappage entre les colonnes de l'ensemble de lignes et les nœuds XML

Descripteur de document XML (idoc)

Ce descripteur de document est retourné par la procédure stockée sp_xml_preparedocument .

Expression XPath identifiant les nœuds à traiter (rowpattern)

L'expression XPath spécifiée comme paramètre rowpattern identifie un ensemble de nœuds dans le document XML. Chaque nœud identifié par rowpattern correspond à une ligne unique de l'ensemble de lignes généré par OPENXML.

Les nœuds identifiés par l'expression XPath peuvent être des nœuds XML quelconques dans le document XML. Si rowpattern identifie un ensemble d'éléments dans le document XML, il existe une seule ligne dans l'ensemble de lignes pour chaque nœud d'élément identifié. Par exemple, si rowpattern s'achève au niveau d'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 lorsque vous spécifiez un schéma d'ensemble de lignes.

Utiliser le format du tableau de bord

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

Lorsque vous procédez ainsi, OPENXML retourne un ensemble de lignes dans le format de table du bord. Ce format est appelé ainsi parce que chaque bord de l'arborescence du document XML analysé est mappé avec une ligne de l'ensemble de lignes.

Les tables du bord représentent au sein d'une table unique la structure à granularité fine d'un document XML. Cette structure inclut les noms des éléments et attributs, la hiérarchie du document, les espaces de noms et les instructions de traitement. Le format de table du bord vous permet d'obtenir des informations supplémentaires non présentées par le biais des métapropriétés. Pour plus d'informations sur les métapropriétés, consultez Specify Metaproperties in OPENXML.

Les informations supplémentaires fournies par une table du bord vous permettent de stocker et d'interroger le type de données d'un élément et d'un attribut, le type de nœud, ainsi que de stocker et d'interroger les informations sur la structure d'un document XML. Ces informations supplémentaires peuvent également vous permettre d'élaborer votre propre système de gestion de documents XML.

En utilisant une table du bord, vous pouvez écrire des procédures stockées qui acceptent des documents XML comme entrée de type BLOB (Binary Large Object), qui génèrent la table du bord, puis extraient et analysent le document à un niveau plus détaillé. Ce niveau détaillé peut inclure la recherche de la hiérarchie du document, des noms des éléments et des attributs, des espaces de noms et des instructions de traitement.

La table du bord peut également faire office de format de stockage pour les documents XML, lorsque le mappage avec 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 du bord à la place pour obtenir les mêmes informations.

Le tableau suivant décrit la structure de la table edge.

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 cela, spécifiez simplement le nom d'une table existante dont le schéma permet à OPENXML de générer l'ensemble de lignes.

Utilisez 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 définissez les noms de colonnes et leurs types de données, ainsi que leur mappage avec le 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é permet de mapper une colonne d'un ensemble de lignes avec le nœud XML identifié par rowpattern et de déterminer le type de mappage.

Si ColPattern n'est spécifié pour aucune colonne, la colonne de l'ensemble de lignes est mappée avec le nœud XML portant le même nom, en fonction du mappage défini par le paramètre flags . Cependant, si ColPattern est spécifié dans le cadre de la définition de schéma dans la clause WITH, il remplace le mappage défini dans le paramètre flags .

Un mappage entre les colonnes de l'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 de l’ensemble de lignes et les nœuds XML identifiés par rowpattern. Cette information est utilisée dans la transformation entre les nœuds XML et les colonnes de l'ensemble de lignes.

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

• en utilisant le paramètre flags

  Le mappage spécifié par le paramètre flags suppose une correspondance de nom dans laquelle les nœuds XML sont mappés avec les colonnes correspondantes du même nom dans l'ensemble de lignes.

• en utilisant le paramètre ColPattern

  L'expression XPathColPatternest spécifiée dans le paramètre SchemaDeclaration de la clause WITH. Le mappage spécifié dans ColPattern remplace celui défini par le paramètre flags .

  Vous pouvez utiliserColPattern 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 le paramètre flags.

  ColPattern est spécifié dans les circonstances suivantes :

  • le nom de colonne de l'ensemble de lignes est différent du nom d'élément ou d'attribut avec lequel il est mappé. Dans ce cas, ColPattern permet d'identifier le nom d'élément ou d'attribut XML avec lequel la colonne de l'ensemble de lignes est mappée.

  • vous souhaitez mapper un attribut de métapropriété avec la colonne. Dans ce cas, ColPattern permet d'identifier la métapropriété avec laquelle la colonne de l'ensemble de lignes est mappée. Pour plus d’informations sur les métapropriétés, consultez Spécifier des métapropriétés dans OPENXML.

Les deux paramètres flags et 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 flags .

mappage centré sur les attributs

L’affectation de la valeur 1 au paramètre flags dans OPENXML (XML_ATTRIBUTES) permet de spécifier le mappage centré sur les attributs . Si le paramètre flags contient XML_ATTRIBUTES, l’ensemble de lignes exposé fournit ou utilise des lignes où chaque élément XML est représenté sous la forme de ligne. Les attributs XML sont mappés avec les attributs définis dans SchemaDeclaration ou fournis par le paramètre Tablename de la clause WITH, en fonction de la correspondance de noms. La correspondance de noms indique une situation dans laquelle les attributs XML d'un nom donné sont stockés dans une colonne de l'ensemble de lignes portant le même nom.

Si le nom de colonne diffère du nom d'attribut avec lequel il est mappé, ColPattern doit être spécifié.

Si l'attribut XML possède un qualificateur d'espace de nom, le nom de colonne dans l'ensemble de lignes doit également comprendre le qualificateur.

Mappage centré sur les éléments

L’affectation de la valeur 2 au paramètre flags dans OPENXML (XML_ELEMENTS) permet de spécifier le mappage centré sur les éléments . Il diffère du mappage centré sur les attributs sur les points suivants :

• La correspondance de noms de l'exemple de mappage, le mappage d'une colonne avec un élément XML de même nom, choisit les sous-éléments non complexes, à moins qu'un modèle de niveau colonne soit 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 pour valeur NULL. Les valeurs d'attribut des sous-éléments sont alors ignorées.

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

Voir aussi

• sp_xml_preparedocument (Transact-SQL)
• sp_xml_removedocument (Transact-SQL)
• OPENXML (Transact-SQL)
• Données XML (SQL Server)