OPENXML (Transact-SQL)
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.
.gif "Themenlink (Symbol)")
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 durch diese ID identifizierte übergeordnete Element ist nicht notwendigerweise das übergeordnete Element; dies hängt vielmehr vom Knotentyp (NodeType) des Knotens ab, dessen übergeordnetes Element nicht von dieser ID identifiziert wird. Wenn es sich beispielsweise bei dem Knoten um einen Textknoten handelt, kann das übergeordnete Element 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. |
Präfix |
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-Abbilds 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