XML mit openxml importieren

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
./@Attributname Gibt an, dass das Attribut des aktuellen Knotens den Namen Attributname hat
./Child-Objektname Gibt die Child-Objekte des aktuellen Knotens an, die Elemente mit dem Namen Child-Objektname 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.

Jede Übereinstimmung des ersten xpath_Abfrage-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_Abfrage-Argument ist /inventory/product und es gibt zwei <product>-Elemente in XML, daher werden zwei Zeilen durch diese Abfrage generiert.

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

Sie können mit openxml eine Edge-Tabelle generieren, eine Tabelle, die eine Zeile für jedes Element im XML-Dokument enthält. Das Generieren einer Edge-Tabelle ist nützlich, 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
...	...	...	...

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;

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

XML mit openxml importieren

XPath-Ausdrücke verwenden

Eine Ergebnismenge mit openxml generieren

Mit openxml eine Edge-Tabelle generieren

openxml mit xp_read_file verwenden

XML in einer Spalte abfragen

Siehe auch