OPENXML (Transact-SQL)
S’applique à : SQL Server
Azure SQL Database
Azure SQL Managed Instance
OPENXML
fournit une vue d’ensemble de lignes sur un document XML. Étant donné qu’il OPENXML
s’agit d’un fournisseur d’ensembles de lignes, OPENXML
peut être utilisé dans les instructions Transact-SQL dans lesquelles des fournisseurs d’ensembles de lignes tels qu’une table, une vue ou la OPENROWSET
fonction peuvent apparaître.
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 |
La valeur par défaut est attribute-centric le mappage. |
1 |
Utilisez le attribute-centric mappage. Peut être combiné avec XML_ELEMENTS . Dans ce cas, attribute-centric le mappage est appliqué en premier. Ensuite, element-centric le mappage est appliqué pour toutes les colonnes restantes. |
2 |
Utilisez le element-centric mappage. Peut être combiné avec XML_ATTRIBUTES . Dans ce cas, element-centric le mappage est appliqué en premier. Ensuite, attribute-centric le mappage est appliqué pour toutes les colonnes restantes. |
8 |
Peut être combiné (OR logique) 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 (
attribute-centric
ouelement-centric
le mappage tel que spécifié par les indicateurs) a lieu.Le modèle XPath spécifié comme ColPattern est utilisé pour spécifier la nature spéciale du mappage (pour
attribute-centric
etelement-centric
le mappage) qui remplace ou améliore le mappage par défaut indiqué par les indicateurs.Le modèle XPath général spécifié comme ColPattern prend également en charge les métapropriétés.
MetaProperty
L’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.
Notes
La WITH
clause fournit un format d’ensemble de lignes (et des informations de mappage supplémentaires en fonction des besoins) à l’aide de SchemaDeclaration ou de la spécification d’un TableName existant. Si la clause facultative WITH
n’est pas spécifiée, les résultats sont retournés dans un format de tableau 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’élément racine a une valeur 0 d’ID . 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, mais il dépend du nœud dont le nodetype parent est identifié par cet ID. Par exemple, si le nœud est un nœud de texte, le parent de celui-ci peut être un nœud d’attribut.Si le nœud se trouve au niveau supérieur dans le document XML, il ParentID est NULL . |
nodetype |
int | Identifie le type de nœud. Cette valeur est un entier qui correspond à la numérotation du type de nœud DOM XML. Les types de nœuds sont :1 = Nœud d’élément2 = Nœud d’attribut3 = Nœud de texte |
localname |
nvarchar | Fournit le nom local de l'élément ou de l'attribut. NULL si l’objet DOM n’a pas 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. |
data type |
nvarchar | Le type de données réel de la ligne d’élément ou d’attribut, sinon est NULL . 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. NULL s’il n’y a pas de frère précédent direct. |
text |
ntext | Contient la valeur d’attribut ou le contenu de l’élément sous forme de texte (ou si NULL l’entrée de table de bord ne nécessite 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 attribute-centric
le mappage. 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 SELECT
instruction est exécutée avec des indicateurs définis 2
sur , indiquant le element-centric
mappage, les valeurs et ContactName
CustomerID
les deux clients du document XML sont retournéesNULL
, car aucun élément n’est nommé 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 entre les colonnes et les 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
,CustomerID
etOrderDate
sont mappés sur les attributs du parent des nœuds identifiés par rowpattern dans le document XML.La colonne
ProdID
de l’ensemble de lignes est mappée sur l’attributProductID
et la colonneQty
de l’ensemble de lignes sur l’attributQuantity
des nœuds identifiés dans rowpattern.
Bien que le element-centric
mappage soit spécifié par le paramètre d’indicateurs , le mappage spécifié dans ColPattern remplace ce mappage.
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. Étant donné que la WITH
clause n’est pas fournie, OPENXML
retourne l’ensemble de lignes dans un format de tableau de bord .
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;