OPENXML (Transact-SQL)

  • Article

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 ) ]

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 ou element-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 et element-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 0d’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é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. 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.

CustomerID ContactName
---------- --------------------
VINET      Paul Henriot
LILAS      Carlos Gonzlez

Si la même SELECT instruction est exécutée avec des indicateurs définis 2sur , 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.

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 et OrderDate 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’attribut ProductID et la colonne Qty de l’ensemble de lignes sur l’attribut Quantity 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.

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;

Contenu connexe