Die Prozedur "openxml" 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:

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

Eine vollständige Liste der von "openxml" unterstützten XPath-Syntax finden Sie unter openxml-Systemprozedur.

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

Eine Ergebnismenge mit openxml generieren

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:

Weitere Hinweise finden Sie unter openxml-Systemprozedur.

Mit openxml eine Edge-Tabelle generieren

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 folgende SQL-Anweisung erstellt die Variable x, die ein XML-Dokument enthält. Das von der Abfrage generierte XML hat ein Wurzelelement namens <root>, das mit der XMLELEMENT-Funktion generiert wird, und es werden Elemente für jede Spalte in den Tabellen "Employees", "SalesOrders" und "Customers" unter Verwendung von FOR XML AUTO generiert, wobei der ELEMENTS-Modifizierer angegeben ist.

Hinweise zur XMLELEMENT-Funktion finden Sie unter XMLELEMENT-Funktion [Zeichenfolge].

Hinweise zu FOR XML AUTO finden Sie unter FOR XML AUTO verwenden.

CREATE VARIABLE x XML;
SET x=(SELECT XMLELEMENT( NAME root,
         (SELECT * FROM Employees
         KEY JOIN SalesOrders
         KEY JOIN Customers
         FOR XML AUTO, ELEMENTS)));
SELECT x;

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>299</EmployeeID>
  <ManagerID>902</ManagerID>
  <Surname>Overbey</Surname>
  <GivenName>Rollin</GivenName>
  <DepartmentID>200</DepartmentID>
  <Street>191 Companion Ct.</Street>
  <City>Kanata</City>
  <State>CA</State>
  <Country>USA</Country>
  <PostalCode>94608</PostalCode>
  <Phone>5105557255</Phone>
  <Status>A</Status>
  <SocialSecurityNumber>025487133</SocialSecurityNumber>
  <Salary>39300.000</Salary>
  <StartDate>1987-02-19</StartDate>
  <BirthDate>1964-03-15</BirthDate>
  <BenefitHealthInsurance>Y</BenefitHealthInsurance>
  <BenefitLifeInsurance>Y</BenefitLifeInsurance>
  <BenefitDayCare>N</BenefitDayCare>
  <Sex>M</Sex>
  <SalesOrders>
  <ID>2001</ID>
  <CustomerID>101</CustomerID>
  <OrderDate>2000-03-16</OrderDate>
  <FinancialCode>r1</FinancialCode>
  <Region>Eastern</Region>
  <SalesRepresentative>299</SalesRepresentative>
    <Customers>
    <ID>101</ID>
    <Surname>Devlin</Surname>
    <GivenName>Michael</GivenName>
    <Street>114 Pioneer Avenue</Street>
    <City>Kingston</City>
    <State>NJ</State>
    <PostalCode>07070</PostalCode>
    <Phone>2015558966</Phone>
    <CompanyName>The Power Group</CompanyName>
    </Customers>
  </SalesOrders>
</Employees>
...

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.

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.

openxml mit xp_read_file verwenden

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:\inventory.xml hat folgenden Inhalt:

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

CREATE VARIABLE x XML;
SELECT xp_read_file( 'c:\\inventory.xml' ) 
 INTO x;
SELECT * FROM openxml( x, '//*' )
 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 test (ManagerID INT, Reports XML);
INSERT INTO test
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 test
ORDER BY ManagerID;

Diese Abfrage erzeugt das folgende Ergebnis:

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

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

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

<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 test, LATERAL( openxml( test.Reports, '//e' )
WITH (EmployeeID INT '.') ) DerivedTable
ORDER BY ManagerID, EmployeeID;

Diese Abfrage erzeugt das folgende Ergebnis:

Weitere Hinweise zu lateral abgeleiteten Tabellen finden Sie unter FROM-Klausel.