OPENXML (Transact-SQL)
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
OPENXML fournit une vue de l'ensemble des lignes d'un document XML. En tant que fournisseur d’ensembles de lignes, OPENXML peut être inclus dans les instructions Transact-SQL dans lesquelles certains fournisseurs d’ensembles de lignes peuvent apparaître (notamment une table, une vue ou la fonction OPENROWSET).
Conventions de la syntaxe Transact-SQL
Syntaxe
OPENXML ( idoc int [ in ]
, rowpattern nvarchar [ in ]
, [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]
Remarque
Pour afficher la syntaxe Transact-SQL pour SQL Server 2014 (12.x) et versions antérieures, consultez la Documentation sur les versions antérieures.
Arguments
idoc
Descripteur de document utilisé pour la représentation interne d’un document XML. La représentation interne d’un document XML est créée en appelant sp_xml_preparedocument.
rowpattern
Modèle XPath utilisé pour identifier les nœuds à traiter comme des lignes. Les nœuds proviennent du document XML dont le descripteur est transmis au paramètre idoc.
flags
Indique le mappage utilisé entre les données XML et l'ensemble de lignes relationnel, ainsi que le mode de remplissage de la colonne de débordement. flags est un paramètre d’entrée facultatif qui peut prendre l’une des valeurs suivantes.
| Valeur d'octet | Description |
|---|---|
| 0 | Utilise par défaut le mappage attribute-centric. |
| 1 | Utilise le mappage attribute-centric. Peut être combiné avec XML_ELEMENTS. Dans ce cas, le mappage centré sur l’attribut est appliqué en premier, suivi du mappage centré sur l’élément pour toutes les colonnes restantes. |
| 2 | Utilisez le mappage element-centric. Peut être combiné avec XML_ATTRIBUTES. Dans ce cas, le mappage centré sur l’attribut est appliqué en premier, suivi du mappage centré sur l’élément pour toutes les colonnes restantes. |
| 8 | Peut être combiné (à l'aide de l'opérateur logique OR) avec XML_ATTRIBUTES ou XML_ELEMENTS. Dans un contexte d’extraction, cet indicateur spécifie que les données consommées ne doivent pas être copiées dans la propriété de dépassement @mp:xmltext. |
SchemaDeclaration
Définition de schéma du formulaire : ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ... ]
ColName
Nom de la colonne dans l’ensemble de lignes.
ColType
Type de données SQL Server de la colonne dans l’ensemble de lignes. Si les types de colonnes diffèrent du type de données xml sous-jacent de l’attribut, un forçage de type se produit.
ColPattern
Modèle XPath général et facultatif, qui décrit les modalités de mappage des nœuds XML vers les colonnes. Si ColPattern n’est pas spécifié, le mappage par défaut (mappage centré sur l’attribut ou centré sur l’élément, indiqué par flags) s’applique.
Le modèle XPath spécifié dans ColPattern permet de préciser qu’il s’agit d’un type de mappage particulier (pour le mappage centré sur l’attribut ou centré sur l’élément) qui remplace ou améliore le mappage par défaut indiqué par flags.
Le modèle XPath général spécifié comme ColPattern prend également en charge les métapropriétés.
MetaProperty
Une des métapropriétés fournies par OPENXML. Si MetaProperty est spécifié, la colonne contient les informations fournies par cette métapropriété. Les métapropriétés vous permettent d'extraire des informations (telles que la position relative et les informations sur l'espace de noms) concernant les nœuds XML. Ces métapropriétés donnent plus d’informations que la représentation textuelle.
TableName
Nom de la table qui peut être fourni (à la place de SchemaDeclaration) si une table comportant le schéma souhaité existe déjà et qu’aucun modèle de colonne n’est nécessaire.
Remarques
La clause WITH fournit un format d’ensemble de lignes (et d’autres informations de mappage pertinentes) en utilisant SchemaDeclaration ou en spécifiant un TableName existant. Si la clause WITH facultative n’est pas spécifiée, les résultats sont retournés au format de table edge. Les tables edge représentent la structure à granularité fine des documents XML (par exemple les noms d’élément/d’attribut, la hiérarchie du document, les espaces de noms, les instructions de traitement, etc.) dans une table unique.
Le tableau suivant décrit la structure de la table edge.
| Nom de la colonne | Type de données | Description |
|---|---|---|
| id | bigint | ID unique du nœud de document. L’ID de l’élément racine a pour valeur 0. Les valeurs négatives d'ID sont réservées. |
| parentid | bigint | Identifie le parent du nœud. Le parent identifié par cet ID n'est pas nécessairement l'élément parent ; il dépend du nodetype du nœud dont le parent est identifié par cet ID. Par exemple, si le nœud est un nœud texte, son parent peut être un nœud d'attribut. Si le nœud se situe au niveau supérieur du document XML, son ParentID a pour valeur NULL. |
| nodetype | int | Identifie le type de nœud. Il s'agit d'un entier qui correspond à la numérotation des types de nœud XML DOM. Les types de nœuds sont : 1 = Nœud d'élément 2 = Nœud d'attribut 3 = Nœud de texte |
| localname | nvarchar | Fournit le nom local de l'élément ou de l'attribut. A pour valeur NULL si l'objet DOM est dépourvu de nom. |
| prefix | nvarchar | Préfixe d’espace de noms du nom de nœud. |
| namespaceuri | nvarchar | URI d’espace de noms du nœud. Si la valeur est NULL, aucun espace de noms n'est présent. |
| datatype | nvarchar | Type de données réel de la ligne d’éléments ou d’attributs, sinon NUL. Le type de données est déduit de la DTD en ligne ou du schéma en ligne. |
| prev | bigint | ID XML de l’élément frère précédent. Vaut NULL en l'absence de frère précédent direct. |
| text | ntext | Contient la valeur de l’attribut ou le contenu de l’élément sous forme textuelle (ou NULL si l’entrée de table edge n’exige pas de valeur). |
Exemples
R. Utiliser une instruction SELECT de base avec OPENXML
Cet exemple crée une représentation interne de l'image XML à l'aide de sp_xml_preparedocument. Une instruction SELECT est alors exécutée sur la représentation interne du document XML, en utilisant un fournisseur d'ensembles de lignes OPENXML.
La valeur flag est définie sur 1. Cette valeur indique un mappage centré sur l’attribut. Par conséquent, les attributs XML sont mappés sur les colonnes de l'ensemble de lignes. Le paramètre rowpattern spécifié en tant que /ROOT/Customer identifie les nœuds <Customers> à traiter.
Le paramètre ColPattern (modèle de colonne) facultatif n’est pas spécifié, car le nom de colonne correspond au nom des attributs XML.
Le fournisseur de l'ensemble de lignes OPENXML crée un ensemble de lignes sur deux colonnes (CustomerID et ContactName), à partir desquelles l'instruction SELECT extrait les colonnes nécessaires (toutes les colonnes dans le cas présent).
DECLARE @idoc INT, @doc VARCHAR(1000);
SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer', 1) WITH (
CustomerID VARCHAR(10),
ContactName VARCHAR(20)
);
Voici le jeu de résultats obtenu.
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
Si la même instruction SELECT est exécutée avec flags égal à 2 (mappage centré sur l’élément), les valeurs retournées de CustomerID et ContactName sont NULL pour les deux clients du document XML, puisqu’il n’existe pas d’éléments nommés CustomerID ou ContactName dans le document XML.
Voici le jeu de résultats obtenu.
CustomerID ContactName
---------- -----------
NULL NULL
NULL NULL
B. Spécifier ColPattern pour le mappage des colonnes aux attributs XML
La requête suivante retourne différents attributs à partir du document XML : ID client, date de commande, ID produit et quantité. rowpattern identifie les éléments <OrderDetails>. ProductID et Quantity sont les attributs de l'élément <OrderDetails>. Toutefois, OrderID, CustomerID et OrderDate sont les attributs de l'élément parent (<Orders>).
Le paramètre facultatif ColPattern est spécifié pour les mappages suivants :
Dans l’ensemble de lignes,
OrderID,CustomerIDetOrderDatesont mappés sur les attributs du parent des nœuds identifiés par rowpattern dans le document XML.La colonne
ProdIDde l’ensemble de lignes est mappée sur l’attributProductIDet la colonneQtyde l’ensemble de lignes sur l’attributQuantitydes nœuds identifiés dans rowpattern.
Bien que le mappage element-centric soit défini par le paramètre flags, il est supplanté par le mappage spécifié dans ColPattern.
DECLARE @idoc INT, @doc VARCHAR(1000);
SET @doc = '
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">v
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer/Order/OrderDetail', 2) WITH (
OrderID INT '../@OrderID',
CustomerID VARCHAR(10) '../@CustomerID',
OrderDate DATETIME '../@OrderDate',
ProdID INT '@ProductID',
Qty INT '@Quantity'
);
Voici le jeu de résultats obtenu.
OrderID CustomerID OrderDate ProdID Qty
------------------------------------------------------------------------
10248 VINET 1996-07-04 00:00:00.000 11 12
10248 VINET 1996-07-04 00:00:00.000 42 10
10283 LILAS 1996-08-16 00:00:00.000 72 3
C. Obtenir les résultats dans un format de table edge
Le document XML de cet exemple comporte les éléments <Customers>, <Orders> et <Order_0020_Details>. La procédure stockée sp_xml_preparedocument est d’abord appelée pour obtenir un descripteur de document. Ce descripteur de document est transmis à OPENXML.
Dans l’instruction OPENXML, rowpattern (/ROOT/Customers) identifie les nœuds <Customers> à traiter. Comme la clause WITH n’est pas fournie, OPENXML retourne l’ensemble de lignes au format de table edge.
Enfin, l’instruction SELECT extrait toutes les colonnes dans la table edge.
DECLARE @idoc INT, @doc VARCHAR(1000);
SET @doc = '
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
<Orders CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
<Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
</Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
<Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
</Orders>
</Customers>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;
-- SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML(@idoc, '/ROOT/Customers')
EXEC sp_xml_removedocument @idoc;
Voir aussi
Commentaires
Bientôt disponible : tout au long de 2024, nous allons éliminer progressivement GitHub Issues comme mécanisme de commentaires pour le contenu et le remplacer par un nouveau système de commentaires. Pour plus d'informations, consultez : https://aka.ms/ContentUserFeedback.
Envoyer et afficher des commentaires pour