Importieren von XML mit dem OPENXML-Operator

Der OPENXML-Operator wird in der FROM-Klausel einer Abfrage benutzt, um eine Ergebnismenge aus einem XML-Dokument zu generieren. OPENXML verwendet eine Teilmenge der XPath-Abfragesprache, um Knoten in einem XML-Dokument auszuwählen.

XPath-Ausdrücke verwenden

Wenn Sie OPENXML verwenden, wird das XML-Dokument syntaktisch analysiert und das Ergebnis wird in einer Baumstruktur dargestellt. Der Baum besteht aus Knoten. XPath-Ausdrücke werden verwendet, um Knoten im Baum auszuwählen. Die folgende Liste beschreibt einige häufig benutzte XPath-Ausdrücke:

/ Gibt den Stammknoten des XML-Dokuments an
// Gibt alle dem Knoten untergeordneten Knoten an, einschließlich des Stammknotens
. (einzelner Punkt) Gibt den aktuellen Knoten des XML-Dokuments an
.// Gibt alle dem aktuellen Knoten untergeordnete Knoten an, einschließlich des aktuellen Knotens
.. Gibt den dem aktuellen Knoten übergeordeten Knoten an
./@attributename Gibt an, dass das Attribut des aktuellen Knotens den Namen attributename hat
./childname Gibt die Child-Objekte des aktuellen Knotens an, die Elemente mit dem Namen childname sind

Nehmen wir das folgende XML-Dokument:

<inventory>
  <product ID="301" size="Medium">Tee Shirt
    <quantity>54</quantity>
  </product>
  <product ID="302" size="One Size fits all">Tee Shirt
    <quantity>75</quantity>
  </product>
  <product ID="400" size="One Size fits all">Baseball Cap
    <quantity>112</quantity>
  </product>
</inventory>

Das Element <inventory> ist der Wurzelknoten. Sie können es referenzieren, indem Sie den folgenden XPath-Ausdruck verwenden:

/inventory

Nehmen wir an, der aktuelle Knoten ist ein <quantity>-Element. Sie können sich auf diesen Knoten beziehen, indem Sie den folgenden XPath-Ausdruck verwenden:

Um alle <product>-Elemente zu finden, die Child-Objekte des <inventory>-Elements sind, verwenden Sie den folgenden XPath-Ausdruck:

/inventory/product

Wenn der aktuelle Knoten ein <product>-Element ist und Sie sich auf das Größenattribut beziehen wollen, verwenden Sie den folgenden XPath-Ausdruck:

./@size

Informationen über die Abfragesprache XPath finden Sie unter [external link] http://www.w3.org/TR/xpath.

Ergebnismengen mit OPENXML generieren

Jede Übereinstimmung des ersten xpath-query-Arguments mit OPENXML generiert eine Zeile in der Ergebnismenge. Die WITH-Klausel gibt das Schema der Ergebnismenge an und wie der Wert für die einzelnen Spalten in der Ergebnismenge gefunden wird. Nehmen wir als Beispiel folgende Abfrage:

SELECT * FROM OPENXML(
'<inventory>
   <product>Tee Shirt
     <quantity>54</quantity>
     <color>Orange</color>
   </product>
   <product>Baseball Cap
     <quantity>112</quantity>
     <color>Black</color>
   </product>
</inventory>',
'/inventory/product' )
WITH ( Name CHAR (25) './text()',
       Quantity CHAR(3) 'quantity',
       Color CHAR(20) 'color');

Das erste xpath-query-Argument ist /inventory/product und es gibt zwei <product>-Elemente in XML, daher generiert diese Abfrage zwei Zeilen.

Die WITH -Klausel legt fest, dass es drei Spalten gibt: Name, Quantity und Color. Die Werte für diese Spalten stammen von den <product>, <quantity>- und <color>-Elementen. Die oben stehende Abfrage generiert das folgende Ergebnis:

Name	Quantity	Color
Tee Shirt	54	Orange
Baseball Cap	112	Black

Mit OPENXML eine Edge-Tabelle generieren

Sie können mit dem OPENXML-Operator eine Edge-Tabelle generieren, eine Tabelle, die eine Zeile für jedes Element im XML-Dokument enthält. Sie können eine Edge-Tabelle generieren, wenn Sie die Daten unter Verwendung von SQL abfragen wollen.

