openxml-Systemprozedur

openxml( xml-data,
 xpath [, flags [, namespaces ] ] )
WITH ( column-name column-type [ xpath ],... )

openxml( { USING FILE | USING VALUE }  xml-data,
 xpath [, flags [, namespaces ] ] )
WITH ( column-name column-type [ xpath ],... ) 
[ OPTION ( scan-option ) ]
[ AS ] correlation-name

scan-option :
ENCODING encoding
| BYTE ORDER MARK { ON | OFF }

WITH-Klausel Gibt das Schema der Ergebnismenge an und wie der Wert für jede einzelne Spalte in der Ergebnismenge gefunden wird. xpath-Argumente in der WITH-Klausel werden entsprechend den Übereinstimmungen mit xpath im zweiten Argument zugeordnet. Wenn ein WITH-Klausel-Ausdruck mehr als einen Knoten findet, wird nur der erste Knoten in der Dokumentordnung verwendet. Falls es sich bei dem Knoten nicht um einen Textknoten handelt, wird das Ergebnis gefunden, indem alle untergeordneten Elemente des Textknotens angehängt werden. Wenn ein WITH-Klausel-Ausdruck mit keinem der Knoten übereinstimmt, ist die Spalte für die betreffende Zeile NULL.
Die xpath-Argumente in der WITH-Klausel können Literalzeichenfolgen oder Variablen sein.

Die Syntax der openxml WITH-Klausel ähnelt der Syntax für SELECT aus einer gespeicherten Prozedur.

USING FILE | USING VALUE Verwenden Sie die USING FILE-Klausel, um Daten aus einer Datei zu laden. DBA oder READFILE-Berechtigung ist erforderlich, um die USING FILE-Klausel verwenden zu können.

Verwenden Sie die USING VALUE-Klausel, um Daten aus einem Ausdruck des Typs CHAR, NCHAR, BINARY oder LONG BINARY bzw. einer BLOB-Zeichenfolge zu laden.

xml-data Die XML-Daten, auf denen die Ergebnismenge basiert. Dies kann ein beliebiger Zeichenfolgenausdruck sein, wie etwa eine Konstante, eine Variable oder eine Spalte.
Die xml-data werden direkt in der NCHAR-Kodierung syntaktisch analysiert, wenn die Ausgabe NCHAR-Spalten enthält. Die Argumente xpath und namespaces werden ebenfalls in die NCHAR-Kodierung konvertiert und syntaktisch analysiert.
xpath Eine Zeichenfolge, die eine XPath-Abfrage enthält. Mit XPath können Sie Muster angeben, die die Struktur des abzufragenden XML-Dokumentes beschreiben. Das in diesem Argument enthaltene XPath-Muster wählt die Knoten aus dem XML-Dokument. Jeder Knoten, der mit der XPath-Abfrage im zweiten xpath-Argument übereinstimmt, erzeugt eine Zeile in der Tabelle.
Meta-Eigenschaften können nur in xpath-Argumenten der WITH-Klausel angegeben werden. Auf eine Meta-Eigenschaft wird innerhalb einer XPath-Abfrage so zugegriffen, als handele es sich um ein Attribut. Wenn namespaces nicht angegeben wird, bindet das System standardmäßig das Präfix "mp" an den Uniform Resource Identifier (URI) urn:ianywhere-com:sa-xpath-metaprop. Wenn namespaces angegeben wird, muss dieser URI an "mp" oder an ein anderes Präfix gebunden werden, damit auf die Meta-Eigenschaften der Abfrage zugegriffen werden kann. Meta-Eigenschaften berücksichtigen die Groß- und Kleinschreibung. Die openxml-Anweisung unterstützt die folgenden Meta-Eigenschaften:
- @mp:id Gibt eine ID für einen Knoten zurück, der innerhalb des XML-Dokumentes eindeutig ist. Die ID für einen bestimmten Knoten in einem bestimmten Dokument kann sich ändern, falls der Datenbankserver neu gestartet wird. Der Wert dieser Meta-Eigenschaft steigt mit der Dokumentordnung.
- @mp:localname Gibt den lokalen Teil des Knotennamens zurück oder NULL, falls der Knoten keinen Namen trägt.
- @mp:prefix Gibt den Präfixteil des Knotennamens zurück oder NULL, falls der Knoten keinen Namen trägt oder falls der Name kein Präfix hat.
- @mp:namespaceuri Gibt den URI des Namespaces zurück, dem der Knoten angehört, bzw. NULL, falls der Knoten sich in keinem Namespace befindet.
- @mp:xmltext Gibt eine Unterstruktur des XML-Dokuments in XML-Form zurück. Wenn Sie z.B. einen internen Knoten suchen, können Sie diese Meta-Eigenschaft dafür benutzen, eine XML-Zeichenfolge zurückzugegeben, und nicht verkettete Werte der untergeordneten Textknoten.

