OPENXML (SQL Server)

Gilt für: SQL Server Azure SQL-Datenbank Azure SQL Managed Instance

OPENXML ist ein Transact-SQL-Schlüsselwort, das für arbeitsspeicherinterne XML-Dokumente ein Rowset bereitstellt, das mit einer Tabelle oder einer Sicht vergleichbar ist. OPENXML ermöglicht es, auf XML-Daten so zuzugreifen, wie auf ein relationales Rowset. Dies geschieht, indem eine Rowsetsicht der internen Darstellung eines XML-Dokuments bereitgestellt wird. Die Datensätze im Rowset können in Datenbanktabellen gespeichert werden.

OPENXML kann immer dann in SELECT- und SELECT INTO-Anweisungen verwendet werden, wenn Rowsetanbieter, eine Sicht oder OPENROWSET als Quelle verwendet werden können. Informationen zur Syntax von OPENXML finden Sie unter OPENXML (Transact-SQL).

Um Abfragen für XML-Dokumente mithilfe von OPENXML zu schreiben, müssen Sie zunächst sp_xml_preparedocumentaufrufen. Dies analysiert das XML-Dokument und gibt ein Handle auf das analysierte Dokument zurück, das zur Verwendung bereit ist. Das analysierte Dokument ist eine strukturierte Dokumentobjektdarstellung (DOM - Document Object Model) der verschiedenen Knoten des XML-Dokuments. Das Dokumenthandle wird an OPENXML übergeben. OPENXML erstellt dann auf der Grundlage der übergebenen Parameter eine Rowsetsicht des Dokuments.

Die interne Darstellung eines XML-Dokuments muss durch einen Aufruf der gespeicherten Systemprozedur sp_xml_removedocument aus dem Arbeitsspeicher gelöscht werden, um den Arbeitsspeicher zu leeren.

Die folgende Abbildung zeigt den Zykluszählprozess.

Um die Funktionsweise von OPENXML zu verstehen, benötigen Sie Kenntnisse über XPath-Abfragen und XML. Weitere Informationen zur XPath-Unterstützung in SQL Server finden Sie unter Verwenden von XPath-Abfragen in SQLXML 4.0.

Beispiel

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 im XML-Dokument (Elemente, Attribute, Text und Kommentare). OPENXML verweist dann auf dieses analysierte XML-Dokument und erstellt eine Rowsetansicht für das gesamte oder Teile des XML-Dokuments. 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 <Customers> -Anweisungen Customers -Elemente in der <Orders> -Tabelle und Orders -Elemente in der INSERT -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_removedocumenterneut 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;

Die folgende Abbildung zeigt die analysierte XML-Struktur des vorherigen XML-Dokuments, das mithilfe von sp_xml_preparedocument erstellt wurde.

OPENXML-Parameter

Zu den Parametern für OPENXML gehören:

• Ein XML-Dokumenthandle (idoc)

• Ein XPath-Ausdruck zur Identifizierung der Knoten, die Zeilen zugeordnet werden (rowpattern)

• Eine Beschreibung des zu generierenden Rowsets

• Die Zuordnung zwischen den Rowsetspalten und den XML-Knoten

XML-Dokumenthandle (idoc)

Das Dokumenthandle wird von der gespeicherten Prozedur sp_xml_preparedocument zurückgegeben.

XPath-Ausdruck zur Identifizierung der zu verarbeitenden Knoten (rowpattern)

Der als rowpattern angegebene XPath-Ausdruck bezeichnet einen Knotensatz im XML-Dokument. Jeder von rowpattern identifizierte Knoten entspricht in dem von OPENXML generierten Rowset einer einzelnen Zeile.

Bei den durch den XPath-Ausdruck identifizierten Knoten kann es sich um jeden im XML-Dokument enthaltenen Knoten handeln. Wenn rowpattern eine Reihe von Elementen im XML-Dokument identifiziert, wird für jeden identifizierten Elementknoten eine Zeile im Rowset erstellt. Wenn z. B. rowpattern mit einem Attribut endet, wird für jeden von rowpatternausgewählten Attributknoten eine Zeile erstellt.

Beschreibung des zu generierenden Rowsets