Die folgenden SQL-Anweisungen erstellen eine Tabelle mit nur einem XML-Dokument. Das von der Abfrage generierte XML enthält ein Wurzelelement namens <root>, das mit der XMLELEMENT-Funktion generiert wird. Außerdem werden unter Verwendung von FOR XML AUTO mit dem ELEMENTS-Modifizierer Elemente für jede Spalte in den Tabellen "Employees", "SalesOrders" und "Customers" generiert.

CREATE TABLE IF NOT EXISTS xmldata (xmldoc XML);
INSERT INTO xmldata WITH AUTO NAME
    SELECT XMLELEMENT( NAME root,
         (SELECT EmployeeID, Employees.GivenName, Employees.Surname,
           Customers.ID, Customers.GivenName, Customers.Surname, Customers.Phone, CompanyName,
           SalesOrders.ID, OrderDate, Region
         FROM Employees
         KEY JOIN SalesOrders        
         KEY JOIN Customers
         ORDER BY EmployeeID, Customers.ID, SalesOrders.ID
         FOR XML AUTO, ELEMENTS)) AS xmldoc;
SELECT xmldoc FROM xmldata;

Der generierte XML-Code sieht folgendermaßen aus (das Ergebnis wurde formatiert, damit es leichter lesbar ist—das von der Abfrage zurückgegebene Ergebnis ist eine kontinuierliche Zeichenfolge):

<root>
  <Employees>
    <EmployeeID>129</EmployeeID>
    <GivenName>Philip</GivenName>
    <Surname>Chin</Surname>
     
    <Customers>
      <ID>101</ID>
      <GivenName>Michaels</GivenName>
      <Surname>Devlin</Surname>
      <Phone>2015558966</Phone>
      <CompanyName>The Power Group</CompanyName>
        <SalesOrders>
          <ID>2560</ID>
          <OrderDate>2001-03-16</OrderDate>
          <Region>Eastern</Region>
        </SalesOrders>
    </Customers>
      
    <Customers>
      <ID>103</ID>
      <GivenName>Erin</GivenName>
      <Surname>Niedringhaus</Surname>
      <Phone>2155556513</Phone>
      <CompanyName>Darling Associates</CompanyName>
        <SalesOrders>
          <ID>2451</ID>
          <OrderDate>2000-12-15</OrderDate>
          <Region>Eastern</Region>
        </SalesOrders>
    </Customers>
      
    <Customers>
      <ID>104</ID>
      <GivenName>Meghan</GivenName>
      <Surname>Mason</Surname>
      <Phone>6155555463</Phone>
      <CompanyName>P.S.C.</CompanyName>
        <SalesOrders>
          <ID>2331</ID>
          <OrderDate>2000-09-17</OrderDate>
          <Region>South</Region>
        </SalesOrders>
        
        <SalesOrders>
          <ID>2342</ID>
          <OrderDate>2000-09-28</OrderDate>
          <Region>South</Region>
        </SalesOrders>
    </Customers>
    ...
  </Employees>
  ...
  <Employees>
  ...
  </Employees>
</root>

