OPENXML (Transact-SQL)
Gilt für: SQL Server
Azure SQL-Datenbank
Azure SQL verwaltete Instanz
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 ) ]
Hinweis
Informationen zum Anzeigen der Transact-SQL-Syntax für SQL Server 2014 (12.x) und früher finden Sie unter Dokumentation zu früheren Versionen.
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
oderelement-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
undelement-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 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 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 NULL er ParentID . |
nodetype |
int | Identifiziert den Knotentyp. Dieser Wert ist eine ganze Zahl, die der XML-DOM-Knotentypnummerierung entspricht. Die Knotentypen sind Folgende:1 = Elementknoten2 = Attributknoten3 = 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 NULL kein 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 ist das Resultset.
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzlez
Wenn dieselbe SELECT
Anweisung mit flags ausgeführt wird, die 2
auf die Zuordnung festgelegt sind, element-centric
werden die Werte von CustomerID
und ContactName
für beide Kunden im XML-Dokument zurückgegeben, NULL
da keine Elemente benannt CustomerID
oder ContactName
im XML-Dokument vorhanden sind.
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
undOrderDate
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 demProductID
-Attribut und dieQty
-Spalte des Rowsets wird demQuantity
-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 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 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;