flags Gibt die Zuordnung an, die zwischen den XML-Daten und der Ergebnismenge verwendet werden sollte, wenn in der WITH-Klausel keine XPath-Abfrage angegeben wird. Wenn der Parameter flags nicht angegeben wird, ist das Standardverhalten, in der Ergebnismenge Attribute und Spalten zuzuordnen. Der Parameter flags kann einen der folgenden Werte annehmen:

Wert	Beschreibung
1	XML-Attribute werden in der Ergebnismenge Spalten zugeordnet (Standardeinstellung).
2	XML-Elemente werden in der Ergebnismenge Spalten zugeordnet.

namespace-declaration Ein XML-Dokument. Die bekannten Namespaces ("in-scope namespaces") für die Abfrage werden aus dem Wurzelelement des Dokumentes entnommen. Wenn Namespaces angegeben werden, müssen Sie das Argument flags einbeziehen, auch wenn alle xpath-Argumente angegeben werden.

column-name Name der Spalte in der Ergebnismenge
column-type Datentyp der Spalte in der Ergebnismenge. Der Datentyp muss mit den aus dem XML-Dokument ausgewählten Werten kompatibel sein.
OPTION-Klausel Verwenden Sie die OPTION-Klausel, um Optionen für die syntaktische Analyse der Eingabedatei anzugeben, wie Escapezeichen, Begrenzer, Kodierung, usw.
- ENCODING-Klausel Mit der ENCODING-Klausel können Sie die Kodierung angeben, mit der die Datei gelesen werden soll.
  Wenn die ENCODING-Klausel nicht angegeben ist, erfolgt das Laden der Werte im Zeichensatz der Datenbank (db_charset), wenn die Werte vom Typ CHAR oder BINARY sind, und im NCHAR-Datenbankzeichensatz (nchar_charset), wenn die Werte vom Typ NCHAR sind.
- BYTE ORDER MARK-Klausel Verwenden Sie die BYTE ORDER MARK-Klausel, um anzugeben, ob eine Byte Order Mark (BOM) in der Kodierung enthalten ist. Standardmäßig ist diese Option ON, sodass der Server eine Byte Order Mark (BOM) am Beginn der Daten suchen und interpretieren kann. Wenn BYTE ORDER MARK OFF ist, sucht der Server nicht nach einer BOM.
  Sie müssen die BYTE ORDER MARK-Klausel angeben, wenn die Eingabedaten kodiert sind.
  
  Wenn die ENCODING-Klausel angegeben ist:
  - Wenn die BYTE ORDER MARK-Option ON ist und Sie eine UTF-16-Kodierung mit Endian wie UTF-16BE oder UTF-16LE angeben, sucht der Datenbankserver nach einer BOM am Beginn der Daten. Wenn eine BOM vorhanden ist, wird sie benutzt, um den Endian der Daten zu prüfen. Wenn Sie den falschen Endian angeben, wird ein Fehler zurückgegeben.
  - Wenn die BYTE ORDER MARK-Option ON ist und Sie eine UTF-16-Kodierung ohne expliziten Endian angegeben haben, sucht der Datenbankserver nach einer BOM am Beginn der Daten. Wenn eine BOM vorhanden ist, wird sie benutzt, um den Endian der Daten zu bestimmen. Sonst wird der Endian des Betriebssystems angenommen.
  - Wenn die BYTE ORDER MARK-Option ON ist und Sie eine UTF-8-Kodierung angegeben haben, sucht der Datenbankserver nach einer BOM am Beginn der Daten. Wenn eine BOM vorhanden ist, wird sie ignoriert.
  Wenn die ENCODING-Klausel nicht angegeben ist:
  - Wenn Sie keine ENCODING-Klausel angeben und die BYTE ORDER MARK-Option ON ist, sucht der Server nach einer BOM am Beginn der Eingabedaten. Wenn es eine BOM findet, wird die Quellkodierung basierend auf der Kodierung der BOM (UTF-16BE, UTF-16LE oder UTF-8) automatisch gewählt und die BOM wird nicht als Teil der zu ladenden Daten angesehen.
  - Wenn Sie keine ENCODING-Klausel angeben und die BYTE ORDER MARK-Option OFF ist oder keine BOM am Beginn der Eingabedaten gefunden wird, verwendet Interactive SQL die CHAR-Kodierung.

