OPENXML (Transact-SQL)

Gilt für:SQL ServerAzure SQL-DatenbankAzure SQL Managed Instance

OPENXML stellt eine Rowsetsicht eines XML-Dokuments bereit. Da OPENXML ein Rowsetanbieter ist, kann OPENXML in Transact-SQL-Anweisungen überall dort verwendet werden, wo Rowsetanbieter, wie z. B. eine Tabelle, eine Sicht oder die OPENROWSET-Funktion, verwendet werden können.

Transact-SQL-Syntaxkonventionen

Syntax

OPENXML ( idoc int [ in ]
    , rowpattern nvarchar [ in ]
    , [ flags byte [ in ] ] )
[ WITH ( SchemaDeclaration | TableName ) ]

Argumente

idoc

Das Dokumenthandle für die interne Darstellung eines XML-Dokuments. Die interne Darstellung eines XML-Dokuments wird durch Aufrufen von sp_xml_preparedocument erstellt.

rowpattern

Das XPath-Muster zum Identifizieren der Knoten, die als Zeilen verarbeitet werden sollen. Die Knoten stammen aus dem XML-Dokument, dessen Handle im idoc-Parameter übergeben wird.

flags

Gibt die zwischen den XML-Daten und dem relationalen Rowset verwendete Zuordnung an und legt fest, wie die Überlaufspalte gefüllt wird. flags ist ein optionaler Eingabeparameter, wobei folgende Werte möglich sind.

SchemaDeclaration

Die Schemadefinition im folgenden Format: ColNameColType [ ColPattern | MetaProperty ] [ , ColNameColType [ ColPattern | MetaProperty ] ... ].

• ColName

  Der Name einer Spalte im Rowset.

• ColType

  Der SQL Server-Datentyp der Spalte im Rowset. Wenn der Spaltentyp vom zugrunde liegenden xml-Datentyp des Attributs abweicht, tritt eine erzwungene Typkoersion auf.

• ColPattern

  Ein optionales allgemeines XPath-Muster, das beschreibt, wie die XML-Knoten den Spalten zugeordnet werden sollten. Wenn ColPattern nicht angegeben ist, wird die Standardzuordnung (attributzentrierte oder elementzentrierte Zuordnung, wie von flags angegeben) verwendet.

  Das als ColPattern angegebene XPath-Muster wird zum Angeben der besonderen Eigenheit der Zuordnung (im Fall der attributzentrierten und elementzentrierten Zuordnung) verwendet, die die von flags angegebene Standardzuordnung überschreibt oder erweitert.

  Das allgemeine, als ColPattern angegebene XPath-Muster unterstützt auch die Metaeigenschaften.

• MetaProperty

  Eine der von OPENXML bereitgestellten Metaeigenschaften. Wenn MetaProperty angegeben ist, enthält die Spalte von der Metaeigenschaft bereitgestellte Informationen. Die Metaeigenschaften ermöglichen es Ihnen, Informationen (wie etwa die relative Position oder Namespaceinformationen) über XML-Knoten zu extrahieren. Diese Metaeigenschaften bieten mehr Informationen, als in der Textdarstellung zu sehen sind.

TableName

Der Tabellenname, der (anstelle von SchemaDeclaration) angegeben werden kann, wenn bereits eine Tabelle mit dem gewünschten Schema vorhanden ist und keine Spaltenmuster erforderlich sind.

Hinweise

Die WITH-Klausel stellt ein Rowsetformat bereit (und bei Bedarf auch zusätzliche Zuordnungsinformationen) und verwendet dazu SchemaDeclaration oder die Angabe eines vorhandenen Tabellennamens (TableName). Wenn die optionale WITH-Klausel nicht angegeben ist, werden die Ergebnisse in einem Rahmentabellenformat (edge) zurückgegeben. Rahmentabellen geben die differenzierte XML-Dokumentstruktur (z. B. Element-/Attributnamen, die Dokumenthierarchie, Namespaces, Verarbeitungsanweisungen usw.) in einer einzelnen Tabelle wieder.

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

Beispiele

A. Verwenden einer einfachen SELECT-Anweisung mit OPENXML

Im folgenden Beispiel wird eine interne Darstellung eines XML-Images mithilfe von sp_xml_preparedocument erstellt. Es wird dann eine SELECT-Anweisung, die einen OPENXML-Rowsetanbieter verwendet, für die interne Darstellung des XML-Dokuments ausgeführt.

Der flag-Wert wird auf 1 festgelegt. Dieser Wert gibt die Verwendung der attributzentrierten Zuordnung an. Daher werden die XML-Attribute den Spalten im Rowset zugeordnet. Mit rowpattern, das als /ROOT/Customer angegeben ist, werden die zu verarbeitenden <Customers>-Knoten identifiziert.

Der optionale ColPattern-Parameter (Spaltenmuster) wird nicht angegeben, da der Spaltenname mit den XML-Attributnamen übereinstimmt.