Zum Generieren des Rowsets verwendet OPENXML ein Rowsetschema. Ein Rowsetschema kann über die folgenden Optionen angegeben werden.

Verwenden des Rahmentabellenformats

Um ein Rowsetschema anzugeben, müssen Sie das Rahmentabellenformat verwenden. Verwenden Sie keine WITH-Klausel.

In diesem Fall gibt OPENXML ein Rowset im Rahmentabellenformat zurück. Die Bezeichnung Rahmentabelle geht darauf zurück, dass jeder Rahmen in der analysierten XML-Dokumentstruktur einer Zeile im Rowset zugeordnet wird.

Rahmentabellen stellen die detaillierte XML-Dokumentstruktur in einer einzigen Tabelle dar. Diese Struktur enthält die Element- und Attributnamen, die Dokumenthierarchie, die Namespaces und die Verarbeitungsanweisungen. Durch das Rahmentabellenformat erhalten Sie zusätzliche Informationen, die nicht über die Metaeigenschaften offen gelegt werden. Weitere Informationen zu Metaeigenschaften finden Sie unter Specify Metaproperties in OPENXML.

Die von Rahmentabellen gebotenen zusätzlichen Informationen ermöglichen Ihnen das Speichern und Abfragen des Datentyps eines Elements oder Attributs, des Knotentyps sowie das Speichern und Abfragen von Informationen zur Struktur von XML-Dokumenten. Die zusätzlichen Informationen ermöglichen außerdem das Erstellen eines eigenen XML-Dokumentmanagementsystems.

Mithilfe einer Rahmentabelle können Sie gespeicherte Prozeduren schreiben, die XML-Dokumente als BLOB-Eingabe (Binary Large OBject) verwenden, die Rahmentabelle ausgeben und dann das Dokument auf einer detaillierteren Ebene extrahieren und analysieren. Diese detaillierte Ebene kann die Dokumenthierarchie, die Element- und Attributnamen, die Namespaces und die Verarbeitungsanweisungen enthalten.

Die Rahmentabelle kann auch als Speicherformat für XML-Dokumente dienen, wenn keine andere Zuordnung zu relationalen Formaten logisch ist und ein ntext-Feld keine ausreichenden strukturellen Informationen bietet.

Wenn die Überprüfung des XML-Dokuments mit einem XML-Parser möglich ist, können Sie dieselben Informationen stattdessen auch mit einer Rahmentabelle erhalten.

In der folgenden Tabelle wird die Struktur der Rahmentabelle (edge) beschrieben.

Verwenden der WITH-Klausel, um eine vorhandene Tabelle anzugeben

Mit der WITH-Klausel können Sie den Namen einer vorhandenen Tabelle angeben. Hierzu geben Sie einfach den Namen einer vorhandenen Tabelle an, deren Schema OPENXML zum Generieren des Rowsets verwenden kann.

Verwenden der WITH-Klausel, um ein Schema anzugeben

Mit der WITH-Klausel können Sie ein vollständiges Schema angeben. Ein Rowsetschema wird durch Angeben der Spaltennamen, der zugehörigen Datentypen und der Zuordnung zum XML-Dokument angegeben.

Sie können das Spaltenmuster mithilfe des ColPattern-Parameters in SchemaDeclaration angeben. Das angegebene Spaltenmuster wird zur Zuordnung einer Rowsetspalte zum von rowpattern bezeichneten XML-Knoten und zur Bestimmung des Zuordnungstyps verwendet.

Wenn ColPattern für eine Spalte nicht angegeben ist, wird die Rowsetspalte basierend auf der vom flags-Parameter angegebenen Zuordnung dem XML-Knoten mit demselben Namen zugeordnet. Wenn ColPattern in der WITH-Klausel jedoch als Teil der Schemaspezifikation angegeben ist, wird die im flags -Parameter angegebene Zuordnung überschrieben.

Zuordnung zwischen den Rowsetspalten und den XML-Knoten

In der OPENXML-Anweisung können Sie optional den Typ der Zuordnung – wie attributzentriert oder elementzentriert – zwischen den Rowsetspalten und den von rowpatternidentifizierten XML-Knoten angeben. Diese Information wird bei der Transformation zwischen XML-Knoten und Rowsetspalten verwendet.

