Verwenden von FOR XML und OPENXML zum Veröffentlichen und Verarbeiten von XML-Daten

Sie können SQL-Abfragen so ausführen, dass Ergebnisse als XML- anstelle von Standardrowsets zurückgegeben werden. Diese Abfragen können entweder direkt oder aus gespeicherten Prozeduren und benutzerdefinierten Funktionen heraus ausgeführt werden. Um die Ergebnisse direkt abzurufen, verwenden Sie die FOR XML-Klausel der SELECT-Anweisung. Geben Sie dann in der FOR XML-Klausel einen XML-Modus an: RAW, AUTO, EXPLICIT oder PATH.

So ruft beispielsweise die folgende SELECT-Anweisung Informationen aus den Tabellen Sales.Customer und Sales.SalesOrderHeader der AdventureWorks-Datenbank ab. Diese Abfrage gibt den AUTO-Modus in der FOR XML-Klausel an:

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

Während Sie die FOR XML-Klausel zum Abrufen von Daten als XML-Dokument verwenden können, dient die Transact-SQL-Funktion OPENXML dazu, als XML-Dokument dargestellte Daten einzufügen. OPENXML ist ein Rowsetanbieter ähnlich einer Tabelle oder einer Sicht, der ein Rowset über arbeitsspeicherinterne XML-Dokumente bereitstellt. OPENXML ermöglicht den Zugriff auf XML-Daten, als ob es sich um ein relationales Rowset handeln würde, indem eine Rowsetsicht der internen Darstellung eines XML-Dokuments bereitgestellt wird. Die Datensätze im Rowset können in Datenbanktabellen gespeichert werden. OPENXML kann in SELECT- und SELECT INTO-Anweisungen verwendet werden, für die eine Quelltabelle oder -sicht angegeben werden kann.

Das folgende Beispiel veranschaulicht die Verwendung von OPENXML in einer INSERT- und einer SELECT-Anweisung. Das XML-Beispieldokument setzt sich aus <Customers>- und <Orders>-Elementen zusammen.

Zuerst analysiert die gespeicherte Prozedur sp_xml_preparedocument das XML-Dokument. Das analysierte Dokument ist eine strukturierte Darstellung der Knoten (Elemente, Attribute, Text und Kommentare) im XML-Dokument. OPENXML verweist dann auf dieses analysierte XML-Dokument und stellt eine Rowsetsicht des gesamten XML-Dokuments oder Teilen davon bereit. Eine INSERT-Anweisung kann mithilfe von OPENXML Daten aus einem solchen Rowset in eine Datenbanktabelle einfügen. Es können mehrere OPENXML-Aufrufe verwendet werden, um eine Rowsetsicht verschiedener Teile des XML-Dokuments bereitzustellen und sie zu verarbeiten, indem sie z. B. in unterschiedliche Tabellen eingefügt werden. Dieses Verfahren wird auch als Aufteilen von XML-Daten in Tabellen bezeichnet.

Im folgenden Beispiel wird ein XML-Dokument so aufgeteilt, dass mithilfe von zwei INSERT-Anweisungen <Customers>-Elemente in der Customers-Tabelle und <Orders>-Elemente in der Orders-Tabelle gespeichert werden. Das Beispiel enthält auch eine SELECT-Anweisung mit OPENXML, die CustomerID und OrderDate aus dem XML-Dokument abruft. Der letzte Schritt in diesem Verfahren besteht darin, sp_xml_removedocument erneut aufzurufen. Dadurch wird der der internen XML-Darstellung, die während der Analysephase erstellt wurde, zugewiesene Arbeitsspeicher freigegeben.

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

In folgender Abbildung wird die analysierte XML-Struktur des vorherigen XML-Dokuments dargestellt, das mithilfe von sp_xml_preparedocument erstellt wurde.