例: OPENXML を使用する
適用対象:
SQL ServerAzure SQL DatabaseAzure SQL Managed Instance
この記事の例では、OPENXML を使用して XML ドキュメントの行セット ビューを作成する方法を示します。 OPENXML の構文の詳細については、「OPENXML (Transact-SQL)」を参照してください。 ここに示す例では、OPENXML でのメタプロパティの指定を除く OPENXML のすべての側面を示します。 OPENXML のメタプロパティの指定方法の詳細については、「 OPENXML 内でのメタプロパティの指定」を参照してください。
例
データを取得する際に、rowpattern を使用して、XML ドキュメント内の行を決定するノードを識別します。 また、 rowpattern は、MSXML XPath の実装で使用される XPath パターン言語で表現されます。 たとえば、パターンが要素や属性になる場合、 rowpatternで選択される要素や属性のノードごとに行が作成されます。
flags 値は、既定のマッピングを指定します。 SchemaDeclaration で ColPatternが指定されていない場合、 flags で指定されたマッピングが想定されます。 SchemaDeclaration で ColPattern が指定されている場合、 flagsの値は無視されます。 指定された ColPattern により、マッピング、属性中心か要素中心か、さらにオーバーフロー データと未使用データを処理するときの動作が決まります。
A。 OPENXML を指定して基本的な SELECT ステートメントを実行する
この例の XML ドキュメントは、<Customer> 要素、<Order> 要素、および <OrderDetail> 要素で構成されます。 OPENXML ステートメントでは、XML ドキュメントから顧客情報が 2 列の行セット ( CustomerID と ContactName) で取得されます。
最初に、 sp_xml_preparedocument ストアド プロシージャを呼び出してドキュメント ハンドルを取得します。 このドキュメント ハンドルを OPENXML に渡します。
この OPENXML ステートメントは、次のことを表します。
rowpattern (/ROOT/Customer) により、処理する
<Customer>ノードが識別されます。flags パラメーター値を 1 に設定し、属性中心のマッピングを示します。 その結果、XML 属性は SchemaDeclarationで定義される行セットの列にマップされます。
WITH 句の SchemaDeclarationでは、指定された ColName 値は対応する XML 属性の名前と一致します。 したがって、 SchemaDeclaration では ColPatternパラメーターが指定されていません。
次に、SELECT ステートメントにより、OPENXML で提供される行セット内のすべての列が取得されます。
DECLARE @DocHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@DocHandle, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @DocHandle;
結果を次に示します。
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
flags を要素中心のマッピングを示す 2 に設定して同じ SELECT ステートメントを実行すると、<Customer> 要素にはサブ要素がないので、両方の顧客の CustomerID と ContactName の値が NULL として返されます。
また、@xmlDocument を xml 型や (n)varchar(max) 型にすることもできます。
XML ドキュメント内で <CustomerID> と <ContactName> がサブ要素の場合は、要素中心のマッピングにより値が取得されます。
DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer>
<CustomerID>VINET</CustomerID>
<ContactName>Paul Henriot</ContactName>
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer>
<CustomerID>LILAS</CustomerID>
<ContactName>Carlos Gonzalez</ContactName>
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @XmlDocumentHandle;
結果を次に示します。
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
sp_xml_preparedocument から返されたドキュメント ハンドルは、セッション内ではなく、バッチが実行されている間有効です。
B. 行セットの列と XML の属性や要素の間でマッピングを行うために ColPattern を指定する
この例では、省略可能な ColPattern パラメーターに XPath パターンを指定して、行セットの列と XML の属性や要素の間でマッピングを行う方法を示します。
この例の XML ドキュメントは、<Customer> 要素、<Order> 要素、および <OrderDetail> 要素で構成されます。 OPENXML ステートメントにより、XML ドキュメントから顧客情報と注文情報が 1 つの行セット (CustomerID、 OrderDate、 ProdID、および Qty) として取得されます。
最初に、 sp_xml_preparedocument ストアド プロシージャを呼び出してドキュメント ハンドルを取得します。 このドキュメント ハンドルを OPENXML に渡します。
この OPENXML ステートメントは、次のことを表します。
- rowpattern (/ROOT/Customer/Order/OrderDetail) により、処理する
<OrderDetail>ノードが識別されます。
ここでは説明のために、 flags パラメーター値を 2 に設定して、要素中心のマッピングを示しています。 ただし、このマッピングは ColPattern に指定したマッピングによって上書きされます。 つまり、 ColPattern に指定した XPath パターンにより、行セットの列が属性にマップされます。 この結果、属性中心のマッピングになります。
WITH 句の SchemaDeclarationでは、 ColName パラメーターと ColType パラメーターを使用して ColPattern も指定されています。 省略可能な ColPattern は指定された XPath パターンで、次のことを示します。
行セットの OrderID 列、CustomerID 列、および OrderDate 列は rowpattern によって識別されたノードの親の属性にマップされ、rowpattern によって
<OrderDetail>ノードが識別されます。 したがって、CustomerID 列と OrderDate 列は<Order>要素の CustomerID 属性と OrderDate 属性にマップされます。行セットの ProdID 列と Qty 列は、 rowpattern で識別されたノードの ProductID 属性と Quantity属性にマップされます。
次に、SELECT ステートメントにより、OPENXML で提供される行セット内のすべての列が取得されます。
DECLARE @XmlDocumentHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument;
-- Execute a SELECT stmt using OPENXML rowset provider.
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order/OrderDetail',2)
WITH (OrderID int '../@OrderID',
CustomerID varchar(10) '../@CustomerID',
OrderDate datetime '../@OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity');
EXEC sp_xml_removedocument @XmlDocumentHandle;
結果を次に示します。
OrderID CustomerID OrderDate ProdID Qty
-------------------------------------------------------------
10248 VINET 1996-07-04 00:00:00.000 11 12
10248 VINET 1996-07-04 00:00:00.000 42 10
10283 LILAS 1996-08-16 00:00:00.000 72 3
ColPattern として指定した XPath パターンを、行セットの列に XML 要素をマップするために指定することもできます。 この結果、要素中心のマッピングになります。 次の例では、XML ドキュメントの <CustomerID> と <OrderDate> は、<Orders> 要素のサブ要素です。 ColPattern により flags パラメーターに指定されたマッピングが上書きされるので、OPENXML では flags パラメーターを指定しません。
DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order EmployeeID="5" >
<OrderID>10248</OrderID>
<CustomerID>VINET</CustomerID>
<OrderDate>1996-07-04T00:00:00</OrderDate>
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
<Order EmployeeID="3" >
<OrderID>10283</OrderID>
<CustomerID>LILAS</CustomerID>
<OrderDate>1996-08-16T00:00:00</OrderDate>
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail')
WITH (CustomerID varchar(10) '../CustomerID',
OrderDate datetime '../OrderDate',
ProdID int '@ProductID',
Qty int '@Quantity');
EXEC sp_xml_removedocument @docHandle;
.C 属性中心のマッピングと要素中心のマッピングを組み合わせる
この例では、 flags パラメーターを 3 に設定し、属性中心のマッピングと要素中心のマッピングの両方が適用されることを示します。 この場合、まだ処理されていないすべての列に対して、まず属性中心のマッピングが適用されてから、次に要素中心のマッピングが適用されます。
DECLARE @docHandle int;
DECLARE @XmlDocument nvarchar(1000);
SET @XmlDocument =N'<ROOT>
<Customer CustomerID="VINET" >
<ContactName>Paul Henriot</ContactName>
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5"
OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" >
<ContactName>Carlos Gonzalez</ContactName>
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3"
OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer',3)
WITH (CustomerID varchar(10),
ContactName varchar(20));
EXEC sp_xml_removedocument @docHandle;
結果
CustomerID ContactName
---------- --------------------
VINET Paul Henriot
LILAS Carlos Gonzalez
CustomerIDに属性中心のマッピングが適用されます。 <Customer> 要素には ContactName 属性がありません。 そのため、要素中心のマッピングが適用されます。
D. ColPattern として text() XPath 関数を指定する
この例の XML ドキュメントは、<Customer> 要素および <Order> 要素で構成されます。 OPENXML ステートメントにより、<Order> 要素の oid 属性、rowpattern で識別されるノードの親の ID、および要素のコンテンツのリーフ値の文字列で構成される行セットが取得されます。
最初に、 sp_xml_preparedocument ストアド プロシージャを呼び出してドキュメント ハンドルを取得します。 このドキュメント ハンドルを OPENXML に渡します。
この OPENXML ステートメントは、次のことを表します。
rowpattern (/root/Customer/Order) により、処理する
<Order>ノードが識別されます。flags パラメーター値を 1 に設定し、属性中心のマッピングを示します。 その結果、XML 属性は SchemaDeclarationで定義される行セットの列にマップされます。
WITH 句の SchemaDeclaration では、行セットの列名である oid と amount が対応する XML 属性の名前と一致します。 したがって、 ColPattern パラメーターは指定されません。 行セット内の comment 列では、XPath 関数
text()が ColPatternに指定されています。 これにより、 flagsに指定された属性中心のマッピングが上書きされ、列には要素コンテンツのリーフ値の文字列が含まれます。
次に、SELECT ステートメントにより、OPENXML で提供される行セット内のすべての列が取得されます。
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--sample XML document
SET @xmlDocument =N'<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
Happy Customer.
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
WITH (oid char(5),
amount float,
comment ntext 'text()');
EXEC sp_xml_removedocument @docHandle;
結果を次に示します。
oid amount comment
----- ----------- -----------------------------
O1 3.5 NULL
O2 13.4 Customer was very satisfied
O3 100.0 Happy Customer.
O4 10000.0 NULL
E. WITH 句で TableName を指定する
この例では、WITH 句に SchemaDeclaration ではなく TableNameを指定します。 この指定は、目的の構造のテーブルがあり、列パターンの ColPattern パラメーターが必要ない場合に役立ちます。
この例の XML ドキュメントは、<Customer> 要素および <Order> 要素で構成されます。 OPENXML ステートメントでは、XML ドキュメントから注文情報が 3 列の行セット (oid、 date、および amount) に取得されます。
最初に、 sp_xml_preparedocument ストアド プロシージャを呼び出してドキュメント ハンドルを取得します。 このドキュメント ハンドルを OPENXML に渡します。
この OPENXML ステートメントは、次のことを表します。
rowpattern (/root/Customer/Order) により、処理する
<Order>ノードが識別されます。WITH 句には SchemaDeclaration がありません。 代わりに、テーブル名が指定されています。 そのため、行セット スキーマとしてテーブル スキーマが使用されます。
flags パラメーター値を 1 に設定し、属性中心のマッピングを示します。 したがって、 rowpatternで識別される要素の属性は、同じ名前の行セット列にマップされます。
次に、SELECT ステートメントにより、OPENXML で提供される行セット内のすべての列が取得されます。
-- Create a test table. This table schema is used by OPENXML as the
-- rowset schema.
CREATE TABLE T1(oid char(5), date datetime, amount float);
GO
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
-- Sample XML document
SET @xmlDocument =N'<root>
<Customer cid= "C1" name="Janine" city="Issaquah">
<Order oid="O1" date="1/20/1996" amount="3.5" />
<Order oid="O2" date="4/30/1997" amount="13.4">Customer was very
satisfied</Order>
</Customer>
<Customer cid="C2" name="Ursula" city="Oelde" >
<Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
white red">
<Urgency>Important</Urgency>
</Order>
<Order oid="O4" date="1/20/1996" amount="10000"/>
</Customer>
</root>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/root/Customer/Order', 1)
WITH T1;
EXEC sp_xml_removedocument @docHandle;
結果を次に示します。
oid date amount
----- --------------------------- ----------
O1 1996-01-20 00:00:00.000 3.5
O2 1997-04-30 00:00:00.000 13.4
O3 1999-07-14 00:00:00.000 100.0
O4 1996-01-20 00:00:00.000 10000.0
.F エッジ テーブル形式での結果の取得
この例では、OPENXML ステートメントに WITH 句が指定されていません。 その結果、OPENXML で生成される行セットがエッジ テーブル形式になります。 SELECT ステートメントにより、エッジ テーブル内のすべての列が返されます。
この例のサンプル XML ドキュメントは、<Customer> 要素、<Order> 要素、および <OrderDetail> 要素で構成されます。
最初に、 sp_xml_preparedocument ストアド プロシージャを呼び出してドキュメント ハンドルを取得します。 このドキュメント ハンドルを OPENXML に渡します。
この OPENXML ステートメントは、次のことを表します。
rowpattern (/ROOT/Customer) により、処理する
<Customer>ノードが識別されます。WITH 句は指定されていません。 したがって、OPENXML により、行セットがエッジ テーブル形式で返されます。
次に、SELECT ステートメントにより、エッジ テーブル内のすべての列が取得されます。
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
SET @xmlDocument = N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<OrderDetail OrderID="10248" ProductID="11" Quantity="12"/>
<OrderDetail OrderID="10248" ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
<Order CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<OrderDetail OrderID="10283" ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer');
EXEC sp_xml_removedocument @docHandle;
結果はエッジ テーブルとして返されます。 エッジ テーブルに対するクエリを作成して、情報を取得できます。 次に例を示します。
次のクエリにより、ドキュメント内の Customer ノードの数が返されます。 WITH 句が指定されていないので、OPENXML からエッジ テーブルが返されます。 SELECT ステートメントにより、エッジ テーブルへのクエリが実行されます。
SELECT count(*) FROM OPENXML(@docHandle, '/') WHERE localname = 'Customer';次のクエリにより、要素型の XML ノードのローカル名が返されます。
SELECT distinct localname FROM OPENXML(@docHandle, '/') WHERE nodetype = 1 ORDER BY localname;
G. 属性で終了する rowpattern を指定する
この例の XML ドキュメントは、<Customer> 要素、<Order> 要素、および <OrderDetail> 要素で構成されます。 OPENXML ステートメントにより、XML ドキュメントから注文明細の情報が 3 列の行セット (ProductID、 Quantity、および OrderID) で取得されます。
最初に、 sp_xml_preparedocument を呼び出してドキュメント ハンドルを取得します。 このドキュメント ハンドルを OPENXML に渡します。
この OPENXML ステートメントは、次のことを表します。
rowpattern (/ROOT/Customer/Order/OrderDetail/@ProductID) は、XML 属性 ProductIDで終了します。 結果の行セットでは、XML ドキュメント内で選択した属性ノードごとに行が作成されます。
この例では、flags パラメーターは指定されていません。 代わりに、 ColPattern パラメーターによってマッピングを指定します。
WITH 句の SchemaDeclaration では、 ColName パラメーターと ColType パラメーターを使用して ColPattern も指定されています。 省略可能な ColPattern は、次のことを示すために指定する XPath パターンです。
行セット内のProdID列の ColPattern に指定された XPath パターン ( . ) により、コンテキスト ノード (現在のノード) が識別されます。 指定された rowpattern によって、これは、
<OrderDetail>要素の ProductID 属性となります。行セット内の Qty 列に指定された ColPattern である ../@Quantity により、コンテキスト ノード <ProductID> の親ノードである
<OrderDetail>の Quantity 属性が識別されます。同様に、行セット内の OID 列に指定された ColPattern である ../../@OrderID により、コンテキスト ノードの親ノードの親である
<Order>の OrderID 属性が識別されます。 親ノードは<OrderDetail>で、コンテキスト ノードは<ProductID>です。
次に、SELECT ステートメントにより、OPENXML で提供される行セット内のすべての列が取得されます。
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(1000);
--Sample XML document
SET @xmlDocument =N'<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate=
"1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzalez">
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate=
"1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>';
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@docHandle, '/ROOT/Customer/Order/OrderDetail/@ProductID')
WITH ( ProdID int '.',
Qty int '../@Quantity',
OID int '../../@OrderID');
EXEC sp_xml_removedocument @docHandle;
結果を次に示します。
ProdID Qty OID
----------- ----------- -------
11 12 10248
42 10 10248
72 3 10283
H. 複数のテキスト ノードを含む XML ドキュメントを指定する
XML ドキュメント内に複数のテキスト ノードがある場合、 ColPatternである text() が指定された SELECT ステートメントにより、すべてのテキスト ノードではなく最初のテキスト ノードだけが返されます。 次に例を示します。
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT,
N'<root xmlns:a="urn:1">
<a:Elem abar="asdf">
T<a>a</a>U
</a:Elem>
</root>',
'<ns xmlns:b="urn:1" />';
SELECT * FROM openxml(@h, '/root/b:Elem')
WITH (Col1 varchar(20) 'text()');
EXEC sp_xml_removedocument @h;
SELECT ステートメントでは、結果として TaU ではなく Tが返されます。
I. WITH 句で xml データ型を指定する
WITH 句では、 xml データ型の列にマップされる列パターンが型指定されているかどうかにかかわらず、この列パターンから、空のシーケンスまたは要素のシーケンス、処理命令、テキスト ノード、およびコメントが返されます。 データは xml データ型にキャストされます。
次の例では、WITH 句のテーブル スキーマ宣言に xml 型の列が含まれています。
DECLARE @h int;
DECLARE @x xml;
set @x = '<Root>
<row id="1"><lname>Duffy</lname>
<Address>
<Street>111 Maple</Street>
<City>Seattle</City>
</Address>
</row>
<row id="2"><lname>Wang</lname>
<Address>
<Street>222 Pine</Street>
<City>Bothell</City>
</Address>
</row>
</Root>';
EXEC sp_xml_preparedocument @h output, @x;
SELECT *
FROM OPENXML (@h, '/Root/row', 10)
WITH (id int '@id',
lname varchar(30),
xmlname xml 'lname',
OverFlow xml '@mp:xmltext');
EXEC sp_xml_removedocument @h;
具体的には、 xml 型の変数 (@x) を sp_xml_preparedocument() 関数に渡しています。
結果を次に示します。
id lname xmlname OverFlow
--- ------- ------------------------------ -------------------------------
1 Duffy <lname>Duffy</lname> <row><Address>
<Street>111 Maple</Street>
<City>Seattle</City>
</Address></row>
2 Wang <lname>Wang</lname> <row><Address>
<Street>222 Pine</Street>
<City>Bothell</City>
</Address></row>
結果では、次の点に注意してください。
varchar(30) 型の lname 列の場合、この列の値が対応する
<lname>要素から取得されます。xml 型の xmlname 列では、この列の値として同じ名前の要素が返されます。
フラグは 10 に設定されます。 10 は 2 + 8 を意味し、2 は要素中心のマッピングを示します。8 は未使用の XML データのみを WITH 句で定義した OverFlow 列に追加する必要があることを示します。 フラグを 2 に設定すると、WITH 句で指定された OverFlow 列に XML ドキュメント全体がコピーされます。
WITH 句の列が型指定された XML 列で、XML インスタンスがスキーマに準拠しない場合、エラーが返されます。
J. 複数の値の属性から個別の値を取得する
XML ドキュメントには、複数の値を指定できる属性を含めることができます。 たとえば、 IDREFS 属性には複数の値を指定できます。 XML ドキュメントでは、複数の値の属性値を、各値をスペースで区切った文字列として指定します。 次の XML ドキュメントでは、<Student> 要素の attends 属性と <Class> 要素の attendedBy 属性に複数の値を指定できます。 複数の値を指定できる XML 属性から値を個別に取得し、各値をデータベース内の別々の行に格納するには、追加作業が必要です。 この例ではその処理を示します。
このサンプル XML ドキュメントは、次の要素で構成されます。
<学生>
id (学生 ID) 属性、 name属性、および attends 属性。 attends 属性には複数の値を指定できます。
<クラス>
id (クラス ID) 属性、 name属性、および attendedBy 属性。 attendedBy 属性には複数の値を指定できます。
<Student> の attends 属性と <Class> の attendedBy 属性は、Student テーブルと Class テーブル間の m:n リレーションシップを表します。 学生は多くのクラスを受講でき、クラスは多くの学生を受け入れることができます。
次に示すように、このドキュメントを細分化し、データベースに保存するとします。
<Student>データは Students テーブルに保存します。<Class>データは Courses テーブルに保存します。Student と Class 間の m:n リレーションシップ データは、CourseAttendence テーブルに保存します。 値を抽出するには、さらなる作業が必要です。 この情報を取得し、テーブルに格納するには、次のストアド プロシージャを使用します。
Insert_Idrefs_Values
コース ID と学生 ID の値を CourseAttendence テーブルに挿入します。
Extract_idrefs_values
各 <Course> 要素から個別の学生 ID を抽出します。 エッジ テーブルを使用して、これらの値を取得します。
次に手順を示します。
-- Create these tables:
DROP TABLE CourseAttendance;
DROP TABLE Students;
DROP TABLE Courses;
GO
CREATE TABLE Students(
id varchar(5) primary key,
name varchar(30)
);
GO
CREATE TABLE Courses(
id varchar(5) primary key,
name varchar(30),
taughtBy varchar(5)
);
GO
CREATE TABLE CourseAttendance(
id varchar(5) references Courses(id),
attendedBy varchar(5) references Students(id),
constraint CourseAttendance_PK primary key (id, attendedBy)
);
GO
-- Create these stored procedures:
DROP PROCEDURE f_idrefs;
GO
CREATE PROCEDURE f_idrefs
@t varchar(500),
@idtab varchar(50),
@id varchar(5)
AS
DECLARE @sp int;
DECLARE @att varchar(5);
SET @sp = 0;
WHILE (LEN(@t) > 0)
BEGIN
SET @sp = CHARINDEX(' ', @t+ ' ');
SET @att = LEFT(@t, @sp-1);
EXEC('INSERT INTO '+@idtab+' VALUES ('''+@id+''', '''+@att+''')');
SET @t = SUBSTRING(@t+ ' ', @sp+1, LEN(@t)+1-@sp);
END;
GO
DROP PROCEDURE fill_idrefs
GO
CREATE PROCEDURE fill_idrefs
@xmldoc int,
@xpath varchar(100),
@from varchar(50),
@to varchar(50),
@idtable varchar(100)
AS
DECLARE @t varchar(500);
DECLARE @id varchar(5);
/* Temporary Edge table */
SELECT *
INTO #TempEdge
FROM OPENXML(@xmldoc, @xpath);
DECLARE fillidrefs_cursor CURSOR FOR
SELECT CAST(iv.text AS nvarchar(200)) AS id,
CAST(av.text AS nvarchar(4000)) AS refs
FROM #TempEdge c, #TempEdge i,
#TempEdge iv, #TempEdge a, #TempEdge av
WHERE c.id = i.parentid
AND UPPER(i.localname) = UPPER(@from)
AND i.id = iv.parentid
AND c.id = a.parentid
AND UPPER(a.localname) = UPPER(@to)
AND a.id = av.parentid;
OPEN fillidrefs_cursor
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
execute f_idrefs @t, @idtable, @id;
END
FETCH NEXT FROM fillidrefs_cursor INTO @id, @t
END;
CLOSE fillidrefs_cursor;
DEALLOCATE fillidrefs_cursor;
Go
-- This is the sample document that is shredded and the data is stored in the preceding tables.
DECLARE @h int;
EXECUTE sp_xml_preparedocument @h OUTPUT, N'<Data>
<Student id = "s1" name = "Student1" attends = "c1 c3 c6" />
<Student id = "s2" name = "Student2" attends = "c2 c4" />
<Student id = "s3" name = "Student3" attends = "c2 c4 c6" />
<Student id = "s4" name = "Student4" attends = "c1 c3 c5" />
<Student id = "s5" name = "Student5" attends = "c1 c3 c5 c6" />
<Student id = "s6" name = "Student6" />
<Class id = "c1" name = "Intro to Programming"
attendedBy = "s1 s4 s5" />
<Class id = "c2" name = "Databases"
attendedBy = "s2 s3" />
<Class id = "c3" name = "Operating Systems"
attendedBy = "s1 s4 s5" />
<Class id = "c4" name = "Networks" attendedBy = "s2 s3" />
<Class id = "c5" name = "Algorithms and Graphs"
attendedBy = "s4 s5"/>
<Class id = "c6" name = "Power and Pragmatism"
attendedBy = "s1 s3 s5" />
</Data>';
INSERT INTO Students SELECT * FROM OPENXML(@h, '//Student') WITH Students
INSERT INTO Courses SELECT * FROM OPENXML(@h, '//Class') WITH Courses
/* Using the edge table */
EXECUTE fill_idrefs @h, '//Class', 'id', 'attendedby', 'CourseAttendance';
SELECT * FROM Students;
SELECT * FROM Courses;
SELECT * FROM CourseAttendance;
EXECUTE sp_xml_removedocument @h;
K. XML 内の base64 エンコードされたデータからバイナリ データを取得する
多くの場合、バイナリ データは base64 エンコードを使用して XML に含められます。 OPENXML を使用してこの XML を細分化するときに、base64 エンコードされたデータを受け取ります。 この例では、base64 エンコードされたデータをバイナリ データに変換する方法を示します。
サンプル バイナリ データを含むテーブルを作成します。
FOR XML クエリと BINARY BASE64 オプションを使用して、base64 としてエンコードされるバイナリ データを含む XML を構築します。
OPENXML を使用して XML を細分化します。 OPENXML から返されるデータは base64 エンコードされたデータです。 次に、
.value関数を呼び出してデータをバイナリ データに変換します。
CREATE TABLE T (Col1 int primary key, Col2 varbinary(100));
GO
-- Insert sample binary data
INSERT T VALUES(1, 0x1234567890);
GO
-- Create test XML document that has base64 encoded binary data (use FOR XML query and specify BINARY BASE64 option)
SELECT * FROM T
FOR XML AUTO, BINARY BASE64;
GO
-- result
-- <T Col1="1" Col2="EjRWeJA="/>
-- Now shredd the sample XML using OPENXML.
-- Call the .value function to convert
-- the base64 encoded data returned by OPENXML to binary.
DECLARE @h int;
EXEC sp_xml_preparedocument @h OUTPUT, '<T Col1="1" Col2="EjRWeJA="/>';
SELECT Col1,
CAST('<binary>' + Col2 + '</binary>' AS XML).value('.', 'varbinary(max)') AS BinaryCol
FROM openxml(@h, '/T')
WITH (Col1 integer, Col2 varchar(max)) ;
EXEC sp_xml_removedocument @h;
GO
結果は次のとおりです。 返されるバイナリ データは、テーブル T 内の元のバイナリ データです。
Col1 BinaryCol
----------- ---------------------
1 0x1234567890
関連項目
フィードバック
以下は間もなく提供いたします。2024 年を通じて、コンテンツのフィードバック メカニズムとして GitHub の issue を段階的に廃止し、新しいフィードバック システムに置き換えます。 詳細については、「https://aka.ms/ContentUserFeedback」を参照してください。
フィードバックの送信と表示