Die openxml-Systemprozedur analysiert die xml-data syntaktisch und modelliert das Ergebnis als Struktur. Die Struktur enthält für jedes einzelne Element, Attribut, für jeden Textknoten bzw. für jedes sonstige XML-Konstrukt einen separaten Knoten. Die in der openxml-Systemprozedur angegebenen XPath-Abfragen werden verwendet, um Knoten von der Struktur auszuwählen, und die ausgewählten Knoten werden dann der Ergebnismenge zugeordnet.

Der XML-Parser der openxml-Systemprozedur überprüft die Gültigkeit des XML nicht und liest weder die externe DTD-Teilmenge noch externe Parameter-Entities.

Wenn für einen Spaltenausdruck mehrere Übereinstimmungen vorhanden sind, wird die erste Übereinstimmung in der Dokumentordnung verwendet (die Ordnung des ursprünglichen XML-Dokuments, bevor es syntaktisch analysiert wurde). NULL wird zurückgegeben, wenn es keine übereinstimmenden Knoten gibt. Wenn ein interner Knoten ausgewählt wird, enthält das Ergebnis alle untergeordneten Textknoten des internen Knotens in verketteter Form.

Für Spalten vom Typ BINARY, LONG BINARY, IMAGE und VARBINARY wird das Format Base64-kodiert angenommen und sie werden automatisch dekodiert. Wenn Sie XML mit der Klausel FOR XML generieren, werden diese Typen Base64-kodiert und können mit der openxml-Systemprozedur dekodiert werden.

Die openxml-Systemprozedur unterstützt eine Teilmenge der XPath-Syntax, und zwar wie folgt:

Die Achsen "child", "self", "attribute", "descendant", "descendant-or-self" und "parent" werden vollständig unterstützt.
Sowohl abgekürzte als auch nicht abgekürzte Syntax kann für alle unterstützten Funktionen verwendet werden. Beispiel: 'a' ist gleichwertig mit 'child::a' und '..' ist gleichwertig mit 'parent::node()'.
Namenstests können Platzhalter verwenden. Beispiel: 'a/*/b'.
Die folgenden "Kind"-Tests werden unterstützt: node(), text(), processing-instruction() und comment().
Qualifizierer der Form expr1[expr2] und expr1[expr2="string" ] können verwendet werden, wobei expr2 ein beliebiger unterstützter XPath-Ausdruck ist. Ein Qualifizierer ergibt TRUE, wenn expr2 mit einem oder mehreren Knoten übereinstimmt. Beispiel: 'a[b]' findet a-Knoten, die zumindest ein b-Kind-Objekt (Child) haben, und a[b="I"] findet a-Knoten, die zumindest ein b-Kind-Objekt (Child) mit einem Textwert von I haben.

FOR XML und Binärdaten
XPath-Ausdrücke verwenden
SQL-Datentypen
XML mit openxml importieren
Unterstützte Zeichensätze
FROM-Klausel
XPath-Abfragesprache: http://www.w3.org/TR/xpath.

Die folgende Abfrage erzeugt eine Ergebnismenge aus dem XML-Dokument, das als erstes Argument der openxml-Systemprozedur angegeben wurde:

SELECT * FROM openxml( '<products>
                 <ProductType ID="301">Tee Shirt</ProductType>
                 <ProductType ID="401">Baseball Cap</ProductType>
                 </products>',
                 '/products/ProductType' )
WITH ( ProductName LONG VARCHAR 'text()', ProductID CHAR(3) '@ID');

Diese Abfrage erzeugt das folgende Ergebnis:

ProductName	ProductID
Tee Shirt	301
Baseball Cap	401

Im folgenden Beispiel enthält das erste <ProductType>-Element eine Entität. Wenn Sie eine Abfrage ausführen, wird dieser Knoten syntaktisch als ein Element mit vier Child-Objekten analysiert: Tee, &, Sweater und Set. Sie können einen Punkt (.) verwenden, um die Child-Objekte in der Ergebnismenge zu verketten.

SELECT * FROM openxml( '<products>
                 <ProductType ID="301">Tee &amp; Sweater Set</ProductType>
                 <ProductType ID="401">Baseball Cap</ProductType>
                 </products>',
                 '/products/ProductType' )
WITH ( ProductName LONG VARCHAR '.', ProductID CHAR(3) '@ID');

Diese Abfrage erzeugt das folgende Ergebnis:

ProductName	ProductID
Tee Shirt & Sweater Set	301
Baseball Cap	401

Die folgende Abfrage verwendet ein Gleichheitsprädikat, um eine Ergebnismenge aus dem gelieferten XML-Dokument zu generieren.

