OPENXML (Transact-SQL)

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

Icône Lien de rubriqueConventions de 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. Représentation interne d'un document XML créée en appelant sp_xml_preparedocument.
  • rowpattern
    Schéma XPath utilisé pour identifier les nœuds (dans le document XML dont le descripteur est transmis au paramètre idoc) à traiter comme des lignes.
  • flags
    Indique le mappage qui doit être utilisé entre les données XML et l'ensemble de lignes relationnelles, ainsi que le mode de remplissage de la colonne de débordement. L'argument 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 attribute-centric est d'abord appliqué, puis le mappage element-centric est appliqué pour toutes les colonnes qui n'ont pas encore été traitées.

    2

    Utilise le mappage element-centric. Peut être combiné avec XML_ATTRIBUTES. Dans ce cas, le mappage attribute-centric est d'abord appliqué, puis le mappage element-centric est appliqué pour toutes les colonnes qui n'ont pas encore été traitées.

    8

    Peut être combiné (à l'aide de l'opérateur logique OR) avec XML_ATTRIBUTES ou XML_ELEMENTS. Dans le contexte d'une extraction, cet indicateur indique que les données consommées ne doivent pas être copiées vers 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 conflit de types se produit.
    • ColPattern
      Modèle XPath général et optionnel, 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 est appliqué (attribute-centric ou element-centric, selon la valeur de flags).

      Le modèle XPath spécifié dans ColPattern permet de préciser qu'il s'agit d'un type de mappage particulier (dans le cas d'un mappage attribute-centric ou element-centric) qui remplace ou améliore le mappage par défaut signifié par flags.

      Le modèle XPath général spécifié dans 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ée, la colonne contient les informations fournies par cette dernière. 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. Vous disposez ainsi de plus d'informations que n'en propose la représentation textuelle.
  • TableName
    Nom de la table pouvant être fourni (au lieu de SchemaDeclaration) si une table comportant le schéma souhaité existe déjà et qu'aucun modèle de colonne n'est requis.

Notes

La clause WITH fournit un format d'ensemble de lignes (et d'autres informations de mappage pertinentes) en utilisant la valeur de 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 dans un 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 seule table.

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

Nom de colonne Type de données Description

id

bigint

ID unique du nœud du document.

L'ID de l'élément racine a la valeur 0. Les valeurs ID négatives 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 type du nœud dont le parent est identifié par cet ID. Par exemple, si le nœud est de type 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œud 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 de l'espace de noms du nom de nœud.

namespaceuri

nvarchar

URI de l'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 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. 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 de texte (ou NULL si l'entrée de table edge ne requiert pas de valeur).

Exemples

A. Utilisation d'une instruction SELECT simple 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 1. Autrement dit, le mappage attribute-centric est utilisé. Par conséquent, les attributs XML sont mappés sur les colonnes de l'ensemble de lignes. Le rowpattern spécifié en tant que /ROOT/Customer identifie les nœuds <Customers> à traiter.

Le paramètre (modèle de colonne) ColPattern facultatif n'est pas spécifié, car le nom de colonne correspond aux noms 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
DECLARE @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 l'ensemble des résultats.

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

Si la même instruction SELECT est exécutée lorsque la valeur de flags est 2 (mappage element-centric), les valeurs de CustomerID et ContactName pour les deux clients dans le document XML sont retournées à l'état NULL, puisque les éléments <Customers> ne comportent aucun sous-élément.

Voici l'ensemble des résultats.

CustomerID ContactName
---------- -----------
NULL       NULL
NULL       NULL

B. Spécification de ColPattern pour le mappage des colonnes aux attributs XML

La requête suivante retourne l'identificateur du client, la date de commande, la référence du produit et les attributs de quantité à partir du document XML. Le paramètre 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 modèle ColPattern facultatif est spécifié. Cela signifie que :

  • Dans l'ensemble de lignes, OrderID, CustomerID et OrderDate correspondent aux attributs du parent des nœuds identifiés par rowpattern dans le document XML.
  • La colonne ProdID de l'ensemble de lignes correspond à l'attribut ProductID et la colonne Qty à l'attribut Quantity des 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
DECLARE @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">
   <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 l'ensemble des 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. Obtention des 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. La clause WITH n'est pas fournie : par conséquent, OPENXML retourne l'ensemble de lignes dans un format de table edge.

Enfin, l'instruction SELECT extrait toutes les colonnes dans la table de l'edge.

DECLARE @idoc int
DECLARE @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

Autres ressources

Utilisation de OPENXML

Aide et Informations

Assistance sur SQL Server 2005