Die Zuordnung kann auf zwei Arten angegeben werden, wobei Sie auch beide angeben können:

• Mit dem flags -Parameter

  Die vom flags -Parameter angegebene Zuordnung geht von einer Namensentsprechung aus, bei der die XML-Knoten den entsprechenden Rowsetspalten mit demselben Namen zugeordnet werden.

• Mit dem ColPattern -Parameter

  ColPattern, ein XPath-Ausdruck, wird in der WITH-Klausel als Teil von SchemaDeclaration angegeben. Durch die in ColPattern angegebene Zuordnung wird die Zuordnung des flags -Parameters überschrieben.

  ColPattern kann zur Angabe des Zuordnungstyps – wie attributzentriert oder elementzentriert – verwendet werden, der die vom Parameter flagsangegebene Standardzuordnung überschreibt oder erweitert.

  ColPattern wird in folgenden Situationen angegeben:

  • Der Spaltenname im Rowset unterscheidet sich vom Element- oder Attributnamen, dem er zugeordnet ist. In diesem Fall wird ColPattern zur Identifizierung des XML-Element- und Attributnamens verwendet, dem die Rowsetspalte zugeordnet ist.

  • Der Spalte soll ein Metaeigenschaftsattribut zugeordnet werden. In diesem Fall wird ColPattern zur Identifizierung der Metaeigenschaft verwendet, der die Rowsetspalte zugeordnet ist. Weitere Informationen zur Verwendung von Metaeigenschaften finden Sie unter Angeben von Metaeigenschaften in OPENXML.

Sowohl der flags -Parameter als auch der ColPattern -Parameter sind optional. Wenn keine Zuordnung angegeben ist, wird eine attributzentrierte Zuordnung vorgenommen. Die attributzentrierte Zuordnung ist der Standardwert des flags -Parameters.

Attributzentrierte Zuordnung

Wenn der flags -Parameter in OPENXML auf 1 festgelegt ist (XML_ATTRIBUTES), wird die attributzentrierte Zuordnung durchgeführt. Wenn flags XML_ATTRIBUTES enthält, enthält oder verwendet das verfügbar gemachte Rowset Zeilen, in denen jedes XML-Element als eine Zeile dargestellt ist. Die XML-Attribute werden anhand ihres Namens den Attributen zugeordnet, die in SchemaDeclaration definiert sind, oder die in der WITH-Klausel über TableName bereitgestellt werden. Die Namensübereinstimmung bedeutet, dass XML-Attribute eines bestimmten Namens in einer Spalte im Rowset mit demselben Namen gespeichert werden.

Wenn sich der Spaltenname vom zugeordneten Attributnamen unterscheidet, muss der ColPattern -Parameter angegeben werden.

Wenn das XML-Attribut über einen Namespacequalifizierer verfügt, muss der Spaltenname im Rowset ebenfalls über einen Qualifizierer verfügen.

Elementzentrierte Zuordnung

Wenn der flags -Parameter in OPENXML auf 2 (XML_ELEMENTS) festgelegt ist, wird die elementzentrierte Zuordnung vorgenommen. Sie ist, abgesehen von den folgenden Unterschieden, mit der attributzentrierten Zuordnung vergleichbar:

• Die Namensübereinstimmung der Zuordnung (z. B. eine Spaltenzuordnung zu einem XML-Element desselben Namens) wählt die nicht komplexen Teilelemente aus, es sei denn, es wurde ein Muster auf Spaltenebene angegeben. Wenn das Teilelement komplex ist, weil es weitere Teilelemente enthält, wird die Spalte während des Abfrageprozesses auf NULL festgelegt. Attributwerte der Teilelemente werden dann ignoriert.

• Wenn mehrere Teilelemente denselben Namen besitzen, wird der erste Knoten zurückgeliefert.

Weitere Informationen

• sp_xml_preparedocument (Transact-SQL)
• sp_xml_removedocument (Transact-SQL)
• OPENXML (Transact-SQL)
• XML-Daten (SQL Server)