SELECT * FROM openxml('<EmployeeDirectory>
   <Employee>
      <column name="EmployeeID">105</column>
      <column name="GivenName">Matthew</column>
      <column name="Surname">Cobb</column>
      <column name="Street">7 Pleasant Street</column>
      <column name="City">Grimsby</column>
      <column name="State">UT</column>
      <column name="PostalCode">02154</column>
      <column name="Phone">6175553840</column>
   </Employee>
  <Employee>
      <column name="EmployeeID">148</column>
      <column name="GivenName">Julie</column>
      <column name="Surname">Jordan</column>
      <column name="Street">1244 Great Plain Avenue</column>
      <column name="City">Woodbridge</column>
      <column name="State">AZ</column>
      <column name="PostalCode">01890</column>
      <column name="Phone">6175557835</column>
   </Employee>
  <Employee>
      <column name="EmployeeID">160</column>
      <column name="GivenName">Robert</column>
      <column name="Surname">Breault</column>
      <column name="Street">358 Cherry Street</column>
      <column name="City">Milton</column>
      <column name="State">PA</column>
      <column name="PostalCode">02186</column>
      <column name="Phone">6175553099</column>
   </Employee>
  <Employee>
      <column name="EmployeeID">243</column>
      <column name="GivenName">Natasha</column>
      <column name="Surname">Shishov</column>
      <column name="Street">151 Milk Street</column>
      <column name="City">Grimsby</column>
      <column name="State">UT</column>
      <column name="PostalCode">02154</column>
      <column name="Phone">6175552755</column>
   </Employee> 
</EmployeeDirectory>', '/EmployeeDirectory/Employee')
WITH ( EmployeeID INT 'column[@name="EmployeeID"]',
       GivenName    CHAR(20) 'column[@name="GivenName"]',
       Surname      CHAR(20) 'column[@name="Surname"]',
       PhoneNumber  CHAR(10) 'column[@name="Phone"]');

Diese Abfrage erzeugt die folgende Ergebnismenge:

EmployeeID	GivenName	Surname	PhoneNumber
105	Matthew	Cobb	6175553840
148	Julie	Jordan	6175557835
160	Robert	Breault	6175553099
243	Natasha	Shishov	6175552755

Die folgende Abfrage verwendet den XPath-Ausdruck @attribute, um eine Ergebnismenge zu generieren:

SELECT * FROM openxml( '<Employee
      EmployeeID="105"
      GivenName="Matthew"
      Surname="Cobb"
      Street="7 Pleasant Street"
      City="Grimsby"
      State="UT"
      PostalCode="02154"
      Phone="6175553840"
      />', '/Employee' )
WITH ( EmployeeID INT '@EmployeeID',
       GivenName    CHAR(20) '@GivenName',
       Surname      CHAR(20) '@Surname',
       PhoneNumber  CHAR(10) '@Phone');

Die folgende Abfrage verarbeitet ein XML-Dokument wie jenes in der obenstehenden Abfrage, nur dass ein XML-Namespace verwendet wird. Es veranschaulicht die Verwendung von Platzhaltern im Namenstest bei der XPath-Abfrage und generiert dieselbe Ergebnismenge wie die obenstehende Abfrage.

SELECT * FROM openxml( '<Employee  xmlns="http://www.iAnywhere.com/EmployeeDemo"
      EmployeeID="105"
      GivenName="Matthew"
      Surname="Cobb"
      Street="7 Pleasant Street"
      City="Grimsby"
      State="UT"
      PostalCode="02154"
      Phone="6175553840"
/>', '/*:Employee' )

WITH ( EmployeeID INT '@EmployeeID',
       GivenName    CHAR(20) '@GivenName',
       Surname      CHAR(20) '@Surname',
       PhoneNumber  CHAR(10) '@Phone');

Als Alternative können Sie eine Namespace-Deklaration angeben:

SELECT * FROM openxml( '<Employee  xmlns="http://www.iAnywhere.com/EmployeeDemo"
      EmployeeID="105"
      GivenName="Matthew"
      Surname="Cobb"
      Street="7 Pleasant Street"
      City="Grimsby"
      State="UT"
      PostalCode="02154"
      Phone="6175553840"
/>', '/prefix:Employee', 1, '<r xmlns:prefix="http://www.iAnywhere.com/EmployeeDemo"/>' )
WITH ( EmployeeID INT '@EmployeeID',
       GivenName    CHAR(20) '@GivenName',
       Surname      CHAR(20) '@Surname',
       PhoneNumber  CHAR(10) '@Phone');

openxml-Systemprozedur

Syntax 1

Syntax 2

Argumente

Bemerkungen

Siehe auch

Beispiel