Freigeben über

OPENXML (Transact-SQL)

  • Artikel

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

Themenlink (Symbol)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, das zum Identifizieren der Knoten (im XML-Dokument, dessen Handle im idoc-Parameter übergeben wird) verwendet wird, die als Zeilen verarbeitet werden sollen.

  • flags
    Gibt die Zuordnung an, die zwischen den XML-Daten und dem relationalen Rowset verwendet werden soll, und legt fest, wie die Überlaufspalte (Spill-over-Spalte) gefüllt werden soll. flags ist ein optionaler Eingabeparameter und kann einen der folgenden Werte haben.

    Bytewert Beschreibung

    0

    Attributzentrierte Zuordnung als Standard verwenden.

    1

    Verwenden der attributzentrierten Zuordnung. Kann mit XML_ELEMENTS kombiniert werden. In diesem Fall wird zuerst die attributzentrierte Zuordnung und anschließend die elementzentrierte Zuordnung auf alle noch nicht verarbeiteten Spalten angewendet.

    2

    Verwenden der elementzentrierten Zuordnung. Kann mit XML_ATTRIBUTES kombiniert werden. In diesem Fall wird zuerst die attributzentrierte Zuordnung und anschließend die elementzentrierte Zuordnung auf alle noch nicht verarbeiteten Spalten angewendet.

    8

    Kann mit XML_ATTRIBUTES oder XML_ELEMENTS kombiniert werden (logisches OR). Im Kontext des Abrufens zeigt dieses Flag an, dass die verwendeten Daten nicht in die Überlaufeigenschaft @mp:xmltext kopiert werden sollen.

  • SchemaDeclaration
    Die Schemadefinition in der folgenden Form: ColNameColType [ColPattern | MetaProperty] [**,**ColNameColType [ColPattern | MetaProperty]...]

    • ColName
      Der Name einer Spalte des Rowsets.
    • ColType
      Der SQL Server-Datentyp der Spalte des Rowsets. 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 in 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 mit 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. Dies bietet mehr Informationen, als in der Textdarstellung zu sehen sind.
  • TableName
    Der Tabellenname, der (statt 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 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 beschrieben.

Spaltenname Datentyp Beschreibung

id

bigint

Die eindeutige ID des Dokumentknotens.

Das Stammelement weist den ID-Wert 0 auf. Die negativen ID-Werte sind reserviert.

parentid

bigint

Identifiziert das übergeordnete Element des Knotens. Das von dieser ID identifizierte übergeordnete Objekt ist nicht notwendigerweise das übergeordnete Element. Dieses ist vom NodeType des Knotens abhängig, dessen übergeordnetes Objekt von dieser ID identifiziert wird. Wenn es sich bei dem Knoten beispielsweise um einen Textknoten handelt, kann das übergeordnete Objekt ein Attributknoten sein.

Wenn sich der Knoten auf der obersten Ebene im XML-Dokument befindet, ist ParentID gleich NULL.

nodetype

int

Identifiziert den Knotentyp. Eine ganze Zahl, die der XML DOM-Knotentypnummer (Document Object Model) entspricht.

Die Knotentypen sind Folgende:

1 = Elementknoten

2 = Attributknoten

3 = Textknoten

localname

nvarchar

Gibt den lokalen Namen des Elements oder Attributs an. Ist NULL, wenn das DOM-Objekt keinen Namen hat.

prefix

nvarchar

Das Namespacepräfix des Knotennamens.

namespaceuri

nvarchar

Der Namespace-URI (Universal Resource Identifier) des Knotens. Ist der Wert NULL, ist kein Namespace vorhanden.

datatype

nvarchar

Der eigentliche Datentyp der Element- oder Attributzeile, der andernfalls NULL ist. Der Datentyp wird aus der Inline-DTD (Document Type Definition) oder aus dem Inlineschema abgeleitet.

prev

bigint

Die XML-ID des vorhergehenden gleichgeordneten Elements. Ist NULL, wenn kein direktes vorhergehendes gleichgeordnetes Element vorhanden ist.

text

ntext

Enthält den Attributwert oder den Elementinhalt in Textform (oder ist NULL, wenn der Rahmentabelleneintrag keinen Wert benötigt).

Beispiele

A. Verwenden einer einfachen SELECT-Anweisung mit OPENXML

Im folgenden Beispiel wird eine interne Darstellung eines XML-Abbildes 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. Dies zeigt 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
DECLARE @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))

Dies 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, was die elementzentrierte Zuordnung anzeigt, werden die Werte von CustomerID und ContactName für beide Kunden im XML-Dokument als NULL zurückgegeben, da die <Customers>-Elemente keine Unterelemente haben.

Dies 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>).

Der optionale ColPattern-Wert ist angegeben. Dies zeigt Folgendes an:

  • 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
DECLARE @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">
   <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')

Dies 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. Ausgeben von Ergebnissen im 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 zu erhalten. 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, wird das Rowset von OPENXML im Rahmentabellenformat zurückgegeben.

Schließlich ruft die SELECT-Anweisung alle Spalten in der ****Rahmentabelle ab.

DECLARE @idoc int
DECLARE @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

Siehe auch

Andere Ressourcen

Verwenden von OPENXML

Hilfe und Informationen

Informationsquellen für SQL Server 2005