OPENXML (Transact-SQL)

  • Artikel

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

OPENXML stellt eine Rowsetansicht über ein XML-Dokument bereit. Da OPENXML es sich um einen Rowsetanbieter handelt, kann in Transact-SQL-Anweisungen verwendet werden, OPENXML in denen Rowsetanbieter wie eine Tabelle, Ansicht oder die OPENROWSET Funktion angezeigt 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.

Bytewert Beschreibung
0 Standardmäßig wird die attribute-centric Zuordnung verwendet.
1 Verwenden Sie die attribute-centric Zuordnung. Kann nicht mit XML_ELEMENTS kombiniert werden. In diesem Fall attribute-centric wird zuerst die Zuordnung angewendet. element-centric Als Nächstes wird die Zuordnung für alle verbleibenden Spalten angewendet.
2 Verwenden Sie die element-centric Zuordnung. Kann nicht mit XML_ATTRIBUTES kombiniert werden. In diesem Fall element-centric wird zuerst die Zuordnung angewendet. attribute-centric Als Nächstes wird die Zuordnung für alle verbleibenden Spalten angewendet.
8 Kann kombiniert werden (logisches ODER) mit XML_ATTRIBUTES oder XML_ELEMENTS. Im Abrufkontext zeigt dieses Flag an, dass die verwendeten Daten nicht in die Überlaufeigenschaft @mp:xmltext kopiert werden sollen.

SchemaDeclaration

Eine Schemadefinition des Formulars: 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, erfolgt die Standardzuordnung (attribute-centric oder element-centric Zuordnung gemäß Der Angabe durch Flags).

    Das als ColPattern angegebene XPath-Muster wird verwendet, um die besondere Art der Zuordnung (für attribute-centric und element-centric Zuordnung) anzugeben, die die von Flags angegebene Standardzuordnung überschreibt oder verbessert.

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

  • MetaProperty

    Eine der Metaeigenschaften, die von OPENXML. 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 (und zusätzliche Zuordnungsinformationen nach Bedarf) mithilfe von SchemaDeclaration oder angeben eines vorhandenen TableName bereit. Wenn die optionale WITH Klausel nicht angegeben ist, werden die Ergebnisse in einem Randtabellenformat 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.

Spaltenname Datentyp BESCHREIBUNG
id bigint Die eindeutige ID des Dokumentknotens.

Das Stammelement weist einen ID-Wert 0auf. Die negativen ID-Werte sind reserviert.
parentid bigint Identifiziert das übergeordnete Element des Knotens. Das durch diese ID identifizierte übergeordnete Element ist nicht unbedingt das übergeordnete Element, sondern hängt vom Knoten ab nodetype , dessen übergeordnetes Element durch diese ID identifiziert wird. Wenn der Knoten beispielsweise ein Textknoten ist, kann es sich bei dem übergeordneten Knoten um einen Attributknoten handeln.

Wenn sich der Knoten auf der obersten Ebene im XML-Dokument befindet, ist NULLer ParentID .
nodetype int Identifiziert den Knotentyp. Dieser Wert ist eine ganze Zahl, die der XML-DOM-Knotentypnummerierung entspricht. Die Knotentypen sind Folgende:

1 = Elementknoten
2 = Attributknoten
3 = Textknoten
localname nvarchar Gibt den lokalen Namen des Elements oder Attributs an. NULL wenn das DOM-Objekt keinen Namen hat.
prefix nvarchar Das Namespacepräfix des Knotennamens.
namespaceuri nvarchar Der Namespace-URI des Knotens. Wenn der Wert lautet, ist NULLkein Namespace vorhanden.
data type nvarchar Der tatsächliche Datentyp des Elements oder der Attributzeile, andernfalls ist NULL. Der Datentyp wird aus der Inline-DTD (Document Type Definition) oder aus dem Inlineschema abgeleitet.
prev bigint Die XML-ID des vorhergehenden gleichgeordneten Elements. NULL wenn kein direktes gleichgeordnetes Element vorhanden ist.
text ntext Enthält den Attributwert oder den Elementinhalt in Textform (oder wenn NULL der Randtabelleneintrag keinen Wert erfordert).

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 Zuordnung an attribute-centric . 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 sehen Sie das Ergebnis.

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

Wenn dieselbe SELECT Anweisung mit flags ausgeführt wird, die 2auf die Zuordnung festgelegt sind, element-centric werden die Werte von CustomerID und ContactName für beide Kunden im XML-Dokument zurückgegeben, NULLda keine Elemente benannt CustomerID oder ContactName im XML-Dokument vorhanden sind.

Hier sehen Sie das Ergebnis.

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.

Obwohl die Zuordnung durch den Flags-Parameter angegeben wird, überschreibt die element-centric in ColPattern angegebene Zuordnung 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 sehen Sie das Ergebnis.

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 wird, OPENXML wird das Rowset in einem Randtabellenformat zurückgegeben.

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;

Zugehöriger Inhalt