Die folgende Abfrage verwendet den XPath-Ausdruck "descendant-or-self" (//*), um jedes Element im oben stehenden XML-Dokument zu vergleichen, und bei jedem Element wird die ID-Meta-Eigenschaft verwendet, um eine ID für den Knoten zu finden, wobei der übergeordnete XPath-Ausdruck (../) mit der ID-Meta-Eigenschaft verwendet wird, um den übergeordneten Knoten zu erhalten. Die Meta-Eigenschaft "localname" wird verwendet, um den Namen der einzelnen Elemente zu erhalten. Bei Meta-Eigenschaftsnamen wird die Groß- und Kleinschreibung berücksichtigt. Daher können ID oder LOCALNAME nicht als Meta-Eigenschaftsnamen benutzt werden.

CREATE OR REPLACE VARIABLE x XML;
SELECT xmldoc INTO x FROM xmldata;

SELECT * 
FROM OPENXML( x, '//*' )
WITH (ID INT '@mp:id',
       parent INT '../@mp:id',
       name CHAR(25) '@mp:localname',
       text LONG VARCHAR 'text()' )
ORDER BY ID;

Die durch diese Abfrage generierte Ergebnismenge zeigt die ID jedes Knotens, die ID des übergeordneten Knotens sowie den Namen und Inhalt für jedes Element im XML-Dokument.

ID	parent	name	text
5	(NULL)	root	(NULL)
16	5	Employees	(NULL)
28	16	EmployeeID	129
55	16	GivenName	Phillip
82	16	Surname	Chin
...	...	...	...

Verwenden von OPENXML mit xp_read_file

Bisher wurde XML benutzt, das mit einer Prozedur wie XMLELEMENT generiert wurde. Sie können XML auch aus einer Datei lesen und mit der Prozedur "xp_read_file" syntaktisch analysieren. Angenommen, die Datei c:\temp\inventory.xml wurde mithilfe der unten stehenden Abfrage geschrieben.

SELECT xp_write_file( 'c:\\temp\\inventory.xml',
'<inventory>
   <product>Tee Shirt
      <quantity>54</quantity>
      <color>Orange</color>
   </product>
   <product>Baseball Cap
      <quantity>112</quantity>
      <color>Black</color>
   </product>
</inventory>'
);

Sie können die folgende Anweisung benutzen, um XML in der Datei zu lesen und syntaktisch zu analysieren:

SELECT * 
FROM OPENXML( xp_read_file( 'c:\\temp\\inventory.xml' ),
  '//*' )
WITH (ID INT '@mp:id',
       parent INT '../@mp:id',
       name CHAR(128) '@mp:localname',
       text LONG VARCHAR 'text()' )
ORDER BY ID;

XML in einer Spalte abfragen

Wenn Sie eine Tabelle mit einer Spalte haben, die XML enthält, können Sie OPENXML verwenden, um alle XML-Werte in der Spalte auf einmal abzufragen. Das wird erreicht, indem Sie eine lateral abgeleitete Tabelle verwenden.

Die folgenden Anweisungen erstellen eine Tabelle mit zwei Spalten, "ManagerID" und "Reports". Die Spalte "Reports" enthält XML-Daten, die von der Tabelle "Employees" generiert wurden.

CREATE TABLE IF NOT EXISTS xmltest (ManagerID INT, Reports XML);
INSERT INTO xmltest
  SELECT ManagerID, XMLELEMENT( NAME reports,
    XMLAGG( XMLELEMENT( NAME e, EmployeeID))) 
  FROM Employees
  GROUP BY ManagerID;

Führen Sie die folgende Abfrage aus, um die Daten in der Testtabelle anzuzeigen:

SELECT *
FROM xmltest
ORDER BY ManagerID;

Diese Abfrage erzeugt das folgende Ergebnis:

ManagerID

Reports

501

<reports>
 <e>102</e>
 <e>105</e>
 <e>160</e>
 <e>243</e>
 ...
</reports>

703

<reports>
 <e>191</e> 
 <e>750</e>
 <e>868</e>
 <e>921</e>
 ...
</reports>

902

<reports>
 <e>129</e>
 <e>195</e>
 <e>299</e>
 <e>467</e>
 ...
</reports>

1293

<reports>
 <e>148</e>
 <e>390</e>
 <e>586</e>
 <e>757</e>
 ...
</reports>

...

Die folgende Abfrage verwendet eine lateral abgeleitete Tabelle, um eine Ergebnismenge mit zwei Spalten zu generieren: Eine, die die ID für jeden Vorgesetzten auflistet, und eine, die die ID für jeden Mitarbeiter auflistet, der diesem Vorgesetzten zugeordnet ist:

SELECT ManagerID, EmployeeID
FROM xmltest, LATERAL( OPENXML( xmltest.Reports, '//e' )
WITH (EmployeeID INT '.') ) DerivedTable
ORDER BY ManagerID, EmployeeID;

Diese Abfrage erzeugt das folgende Ergebnis:

ManagerID	EmployeeID
501	102
501	105
501	160
501	243
...	...

Siehe auch

Kommentieren Sie diese Seite in DocCommentXchange.