openxml システムプロシージャー

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 句 結果セットのスキーマと、結果セットの各カラムに対して値を見つける方法を指定します。WITH 句の xpath 引数は、2 番目の引数の xpath に対する一致と相対的に一致します。WITH 句の式が複数のノードと一致した場合、ドキュメントの順序における最初のノードだけが使用されます。ノードがテキストノードではない場合、テキストノードの下位ノードをすべて追加することにより結果を検索します。WITH 句の式がどのノードにも一致しない場合は、そのローのカラムは NULL になります。
WITH 句内の xpath 引数には、リテラル文字列または変数を指定できます。

openxml の WITH 句構文は、ストアドプロシージャーから選択する際に使用する構文と似ています。

USING FILE | USING VALUE USING FILE 句は、ファイルからデータをロードするときに使用します。USING FILE 句を使用するには、DBA または READFILE 権限が必要です。

USING VALUE 句は、CHAR 型、NCHAR 型、BINARY 型、または LONG BINARY 型の式や、BLOB 文字列からデータをロードするときに使用します。

xml-data 結果セットのベースとなる XML。定数、変数、カラムなど、任意の文字列式が使用できます。
出力に NCHAR カラムが存在する場合、xml-data は NCHAR エンコードで直接解析されます。また、xpath 引数と namespaces 引数も NCHAR エンコードで変換され、解析されます。
xpath XPath クエリを含む文字列。XPath を使用すると、問い合わせる XML ドキュメントの構造を記述するパターンを指定できます。この引数にある XPath パターンによって、XML ドキュメントからノードが選択されます。2 番目の xpath 引数の XPath クエリに一致する各ノードが、テーブルにローを 1 つずつ生成します。
WITH 句の xpath 引数に指定できるのは、メタプロパティのみです。メタプロパティは、属性のように XPath クエリ内でアクセスされます。namespaces が指定されていない場合、デフォルトでプレフィクス mp が Uniform Resource Identifier (URI) urn:ianywhere-com:sa-xpath-metaprop にバインドされます。namespaces が指定された場合、この URI は、クエリのメタプロパティにアクセスするために mp または他のプレフィクスにバインドされます。メタプロパティ名の大文字と小文字は区別されます。openxml 文は、次のメタプロパティをサポートします。
- @mp:id XML ドキュメント内のユニークなノードの ID を返します。データベースサーバーが再起動されると、ドキュメント内の指定されたノードの ID が変更される場合もあります。このメタプロパティの値は、ドキュメントの順序で増えていきます。
- @mp:localname ノードの名前のローカル部分を返します。ノードに名前がない場合は NULL を返します。
- @mp:prefix ノードの名前のプレフィクス部分を返します。ノードに名前がない場合または名前にプレフィクスが付いていない場合は NULL を返します。
- @mp:namespaceuri ノードが含まれているネームスペースの URI を返します。ノードがネームスペースに含まれていない場合は NULL を返します。
- @mp:xmltext XML ドキュメントのサブツリーを XML 形式で返します。たとえば、内部ノードを照合する場合、このメタプロパティを使用して、下位のテキストノードの連結値ではなく、XML 文字列を返します。

flags WITH 句に XPath クエリが指定されていない場合、XML データと結果セットの間で使用されるマッピングを示します。flags パラメーターが指定されない場合、デフォルトで結果セットのカラムに属性がマッピングされます。flags パラメーターには、次のいずれかの値を指定します。

値	説明
1	XML 属性が結果セットのカラムにマッピングされます (デフォルト)。
2	XML 要素が結果セットのカラムにマッピングされます。

namespace-declaration XML ドキュメント。クエリのスコープ内のネームスペースは、ドキュメントのルート要素から取得されます。ネームスペースが指定されている場合は、すべての xpath 引数が指定されていても、必ず flags 引数を含めます。

column-name 結果セットのカラムの名前。
column-type 結果セットのカラムのデータ型。データ型は、XML ドキュメントから選択した値と互換性がなければなりません。
OPTION 句 OPTION 句は、エスケープ文字、デリミター、エンコードなど、入力ファイルに使用する解析オプションを指定するときに使用します。
- ENCODING 句 ENCODING 句では、ファイルの読み込みに使用されるエンコードを指定できます。
  ENCODING 句を指定しない場合、値が CHAR 型または BINARY 型のときはデータベース文字セット (db_charset) が、値が NCHAR 型のときは NCHAR データベース文字セット (nchar_charset) が値のエンコードと見なされます。
