Exemples : Utiliser OPENXML
S’applique à :
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
Les exemples de cet article montrent comment OPENXML est utilisé pour créer une vue d’ensemble de lignes d’un document XML. Pour plus d’informations sur la syntaxe d’OPENXML, consultez OPENXML (Transact-SQL). Les exemples montrent tous les aspects d’OPENXML, mais ne spécifient pas de métapropriétés dans OPENXML. Pour plus d’informations sur la spécification de métapropriétés dans OPENXML, consultez Spécifier des métapropriétés dans OPENXML.
Examples
Lors de la récupération des données, rowpattern est utilisé pour identifier les nœuds du document XML qui déterminent les lignes. De plus, rowpattern est exprimé dans le langage du modèle XPath utilisé dans la mise en œuvre XPath de MSXML. Par exemple, si le modèle s’achève au niveau d’un élément ou d’un attribut, une ligne est créée pour chaque nœud d’élément ou d’attribut sélectionné par rowpattern.
La valeur flags fournit le mappage par défaut. Si aucun ColPattern n’est spécifié dans SchemaDeclaration, le mappage défini par la valeur flags est appliqué. La valeur flags est ignorée si ColPattern est spécifié dans SchemaDeclaration. Le paramètre ColPattern spécifié détermine le mappage (centré sur l’attribut ou sur l’élément), ainsi que la façon dont les données en excès ou non consommées sont traitées.
A. Exécuter une instruction SELECT avec OPENXML
Le document XML de cet exemple est constitué des éléments et <Order><OrderDetail> des <Customer>éléments. L’instruction OPENXML extrait des informations sur les clients dans un ensemble de lignes à deux colonnes ( CustomerID et ContactName) à partir du document XML.
Tout d’abord, la procédure stockée sp_xml_preparedocument est appelée pour obtenir un descripteur de document. Ce descripteur est transmis à OPENXML.
L'instruction OPENXML contient les éléments suivants :
rowpattern (/ROOT/Customer) identifie les
<Customer>nœuds à traiter.Le paramètre flags prend la valeur 1 pour indiquer qu’il s’agit d’un mappage centré sur l’attribut. Ainsi, les attributs XML sont mappés sur les colonnes de l’ensemble de lignes défini dans SchemaDeclaration.
Dans SchemaDeclaration(dans la clause WITH), les valeurs ColName spécifiées correspondent aux noms d’attributs XML. Par conséquent, le paramètre ColPattern n’est pas spécifié dans SchemaDeclaration.
Ensuite, l'instruction SELECT extrait toutes les colonnes dans l'ensemble de lignes fourni par OPENXML.
DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<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 Gonzalez">
<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 @DocHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @DocHandle;
Voici le résultat obtenu :
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
Étant donné que les <Customer> éléments n’ont pas de sous-éléments, si la même instruction SELECT est exécutée avec des indicateurs définis sur 2 pour indiquer le mappage centré sur l’élément, les valeurs de CustomerID et contactName pour les deux clients sont retournées comme NULL.
@xmlDocument peut également être de type xml ou de type (n)varchar(max).
Si <CustomerID> et <ContactName> dans le document XML sont des sous-éléments, le mappage centré sur les éléments récupère les valeurs.
DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer>
<CustomerID>VINET</CustomerID>
<ContactName>Paul Henriot</ContactName>
<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</CustomerID>
<ContactName>Carlos Gonzalez</ContactName>
<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 @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @XmlDocumentHandle;
Voici le résultat obtenu :
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
Le handle de document retourné par sp_xml_preparedocument est valide pendant le lot et non pendant la session.
B. Spécifier ColPattern pour le mappage entre les colonnes d’ensemble de lignes et les attributs XML et les éléments
Cet exemple montre comment le modèle XPath est défini dans le paramètre facultatif ColPattern pour fournir un mappage entre les colonnes d’un ensemble de lignes et les attributs/éléments XML.
Le document XML de cet exemple est constitué des éléments et <Order><OrderDetail> des <Customer>éléments. L’instruction OPENXML extrait des informations sur les clients et les commandes sous la forme d’un ensemble de lignes (CustomerID, OrderDate, ProdIDet Qty) à partir du document XML.
Tout d’abord, la procédure stockée sp_xml_preparedocument est appelée pour obtenir un descripteur de document. Ce descripteur est transmis à OPENXML.
L'instruction OPENXML contient les éléments suivants :
- rowpattern (/ROOT/Customer/Order/OrderDetail) identifie les
<OrderDetail>nœuds à traiter.
À titre d’illustration, le paramètre flags prend la valeur 2 pour indiquer qu’il s’agit d’un mappage centré sur l’élément. Toutefois, le mappage spécifié dans ColPattern remplace ce dernier. Autrement dit, le modèle XPath spécifié dans ColPattern mappe les colonnes de l’ensemble de lignes sur des attributs. Il en résulte un mappage centré sur l'attribut.
Dans SchemaDeclaration(dans la clause WITH), le paramètre ColPattern est également spécifié avec les paramètres ColName et ColType . Le paramètre facultatif ColPattern correspond au modèle XPath spécifié et définit les éléments suivants :
Les colonnes OrderID, CustomerID et OrderDate de l’ensemble de lignes correspondent aux attributs du parent des nœuds identifiés par rowpattern et rowpattern identifie les
<OrderDetail>nœuds. Par conséquent, les colonnes CustomerID et OrderDate correspondent aux attributs CustomerID et OrderDate de l’élément<Order>.Les colonnes ProdID et Qty de l’ensemble de lignes sont mappées sur les attributs ProductID et Quantity des nœuds identifiés par rowpattern.
Ensuite, l'instruction SELECT extrait toutes les colonnes dans l'ensemble de lignes fourni par OPENXML.
DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<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 Gonzalez">
<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 @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity');
EXEC sp_xml_removedocument @XmlDocumentHandle;
Voici le résultat 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
Le modèle XPath défini comme ColPattern peut également être spécifié pour mapper les éléments XML sur les colonnes de l’ensemble de lignes (aboutissant ainsi à un mappage centré sur l'élément). Dans l’exemple suivant, le document <CustomerID> XML et <OrderDate> les sous-éléments de l’élément <Orders> sont des sous-éléments. Comme ColPattern remplace le mappage spécifié dans le paramètre d’indicateurs , le paramètre d’indicateurs n’est pas spécifié dans OPENXML.
DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity');
EXEC sp_xml_removedocument @docHandle;
C. Combiner le mappage centré sur les attributs et centré sur les éléments
Dans cet exemple, le paramètre flags a la valeur 3 pour indiquer qu’il s’agit d’un mappage centré à la fois sur l’attribut et sur l’élément. Dans ce cas, le mappage centré sur l'attribut est appliqué en premier, puis le mappage centré sur l'élément est appliqué ensuite pour toutes les colonnes non encore traitées.
DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument =N'<ROOT>
<Customer CustomerID="VINET" >
<ContactName>Paul Henriot</ContactName>
<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 Gonzalez</ContactName>
<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 @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer',3)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @docHandle;
Voici le résultat :
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
Le mappage centré sur l’attribut est appliqué à CustomerID. Il n’existe aucun attribut ContactName dans l’élément <Customer> . Par conséquent, le mappage centré sur l'élément est appliqué.
D. Spécifier la fonction XPath text() en tant que ColPattern
Le document XML de cet exemple est constitué des éléments et <Order> des <Customer> éléments. L’instruction OPENXML récupère un ensemble de lignes constitué de l’attribut oid de l’élément <Order> , l’ID du parent du nœud identifié par rowpattern et la chaîne de valeur feuille du contenu de l’élément.
Tout d’abord, la procédure stockée sp_xml_preparedocument est appelée pour obtenir un descripteur de document. Ce descripteur est transmis à OPENXML.
L'instruction OPENXML contient les éléments suivants :
rowpattern (/root/Customer/Order) identifie les
<Order>nœuds à traiter.Le paramètre flags prend la valeur 1 pour indiquer qu’il s’agit d’un mappage centré sur l’attribut. Ainsi, les attributs XML sont mappés sur les colonnes de l’ensemble de lignes définies dans SchemaDeclaration.
Dans SchemaDeclaration (dans la clause WITH), les noms de colonne de l’ensemble de lignes oid et amount correspondent aux noms des attributs XML équivalents. Par conséquent, le paramètre ColPattern n’est pas spécifié. Pour la colonne de commentaire dans l’ensemble de lignes, la fonction XPath,
text()est spécifiée en tant que ColPattern. Ceci remplace le mappage centré sur l’attribut défini dans le paramètre flags; la colonne contient la chaîne de valeur de feuille du contenu de l’élément.
Ensuite, l'instruction SELECT extrait toutes les colonnes dans l'ensemble de lignes fourni par OPENXML.
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--sample XML document
SET @xmlDocument =N'<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()');
EXEC sp_xml_removedocument @docHandle;
Voici le résultat obtenu :
oid amount comment
----- ----------- -----------------------------
O1 3.5 NULL
O2 13.4 Customer was very satisfied
O3 100.0 Happy Customer.
O4 10000.0 NULL
E. Spécifier TableName dans la clause WITH
Dans cet exemple, TableName est spécifié dans la clause WITH au lieu de SchemaDeclaration. Cela est utile si vous disposez d’une table dont la structure vous convient et qu’aucun modèle de colonne (paramètre ColPattern ) n’est requis.
Le document XML de cet exemple est constitué des éléments et <Order> des <Customer> éléments. L’instruction OPENXML extrait les informations sur les commandes dans un ensemble de lignes à trois colonnes (oid, dateet amount) à partir du document XML.
Tout d’abord, la procédure stockée sp_xml_preparedocument est appelée pour obtenir un descripteur de document. Ce descripteur est transmis à OPENXML.
L'instruction OPENXML contient les éléments suivants :
rowpattern (/root/Customer/Order) identifie les
<Order>nœuds à traiter.Il n’y a pas de schemaDeclaration dans la clause WITH. Un nom de table est spécifié à la place. Par conséquent, le schéma de la table est utilisé en guise de schéma de l'ensemble de lignes.
Le paramètre flags prend la valeur 1 pour indiquer qu’il s’agit d’un mappage centré sur l’attribut. Par conséquent, les attributs des éléments (identifiés par rowpattern) sont mappés sur les colonnes de l’ensemble de lignes portant le même nom.
Ensuite, l'instruction SELECT extrait toutes les colonnes dans l'ensemble de lignes fourni par OPENXML.
-- Create a test table. This table schema is used by OPENXML as the
-- rowset schema.
CREATE TABLE T1(oid char(5), date datetime, amount float);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
-- Sample XML document
SET @xmlDocument =N'<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very
satisfied</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
WITH T1;
EXEC sp_xml_removedocument @docHandle;
Voici le résultat obtenu :
oid date amount
----- --------------------------- ----------
O1 1996-01-20 00:00:00.000 3.5
O2 1997-04-30 00:00:00.000 13.4
O3 1999-07-14 00:00:00.000 100.0
O4 1996-01-20 00:00:00.000 10000.0
F. Obtenir le résultat dans un format de tableau de bord
Dans cet exemple, la clause WITH n’est pas spécifiée dans l’instruction OPENXML. Par conséquent, l'ensemble de lignes généré par OPENXML possède un format de table edge. L'instruction SELECT renvoie toutes les colonnes dans la table edge.
L’exemple de document XML dans l’exemple est constitué des éléments et <OrderDetail><Order>des <Customer>éléments.
Tout d’abord, la procédure stockée sp_xml_preparedocument est appelée pour obtenir un descripteur de document. Ce descripteur est transmis à OPENXML.
L'instruction OPENXML contient les éléments suivants :
rowpattern (/ROOT/Customer) identifie les
<Customer>nœuds à traiter.La clause WITH n’est pas fournie. Par conséquent, OPENXML renvoie l'ensemble de lignes dans un format de table edge.
Ensuite, l'instruction SELECT extrait toutes les colonnes dans la table edge.
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
SET @xmlDocument = N'<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 Gonzalez">
<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 @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer');
EXEC sp_xml_removedocument @docHandle;
Le résultat est renvoyé sous la forme de table edge. Vous pouvez écrire des requêtes sur la table edge afin d'obtenir des informations. Par exemple :
La requête suivante renvoie le nombre de nœuds Customer dans le document. Étant donné que la clause WITH n’est pas spécifiée, OPENXML retourne une table de bord. L'instruction SELECT interroge la table edge.
SELECT count(*) FROM OPENXML(@docHandle, '/') WHERE localname = 'Customer';La requête suivante renvoie les noms locaux des nœuds XML de type element.
SELECT distinct localname FROM OPENXML(@docHandle, '/') WHERE nodetype = 1 ORDER BY localname;
G. Spécifier la fin de rowpattern avec un attribut
Le document XML de cet exemple est constitué des éléments et <Order><OrderDetail> des <Customer>éléments. L’instruction OPENXML extrait les informations sur le détail des commandes dans un ensemble de lignes à trois colonnes (ProductID, Quantityet OrderID) à partir du document XML.
Tout d’abord, la procédure stockée sp_xml_preparedocument est appelée pour obtenir un descripteur de document. Ce descripteur est transmis à OPENXML.
L'instruction OPENXML contient les éléments suivants :
rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) se termine par un attribut XML, ProductID. Dans l'ensemble de lignes obtenu, une ligne est créée pour chaque nœud d'attribut sélectionné dans le document XML.
Dans cet exemple, le paramètre d’indicateurs n’est pas spécifié. En revanche, les mappages sont définis par le paramètre ColPattern .
Dans SchemaDeclaration (dans la clause WITH), le paramètre ColPattern est également spécifié avec les paramètres ColName et ColType . Le paramètre facultatif ColPattern correspond au modèle XPath spécifié pour définir les éléments suivants :
Le modèle XPath (.) spécifié pour le paramètre ColPattern de la colonne ProdID de l’ensemble de lignes identifie le nœud de contexte (nœud actuel). Conformément au rowpattern spécifié, il s’agit de l’attribut ProductID de l’élément
<OrderDetail>.ColPattern, .. /@Quantity, spécifié pour la colonne Qty dans l’ensemble de lignes identifie l’attribut Quantity du parent,
<OrderDetail>nœud du nœud de contexte, <ProductID>.De même, colPattern, .. /.. /@OrderID, spécifié pour la colonne OID dans l’ensemble de lignes identifie l’attribut OrderID du parent,
<Order>du nœud parent du nœud de contexte. Le nœud parent est<OrderDetail>, et le nœud de contexte est<ProductID>.
Ensuite, l'instruction SELECT extrait toutes les colonnes dans l'ensemble de lignes fourni par OPENXML.
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--Sample XML document
SET @xmlDocument =N'<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 Gonzalez">
<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 @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')
WITH ( ProdID int '.',
Qty int '../@Quantity',
OID int '../../@OrderID');
EXEC sp_xml_removedocument @docHandle;
Voici le résultat obtenu :
ProdID Qty OID
----------- ----------- -------
11 12 10248
42 10 10248
72 3 10283
H. Spécifier un document XML qui a plusieurs nœuds de texte
Si vous avez plusieurs nœuds de texte dans un document XML, une instruction SELECT avec un ColPattern, text()retourne uniquement le premier nœud de texte, au lieu de tous ces nœuds. Par exemple :
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT,
N'<root xmlns:a="urn:1">
<a:Elem abar="asdf">
T<a>a</a>U
</a:Elem>
</root>',
'<ns xmlns:b="urn:1" />';
SELECT * FROM openxml(@h, '/root/b:Elem')
WITH (Col1 varchar(20) 'text()');
EXEC sp_xml_removedocument @h;
L’instruction SELECT renvoie T comme résultat (et non TaU).
I. Spécifier le type de données XML dans la clause WITH
Dans la clause WITH, un modèle de colonne mappé sur la colonne de type xml (typé ou non typé) doit renvoyer une séquence vide ou une séquence d’éléments, des instructions de traitement, des nœuds de texte et des commentaires. Les données sont converties en type de données xml .
Dans l’exemple suivant, la déclaration de schéma de la table de la clause WITH inclut des colonnes de type xml .
DECLARE @h int;
DECLARE @x xml;
set @x = '<Root>
<row id="1"><lname>Duffy</lname>
<Address>
<Street>111 Maple</Street>
<City>Seattle</City>
</Address>
</row>
<row id="2"><lname>Wang</lname>
<Address>
<Street>222 Pine</Street>
<City>Bothell</City>
</Address>
</row>
</Root>';
EXEC sp_xml_preparedocument @h output, @x;
SELECT *
FROM OPENXML (@h, '/Root/row', 10)
WITH (id int '@id',
lname varchar(30),
xmlname xml 'lname',
OverFlow xml '@mp:xmltext');
EXEC sp_xml_removedocument @h;
Plus précisément, vous passez une variable de type xml (@x) à la fonction sp_xml_preparedocument().
Voici le résultat obtenu :
id lname xmlname OverFlow
--- ------- ------------------------------ -------------------------------
1 Duffy <lname>Duffy</lname> <row><Address>
<Street>111 Maple</Street>
<City>Seattle</City>
</Address></row>
2 Wang <lname>Wang</lname> <row><Address>
<Street>222 Pine</Street>
<City>Bothell</City>
</Address></row>
Notez les points suivants par rapport au résultat obtenu :
Pour la colonne lname du type varchar(30), sa valeur est récupérée à partir de l’élément correspondant
<lname>.Pour la colonne xmlname de type xml , le même élément name est renvoyé en tant que valeur.
L'indicateur prend la valeur 10, soit 2 + 8, où 2 indique qu'il s'agit d'un mappage centré sur l'élément et 8 indique que seules les données XML non consommées doivent être ajoutées à la colonne OverFlow définie dans la clause WITH. Si vous définissez l'indicateur à 2, l'ensemble du document XML est copié dans la colonne OverFlow spécifiée dans la clause WITH.
Si la colonne de la clause WITH est une colonne XML typée et que l’instance XML ne confirme pas le schéma, une erreur est retournée.
J. Récupérer des valeurs individuelles à partir d’attributs à valeurs multiples
Un document XML peut avoir des attributs qui ont plusieurs valeurs. Par exemple, l’attribut IDREFS peut avoir plusieurs valeurs. Dans un document XML, les valeurs des attributs à plusieurs valeurs sont spécifiées sous la forme d'une chaîne qui contient les valeurs séparées par un espace. Dans le document XML suivant, l’attribut attend de l’élément <Student> et l’attribut attendedBy de <la classe> sont multivalués. La récupération de valeurs individuelles à partir d’un attribut XML à valeurs multiples et le stockage de chaque valeur dans une ligne distincte de la base de données nécessite un travail supplémentaire. Cet exemple illustre le processus.
Cet exemple de document XML est constitué des éléments suivants :
<Étudiant>
Attributs id (ID étudiant), nameet attends . L’attribut attends est un attribut à plusieurs valeurs.
<Classe>
Attributs id (ID cours), nameet attendedBy . L’attribut attendedBy est un attribut à plusieurs valeurs.
L’attribut attend dans <Student> et l’attribut attendedBy dans <La classe> représentent une relation m:n entre les tables Student et Class. Un étudiant peut faire partie de plusieurs cours et un cours peut avoir plusieurs étudiants.
Supposons que vous vouliez fragmenter ce document et l'enregistrer dans la base de données comme suit :
Enregistrez les données
<Student>dans la table Students.Enregistrez les données
<Class>dans la table Courses.Enregistrez les données de la relation m:n (entre Student et Class) dans la table CourseAttendence. D’autres travaux sont nécessaires pour extraire les valeurs. Pour extraire ces informations et les stocker dans la table, utilisez ces procédures stockées :
Insert_Idrefs_Values
Insère les valeurs de l'identificateur du cours et de l'identificateur de l'étudiant dans la table CourseAttendence.
Extract_idrefs_values
Extrait les ID d’étudiants individuels de chaque <élément de cours> . Une table edge est utilisée pour extraire ces valeurs.
Voici les étapes à suivre :
-- Create these tables:
DROP TABLE CourseAttendance;
DROP TABLE Students;
DROP TABLE Courses;
GO
CREATE TABLE Students(
id varchar(5) primary key,
name varchar(30)
);
GO
CREATE TABLE Courses(
id varchar(5) primary key,
name varchar(30),
taughtBy varchar(5)
);
GO
CREATE TABLE CourseAttendance(
id varchar(5) references Courses(id),
attendedBy varchar(5) references Students(id),
constraint CourseAttendance_PK primary key (id, attendedBy)
);
GO
-- Create these stored procedures:
DROP PROCEDURE f_idrefs;
GO
CREATE PROCEDURE f_idrefs
@t varchar(500),
@idtab varchar(50),
@id varchar(5)
AS
DECLARE @sp int;
DECLARE @att varchar(5);
SET @sp = 0;
WHILE (LEN(@t) > 0)
BEGIN
SET @sp = CHARINDEX(' ', @t+ ' ');
SET @att = LEFT(@t, @sp-1);
EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')');
SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp);
END;
GO
DROP PROCEDURE fill_idrefs
GO
CREATE PROCEDURE fill_idrefs
@xmldoc int,
@xpath varchar(100),
@from varchar(50),
@to varchar(50),
@idtable varchar(100)
AS
DECLARE @t varchar(500);
DECLARE @id varchar(5);
/* Temporary Edge table */
SELECT *
INTO #TempEdge
FROM OPENXML(@xmldoc, @xpath);
DECLARE fillidrefs_cursor CURSOR FOR
SELECT CAST(iv.text AS nvarchar(200)) AS id,
CAST(av.text AS nvarchar(4000)) AS refs
FROM #TempEdge c, #TempEdge i,
#TempEdge iv, #TempEdge a, #TempEdge av
WHERE c.id = i.parentid
AND UPPER(i.localname) = UPPER(@from)
AND i.id = iv.parentid
AND c.id = a.parentid
AND UPPER(a.localname) = UPPER(@to)
AND a.id = av.parentid;
OPEN fillidrefs_cursor
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
execute f_idrefs @t, @idtable, @id;
END
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
END;
CLOSE fillidrefs_cursor;
DEALLOCATE fillidrefs_cursor;
Go
-- This is the sample document that is shredded and the data is stored in the preceding tables.
DECLARE @h int;
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>
<Student id = "s1" name = "Student1" attends = "c1 c3 c6" />
<Student id = "s2" name = "Student2" attends = "c2 c4" />
<Student id = "s3" name = "Student3" attends = "c2 c4 c6" />
<Student id = "s4" name = "Student4" attends = "c1 c3 c5" />
<Student id = "s5" name = "Student5" attends = "c1 c3 c5 c6" />
<Student id = "s6" name = "Student6" />
<Class id = "c1" name = "Intro to Programming"
attendedBy = "s1 s4 s5" />
<Class id = "c2" name = "Databases"
attendedBy = "s2 s3" />
<Class id = "c3" name = "Operating Systems"
attendedBy = "s1 s4 s5" />
<Class id = "c4" name = "Networks" attendedBy = "s2 s3" />
<Class id = "c5" name = "Algorithms and Graphs"
attendedBy = "s4 s5"/>
<Class id = "c6" name = "Power and Pragmatism"
attendedBy = "s1 s3 s5" />
</Data>';
INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students
INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses
/* Using the edge table */
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance';
SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM CourseAttendance;
EXECUTE sp_xml_removedocument @h;
K. Récupérer le fichier binaire à partir de données encodées en base64 dans XML
Des données binaires sont souvent incluses dans XML à l'aide de l'encodage base64. Lorsque vous fragmentez ce code XML à l'aide d'une instruction OPENXML, vous recevez les données encodées en base64. Cet exemple montre comment reconvertir en binaire les données encodées en base64.
Créez une table avec un exemple de données binaires.
Utilisez une requête FOR XML et l'option BINARY BASE64 pour construire du code XML où les données binaires sont encodées en base64.
Fragmentez le code XML à l'aide de OPENXML. Les données renvoyées par OPENXML seront des données encodées en base64. Ensuite, appelez la
.valuefonction pour la convertir en binaire.
CREATE TABLE T (Col1 int primary key, Col2 varbinary(100));
GO
-- Insert sample binary data
INSERT T VALUES(1, 0x1234567890);
GO
-- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)
SELECT * FROM T
FOR XML AUTO, BINARY BASE64;
GO
-- result
-- <T Col1="1" Col2="EjRWeJA="/>
-- Now shredd the sample XML using OPENXML.
-- Call the .value function to convert
-- the base64 encoded data returned by OPENXML to binary.
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT, '<T Col1="1" Col2="EjRWeJA="/>';
SELECT Col1,
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol
FROM openxml(@h, '/T')
WITH (Col1 integer, Col2 varchar(max)) ;
EXEC sp_xml_removedocument @h;
GO
Voici l'ensemble de résultats. Les données binaires renvoyées sont les données binaires d'origine de la table T.
Col1 BinaryCol
----------- ---------------------
1 0x1234567890
Voir aussi
Commentaires
Bientôt disponible : Tout au long de 2024, nous allons supprimer 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