Extraction et écriture de 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 être exécutées directement ou à partir de procédures stockées. Pour extraire des résultats directement, vous utilisez la clause FOR XML de l'instruction SELECT puis définissez un mode XML dans cette clause : RAW, AUTO ou EXPLICIT.

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

SELECT Customers.CustomerID, ContactName, CompanyName,
       Orders.CustomerID, OrderDate
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID 
AND (Customers.CustomerID = N'ALFKI' 

    OR Customers.CustomerID = N'XYZAA')
ORDER BY Customers.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 à partir de documents XML en mémoire. En fournissant une vue des données internes d'un document XML sous la forme d'un ensemble de lignes, OPENXML permet d'accéder à des 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 instruction SELECT et SELECT INTO à la place habituelle d'une vue ou table source.

L'exemple suivant montre l'utilisation de OPENXML dans une instruction INSERT et dans une instruction SELECT. L'exemple de document XML comprend des é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, commentaires, etc.) 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. Plusieurs appels OPENXML peuvent être utilisés pour fournir une vue des ensembles de lignes de différentes parties du document XML et pour les traiter, par exemple pour les insérer dans différentes tables (ce processus s'appelle également " Morcellement XML dans des tables "). Dans l'exemple suivant, un document XML est morcelé de telle sorte que les éléments <Customers> sont stockés dans la table Customers et que les éléments <Orders> sont stockés dans la table Orders à l'aide de deux instructions INSERT.

Cet exemple illustre également comment une instruction SELECT peut, à l'aide du mot-clé OPENXML, extraire des données CustomerID et OrderDate du document XML.

DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, 
      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>'
-- Use OPENXML to provide rowset consisting of customer data.
INSERT Customers 
SELECT * 
FROM OPENXML(@hDoc, N'/ROOT/Customers') 
     WITH Customers
-- Use OPENXML to provide rowset consisting of order data.
INSERT Orders 
SELECT * 
FROM OPENXML(@hDoc, N'//Orders') 
     WITH Orders
-- Using OPENXML in a SELECT statement.
SELECT * FROM OPENXML(@hDoc, N'/ROOT/Customers/Orders') with (CustomerID nchar(5) '../@CustomerID', OrderDate datetime)
-- Remove the internal representation of the XML document.
EXEC sp_xml_removedocument @hDoc

L'illustration suivante montre l'analyse arborescente du document XML précédent créée par la procédure stockée sp_xml_pareparedocument.