Der OPENXML-Rowsetanbieter erstellt ein zweispaltiges Rowset (CustomerID und ContactName), aus dem die SELECT-Anweisung die benötigten Spalten abruft (in diesem Fall alle Spalten).

DECLARE @idoc INT, @doc VARCHAR(1000);

SET @doc = '
<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 Gonzlez">
   <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 @idoc OUTPUT, @doc;

-- Execute a SELECT statement that uses the OPENXML rowset provider.
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer', 1) WITH (
        CustomerID VARCHAR(10),
        ContactName VARCHAR(20)
    );

Hier ist das Resultset.

CustomerID ContactName
---------- --------------------
VINET      Paul Henriot
LILAS      Carlos Gonzlez

Wenn dieselbe SELECT-Anweisung mit dem Wert 2 für flags ausgeführt wird, wodurch die elementzentrierte Zuordnung angezeigt wird, werden die Werte von CustomerID und ContactName für beide Kunden im XML-Dokument als NULL zurückgegeben, da das XML-Dokument keine Elemente mit dem Namen CustomerID oder ContactName enthält.

Hier ist das Resultset.

CustomerID ContactName
---------- -----------
NULL       NULL
NULL       NULL

B. Angeben von ColPattern für die Zuordnung zwischen Spalten und den XML-Attributen

Die folgende Abfrage gibt die Attribute für die Kunden-ID, das Bestelldatum, die Produkt-ID und die Menge aus dem XML-Dokument zurück. rowpattern identifiziert die <OrderDetails>-Elemente. ProductID und Quantity sind die Attribute des <OrderDetails>-Elements. OrderID, CustomerID und OrderDate sind jedoch die Attribute des übergeordneten Elements (<Orders>).

Das optionale ColPattern wird für die folgenden Zuordnungen angegeben:

• Die Spalten OrderID, CustomerID und OrderDate des Rowsets werden den Attributen des übergeordneten Knotens der Knoten zugeordnet, die von rowpattern im XML-Dokument identifiziert werden.

• Die ProdID-Spalte des Rowsets wird dem ProductID-Attribut und die Qty-Spalte des Rowsets wird dem Quantity-Attribut der Knoten zugeordnet, die in rowpattern identifiziert sind.

Zwar ist durch den flags-Parameter die elementzentrierte Zuordnung angegeben, die im ColPattern-Parameter angegebene Zuordnung überschreibt jedoch diese Zuordnung.

DECLARE @idoc INT, @doc VARCHAR(1000);

SET @doc = '
<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 Gonzlez">v
   <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 @idoc OUTPUT, @doc;

-- SELECT stmt using OPENXML rowset provider
SELECT *
FROM OPENXML(@idoc, '/ROOT/Customer/Order/OrderDetail', 2) WITH (
        OrderID INT '../@OrderID',
        CustomerID VARCHAR(10) '../@CustomerID',
        OrderDate DATETIME '../@OrderDate',
        ProdID INT '@ProductID',
        Qty INT '@Quantity'
    );

Hier ist das Resultset.

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

C. Abrufen von Ergebnissen in einem Rahmentabellenformat

Das XML-Dokument in diesem Beispiel setzt sich aus den Elementen <Customers>, <Orders> und <Order_0020_Details> zusammen. Zunächst wird sp_xml_preparedocument aufgerufen, um ein Dokumenthandle abzurufen. Dieses Dokumenthandle wird an OPENXML übergeben.

In der OPENXML-Anweisung identifiziert rowpattern (/ROOT/Customers) die zu verarbeitenden <Customers>-Knoten. Da die WITH-Klausel nicht angegeben ist, gibt OPENXML das Rowset in einem Rahmentabellenformat (edge) zurück.

Schließlich ruft die SELECT-Anweisung alle Spalten in der Rahmentabelle (edge) ab.

DECLARE @idoc INT, @doc VARCHAR(1000);

SET @doc = '
<ROOT>
<Customers CustomerID="VINET" ContactName="Paul Henriot">
   <Orders CustomerID="VINET" EmployeeID="5" OrderDate=
           "1996-07-04T00:00:00">
      <Order_x0020_Details OrderID="10248" ProductID="11" Quantity="12"/>
      <Order_x0020_Details OrderID="10248" ProductID="42" Quantity="10"/>
   </Orders>
</Customers>
<Customers CustomerID="LILAS" ContactName="Carlos Gonzlez">
   <Orders CustomerID="LILAS" EmployeeID="3" OrderDate=
           "1996-08-16T00:00:00">
      <Order_x0020_Details OrderID="10283" ProductID="72" Quantity="3"/>
   </Orders>
</Customers>
</ROOT>';

--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc;

-- SELECT statement that uses the OPENXML rowset provider.
SELECT * FROM OPENXML(@idoc, '/ROOT/Customers')

EXEC sp_xml_removedocument @idoc;

Weitere Informationen

• Beispiele: Verwenden von OPENXML