- BYTE ORDER MARK 句 BYTE ORDER MARK 句は、エンコード内にバイトオーダーマーク (BOM) があるかどうかを制御するときに指定します。デフォルトでは、このオプションは ON です。この場合、サーバーはデータの最初でバイトオーダーマーク (BOM) を検索して解釈できます。BYTE ORDER MARK が OFF の場合、サーバーは BOM を検索しません。
  入力データがエンコードされている場合は、BYTE ORDER MARK 句を指定してください。
  
  ENCODING 句が指定されている場合
  - BYTE ORDER MARK オプションが ON のときに、UTF-16BE または UTF-16LE などのエンディアンを持つ UTF-16 エンコードを指定すると、データベースサーバーはデータの最初で BOM を検索します。BOM がある場合は、データのエンディアンの検証に使用されます。間違ったエンディアンを指定すると、エラーが返されます。
  - BYTE ORDER MARK オプションが ON のときに、明示的なエンディアンのない UTF-16 エンコードを指定すると、データベースサーバーはデータの最初で BOM を検索します。BOM がある場合は、データのエンディアンの確認に使用されます。BOM がない場合は、オペレーティングシステムのエンディアンであると想定されます。
  - BYTE ORDER MARK オプションが ON のときに UTF-8 エンコードを指定すると、データベースサーバーはデータの最初で BOM を検索します。BOM がある場合は無視されます。
  ENCODING 句が指定されていない場合
  - ENCODING 句を指定しないで BYTE ORDER MARK オプションを ON にすると、サーバーは入力データの最初で BOM を検索します。BOM が見つかると、BOM のエンコード (UTF-16BE、UTF-16LE、または UTF-8) に基づいてソースエンコードが自動的に選択され、BOM はロードするデータの一部であるとは見なされなくなります。
  - ENCODING 句を指定せず、BYTE ORDER MARK オプションが OFF であるか、または BOM が入力データの先頭で見つからない場合は、データベースの CHAR エンコードが使用されます。

openxml システムプロシージャーは、xml-data を解析し、結果をツリーとして雛型化します。ツリーには、要素、属性、テキストノード、その他の XML 構成要素ごとに個別のノードがあります。openxml システムプロシージャーに指定される XPath クエリは、ツリーからノードを選択する際に使用され、選択されたノードはその後、結果セットにマッピングされます。

openxml システムプロシージャーで使用される XML パーサーは妥当性が検証されず、外部 DTD サブセットまたは外部パラメーターのエンティティを読み取りません。

カラム式に複数の一致がある場合、ドキュメントの順序 (解析される前の元の XML ドキュメントの順序) における最初の一致が使用されます。一致するノードがない場合は、NULL が返されます。内部ノードが選択される場合、結果は連結された内部ノードの下位のテキストノードすべてになります。

データ型が BINARY、LONG BINARY、IMAGE、VARBINARY のカラムは、base64 エンコード形式と見なされ、自動的に復号化されます。FOR XML 句を使用して XML を生成した場合、これらのタイプは base64 エンコードであり、openxml システムプロシージャーを使用して復号化できます。

openxml システムプロシージャーは、XPath 構文のサブセットを次のようにサポートしています。

子、自分、属性、子孫、子孫と自分、親は、完全にサポートされています。
省略形または省略形ではない構文のどちらも、サポートされるすべての機能に使用できます。たとえば、'a' は 'child::a' に等しく、'..' は 'parent::node()' と同じです。
名前のテストにワイルドカードが使用できます。たとえば 'a/*/b' のようになります。
サポートされるテストの種類は、node()、text()、processing-instruction()、comment() です。
フォーム expr1[expr2] and expr1[expr2="string" ] の修飾子を使用できます。この expr2 はサポートされている XPath 式です。expr2 が 1 つ以上のノードと一致する場合、修飾子は TRUE と評価されます。たとえば、'a[b]' は、少なくとも 1 つの子 b を持つ a ノードを検索します。a[b="I"] は、テキスト値 I を持つ子 b を少なくとも 1 つ持つ a ノードを検索します。

FOR XML とバイナリデータ
XPath 式の使用
SQL データ型
openxml を使用した XML のインポート
サポートされている文字セット
FROM 句
XPath クエリ言語： http://www.w3.org/TR/xpath。

次のクエリは、openxml システムプロシージャーへの最初の引数として指定された XML ドキュメントから結果セットを生成します。

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');

このクエリは、次の結果を生成します。

ProductName	ProductID
Tee Shirt	301
Baseball Cap	401

次の例では、最初の <ProductType> 要素にエンティティがあります。クエリを実行すると、このノードは、Tee、&、Sweater、Set の 4 つの子がある要素として解析されます。結果セット内で子を連結するには、. を使用します。

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');

このクエリは、次の結果を生成します。

ProductName	ProductID
Tee Shirt & Sweater Set	301
Baseball Cap	401

次のクエリは、等号述部を使用して、指定された XML ドキュメントから結果セットを生成します。

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"]');

このクエリは、次の結果セットを生成します。

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

次のクエリは、XPath @attribute 式を使用して結果セットを生成します。

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');

次のクエリは、上記のクエリで使用されているような XML ドキュメント (ただし、XML 名前空間は使用されています) を操作します。XPath クエリに名前のテストにワイルドカードを使用し、上記のクリックと同じ結果セットを生成する例です。

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');

または、名前空間の宣言を指定することもできます。

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 システムプロシージャー

構文 1

構文 2

引数

備考

参照

例