Partager via

  • Article

Utilisation de FOR XML et OPENXML pour publier et traiter des données XML

Vous pouvez exécuter des requêtes SQL afin de renvoyer des résultats sous forme de données XML plutôt que d'ensembles de lignes ordinaires. Ces requêtes peuvent s'exécuter directement ou à partir de procédures stockées et de fonctions définies par l'utilisateur. Pour récupérer les résultats directement, utilisez tout d'abord la clause FOR XML de l'instruction SELECT. Dans la clause FOR XML, spécifiez ensuite un mode XML : RAW, AUTO, EXPLICIT ou PATH.

Par exemple, l'instruction SELECT suivante extrait des informations des tables Sales.Customer et Sales.SalesOrderHeader de la base de données AdventureWorks. Cette requête spécifie le mode AUTO dans la clause FOR XML :

USE AdventureWorks
GO
SELECT Cust.CustomerID, 
       OrderHeader.CustomerID,
       OrderHeader.SalesOrderID, 
       OrderHeader.Status,
       Cust.CustomerType
FROM Sales.Customer Cust 
INNER JOIN Sales.SalesOrderHeader OrderHeader
ON Cust.CustomerID = OrderHeader.CustomerID
FOR XML AUTO

Là où vous pouvez utiliser la clause FOR XML pour extraire des données sous forme de document XML, vous pouvez utiliser la fonction Transact-SQL OPENXML pour insérer des données représentées sous forme de document XML. OPENXML est un fournisseur d'ensembles de lignes similaire à une table ou à une vue, qui fournit un ensemble de lignes à des documents XML se trouvant en mémoire. En fournissant une vue de la représentation interne d'un document XML sous la forme d'un ensemble de lignes, OPENXML permet d'accéder aux données XML comme s'il s'agissait d'un ensemble de lignes relationnel. Les enregistrements de l'ensemble de lignes peuvent être stockés dans des tables de base de données. OPENXML peut apparaître dans les instructions SELECT et SELECT INTO dans lesquelles une vue ou une table source peut être spécifiée.

L'exemple ci-dessous montre l'utilisation de OPENXML dans une instruction INSERT et une instruction SELECT. L'exemple de document XML comprend les éléments <Customers> et <Orders>.

D'abord, la procédure stockée sp_xml_preparedocument analyse le document XML. Le document analysé est une représentation arborescente des nœuds (éléments, attributs, texte et commentaires) qui figurent dans le document XML. OPENXML fait ensuite référence à ce document XML analysé et fournit, sous la forme d'un ensemble de lignes, une vue de tout ou partie de ce document. Une instruction INSERT utilisant OPENXML peut ainsi insérer des données dans une table de base de données à partir de ce type d'ensemble de lignes. Vous pouvez utiliser plusieurs appels à OPENXML pour fournir une vue d'un ensemble de lignes provenant de différentes parties du document XML puis traiter celles-ci en les insérant par exemple dans différentes tables. Cette opération est également qualifiée de fragmentation de données XML dans des tables.

Dans l'exemple suivant, un document XML est fragmenté de telle sorte que les éléments <Customers> sont stockés dans la table Customers et les éléments <Orders> sont stockés dans la table Orders à l'aide de deux instructions INSERT. Cet exemple illustre également une instruction SELECT associée à OPENXML qui récupère les données des colonnes CustomerID et OrderDate du document XML. Dans la dernière étape du processus, la procédure stockée sp_xml_removedocument est appelée. Ceci permet en effet de libérer la mémoire allouée à la représentation en arborescence XML interne créée lors de la phase d'analyse.

-- Create tables for later population using OPENXML.
CREATE TABLE Customers (CustomerID varchar(20) primary key,
                ContactName varchar(20), 
                CompanyName varchar(20))
GO
CREATE TABLE Orders( CustomerID varchar(20), OrderDate datetime)
GO
DECLARE @docHandle int
DECLARE @xmlDocument nvarchar(max) -- or xml type
SET @xmlDocument = N'<ROOT>
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>
</Customers>
<Customers CustomerID="XYZBB" ContactName="Steve"
CompanyName="Company2">No Orders yet!
</Customers>
</ROOT>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@docHandle, N'/ROOT/Customers') 
  WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@docHandle, N'//Orders') 
  WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@docHandle, N'/ROOT/Customers/Orders') WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @docHandle

L'illustration suivante montre l'arborescence XML analysée du document XML précédent créé à l'aide de sp_xml_preparedocument.

Arborescence XML analysée