BizTalk Serverを使用して、SQL Serverで FOR XML 句を持つストアドプロシージャを実行する

[アーティクル]
07/21/2023

SQL SELECT ステートメントには、クエリ結果を行セットではなく XML として返す FOR XML 句を指定できます。 FOR XML 句を含む SELECT ステートメントを持つストアドプロシージャを作成することもできます。 FOR XML (SQL Server) には、詳細が含まれます。

WCF ベースの SQL アダプターを使用して、このようなストアドプロシージャを実行できます。

重要

BizTalk Serverで使用できる "ネイティブ" SQL アダプターでは、ストアドプロシージャで SELECT ステートメントの一部として FOR XML 句を使用する必要があります。このようなストアドプロシージャは、ストアドプロシージャ定義を変更せずに WCF ベースの SQL アダプターで使用できます。

以前のバージョンのBizTalk Serverで提供されている "ネイティブ" SQL アダプターを使用して生成されたスキーマは、いつでも使用できます。詳細については、「 WCF-SQL アダプターでの FOR XML クエリの使用」を参照してください。

FOR XML 句を使用してストアドプロシージャを呼び出す方法

SQL Server Management Studioで FOR XML 句を使用してストアドプロシージャを呼び出すか、BizTalk Serverで使用できる SQL アダプターを使用すると、出力は xml メッセージの形式になります。 WCF ベースの SQL アダプターでこれらの手順を使用するには、出力メッセージのスキーマが必要です。 WCF ベースの SQL アダプターでは、FOR XML 句を使用してストアドプロシージャを実行した後、SQL Serverから応答メッセージを受信するときに、このスキーマが必要です。このストアドプロシージャを呼び出す要求メッセージは、アダプター自体によって生成されることに注意してください。

応答メッセージのスキーマとは別に、WCF ベースの SQL アダプターを使用して FOR XML 句を使用してストアドプロシージャを呼び出す特定のタスクも実行する必要があります。

FOR XML 句を使用して、ストアドプロシージャの応答メッセージのスキーマを生成します。 BizTalk Serverで使用できる "ネイティブ" SQL アダプターによって生成された応答スキーマが既にある場合は、この手順をスキップできます。
BizTalk プロジェクトを作成し、生成されたスキーマをプロジェクトに追加します。
WCF ベースの SQL アダプターを使用して、FOR XML 句を使用してストアドプロシージャのスキーマを生成します。これにより、アダプターがストアドプロシージャを呼び出すためにSQL Serverに送信する要求メッセージのスキーマが提供されます。
BizTalk プロジェクトでメッセージを作成し、SQL Serverからメッセージを送受信します。要求メッセージは、アダプターによって生成された要求メッセージのスキーマに準拠している必要があります。応答メッセージは、"ネイティブ" SQL アダプターを使用するか、SQL Server Management Studioの FOR XML 句を使用してストアドプロシージャを実行して取得した応答メッセージのスキーマに準拠している必要があります。
SQL Server データベースでストアドプロシージャを呼び出すオーケストレーションを作成します。
BizTalk プロジェクトをビルドして展開します。
物理送受信ポートを作成して BizTalk アプリケーションを構成します。
BizTalk アプリケーションを起動します。

ストアドプロシージャの応答メッセージのスキーマの生成

Note

SQL アダプターによって生成された応答スキーマを BizTalk Server で使用できる場合は、この手順を実行する必要はありません。

ストアドプロシージャの SELECT ステートメントに句と句がある場合は、ストアドプロシージャ xmlschema の応答メッセージのスキーマを for xml 生成できます。このトピックでは、特定の従業員 ID の従業員の詳細を取得するGET_EMP_DETAILS_FOR_XML ストアドプロシージャを使用します。ストアドプロシージャを実行してスキーマを取得するには、SELECT ステートメントは次のようになります。

SELECT [Employee_ID] ,[Name] ,[DOJ] ,[Designation] ,[Job_Description] ,[Photo] ,cast([Rating] as varchar(100)) as Rating ,[Salary] ,[Last_Modified] ,[Status] ,[Address]
FROM [Adapt_Doc].[dbo].[Employee] for xml auto, xmlschema

このストアドプロシージャを実行して、応答メッセージのスキーマを取得します。ストアドプロシージャからの応答には、スキーマと、ストアドプロシージャの実行からのデータが含まれていることに注意してください。応答からスキーマをコピーし、テキストパッドに保存する必要があります。この例では、このスキーマに ResponseSchema.xsd という名前を付けることができます。次に、Visual Studio で BizTalk プロジェクトを作成し、このスキーマをプロジェクトに追加する必要があります。

重要

スキーマを生成するストアドプロシージャを xmlschema 実行した後で、句を削除してください。これを行わない場合、最終的に BizTalk を使用してストアドプロシージャを実行すると、応答メッセージにスキーマが再度生成されます。そのため、応答メッセージを xml として取得するには、句を削除する xmlschema 必要があります。

BizTalk プロジェクトにスキーマを追加するには

Visual Studio で BizTalk プロジェクトを作成します。
ストアドプロシージャ用に生成した応答スキーマを BizTalk プロジェクトに追加します。ソリューションエクスプローラーで BizTalk プロジェクトを右クリックし、[追加] をポイントして、[既存の項目] をクリックします。 [既存の項目の追加] ダイアログボックスで、スキーマを保存した場所に移動し、[ 追加] をクリックします。
Visual Studio でスキーマを開き、次の変更を行います。
1. スキーマにノードを追加し、この新しく追加されたノードの下に既存のルートノードを移動します。ルートノードに名前を付けます。このトピックでは、ルートノードの名前を Root に変更します。
2. ストアドプロシージャに対して生成された応答スキーマは、sqltypes.xsd を参照します。 sqltypes.xsd スキーマはから https://go.microsoft.com/fwlink/?linkid=31850取得できます。 sqltypes.xsd スキーマを BizTalk プロジェクトに追加します。このスキーマの詳細については、次のページを参照してください。
  - 2.2.5.2 sqlTypes
  - 組み込みの XML スキーマコレクションを参照する (sys)
3. ストアドプロシージャ用に生成されたスキーマで、の import schemaLocation 値を次のように変更します。
```
import schemaLocation=”sqltypes.xsd”
```
  これは、既に sqltypes.xsd スキーマを BizTalk プロジェクトに追加しているためです。
4. スキーマのターゲット名前空間を指定します。 [スキーマ>] ノードを<クリックし、プロパティペインで [ターゲット名前空間] プロパティに名前空間を指定します。このトピックでは、名前空間をとして http://ForXmlStoredProcs/namespace指定します。

ストアドプロシージャを呼び出す要求メッセージのスキーマの生成

要求メッセージのスキーマを生成するには、Visual Studio の BizTalk プロジェクトからアダプターサービスアドインを使用できます。このトピックでは、GET_EMP_DETAILS_FOR_XML ストアドプロシージャのスキーマを生成します。アダプターサービスアドインを使用してスキーマを生成する方法の詳細については、「SQL アダプターを使用して Visual Studio でSQL Server操作のメタデータを取得する」を参照してください。

重要

スキーマを生成するには、[アダプターサービスアドインを使用する] の [ プロシージャ ] ノードからプロシージャのみを選択する必要があります。

メッセージとメッセージの種類の定義

先に生成したスキーマは、オーケストレーションのメッセージに求められる "型" を記述します。通常、メッセージは変数であり、対応するスキーマによって定義される型です。オーケストレーションのメッセージを作成し、前の手順で生成したスキーマにリンクする必要があります。

メッセージを作成し、スキーマにリンクするには

BizTalk プロジェクトにオーケストレーションを追加します。ソリューションエクスプローラーから BizTalk プロジェクト名を右クリックし、[追加] をポイントして、[新しい項目] をクリックします。 BizTalk オーケストレーションの名前を入力し、[ 追加] をクリックします。
BizTalk プロジェクトの [オーケストレーションの種類] ウィンドウを開きます (まだ表示されていない場合)。これを行うには、[ 表示] をクリックし、[ その他のウィンドウ] をポイントして、[ オーケストレーションビュー] をクリックします。
オーケストレーションビューで、[ メッセージ] を右クリックし、[ 新しいメッセージ] をクリックします。
新しく作成したメッセージを右クリックし、[ プロパティウィンドウ] を選択します。

Message_1の [プロパティ] ウィンドウで、次の操作を行います。

プロパティ	目的
識別子	「`Request`」と入力します
メッセージ型	ドロップダウンリストから [ スキーマ] を展開し、[ForXMLProcedure.Procedure_dbo] を選択します。GET_EMP_DETAILS_FOR_XML。ForXMLProcedure は BizTalk プロジェクトの名前です。 Procedure_dboは、GET_EMP_DETAILS_FOR_XML プロシージャを呼び出すためのスキーマです。

手順 2 を繰り返して、新しいメッセージを作成します。新しいメッセージの [プロパティ ] ウィンドウで、次の操作を行います。

プロパティ	目的
識別子	「`Response`」と入力します
メッセージ型	ドロップダウンリストから [ スキーマ] を展開し、[ ForXMLProcedure.ResponseSchema] を選択します。ここで、ResponseSchema はストアドプロシージャの応答メッセージのスキーマの生成に関するページで説明されているように、ストアドプロシージャを実行して生成される応答スキーマの名前です。

オーケストレーションの設定

SQL Serverでストアドプロシージャを実行するためにBizTalk Serverを使用するには、BizTalk オーケストレーションを作成する必要があります。このオーケストレーションでは、定義された受信場所に要求メッセージをドロップします。 SQL アダプターはこのメッセージを使用し、SQL Serverに渡します。 SQL Serverからの応答は、別の場所に保存されます。 SQL Serverにメッセージを送信し、応答を受信するには、それぞれ [送受信] 図形を含める必要があります。プロシージャを呼び出すためのサンプルオーケストレーションは、次のようになります。

eac6e8b6-f0f4-44af-8218-03ca3864b267 ストアドプロシージャを

メッセージ図形の追加

メッセージ図形ごとに次のプロパティを指定してください。 [図形] 列に表示される名前は、前述のオーケストレーションに表示されるメッセージ図形の名前です。

図形	図形の種類	プロパティ
ReceiveMessage	受信	- 名前を ReceiveMessage に設定する - アクティブ化を True に設定する
SendMessage	Send	- 名前を SendMessage に設定する
ReceiveResponse	受信	- 名前を ReceiveResponse に設定する - アクティブ化を False に設定する
SendResponse	Send	- 名前を SendResponse に設定する

ポートの追加

論理ポートごとに次のプロパティを指定してください。 [ポート] 列に表示される名前は、オーケストレーションに表示されるポートの名前です。

Port	プロパティ
MessageIn	- 識別子を MessageIn に設定する - Type を MessageInType に設定する - 通信パターンを一方向に設定する - 通信方向を受信に設定する
LOBPort	- 識別子を LOBPort に設定する - 型を LOBPortType に設定する - 通信パターンを Request-Response に設定する - [通信の方向] を [送受信] に設定する
ResponseOut	- 識別子を ResponseOut に設定する - Type を ResponseOutType に設定する - 通信パターンを一方向に設定する - [通信の方向] を [送信] に設定する

アクション図形のメッセージを指定し、ポートに接続する

次の表では、アクション図形のメッセージを指定し、メッセージをポートにリンクするために設定するプロパティとその値を指定します。 [図形] 列に表示される名前は、前述のオーケストレーションに表示されるメッセージ図形の名前です。

図形	プロパティ
ReceiveMessage	- メッセージを要求に設定する - 操作を MessageIn.FOR_XML.Request に設定する
SendMessage	- メッセージを要求に設定する - 操作を LOBPort.FOR_XML.Request に設定する
ReceiveResponse	- メッセージを応答に設定する - 操作を LOBPort.FOR_XML.Response に設定する
SendResponse	- メッセージを応答に設定する - 操作を ResponseOut.FOR_XML.Request に設定する

これらのプロパティを指定すると、メッセージの図形とポートが接続され、オーケストレーションが完了します。

BizTalk ソリューションをビルドし、BizTalk Serverに展開する必要があります。詳細については、「オーケストレーションのビルドと実行」を参照してください。

BizTalk アプリケーションの構成

BizTalk プロジェクトを展開すると、先ほど作成したオーケストレーションが、BizTalk Server管理コンソールの [オーケストレーション] ウィンドウの下に一覧表示されます。アプリケーションを構成するには、BizTalk Server管理コンソールを使用する必要があります。チュートリアルについては、「チュートリアル: 基本的な BizTalk アプリケーションの展開」を参照してください。

アプリケーションの構成には、次の作業が含まれます。

アプリケーションのホストの選択。

オーケストレーションで作成したポートを、BizTalk Server管理コンソールの物理ポートにマッピングします。このオーケストレーションでは、次の操作を行う必要があります。

ハードディスク上の場所と、要求メッセージをドロップする対応するファイルポートを定義します。 BizTalk オーケストレーションは要求メッセージを使用し、SQL Serverデータベースに送信します。
ハードディスク上の場所と、BizTalk オーケストレーションがデータベースからの応答を含む応答メッセージを削除する対応するファイルポートSQL Server定義します。

データベースにメッセージを送信する物理 WCF-Custom または WCF-SQL 送信ポートSQL Server定義します。送信ポートを作成する方法については、「 SQL アダプターへの物理ポートバインドを手動で構成する」を参照してください。

また、送信ポートでアクションを指定する必要があります。 FOR XML 句を含むプロシージャの場合は、アクションを次の形式で設定する必要があります。

XmlProcedure/<schema_name>/<procedure_name>

そのため、GET_EMP_DETAILS_FOR_XML プロシージャを実行するこのトピックでは、アクションは次のようになります。

XmlProcedure/dbo/GET_EMP_DETAILS_FOR_XML

アクションの設定の詳細については、「 SQL アダプターの SOAP アクションを構成する」を参照してください。

FOR XML 句を使用してストアドプロシージャを実行する場合は、次のバインドプロパティも設定する必要があります。

バインドプロパティ名	これを
XmlStoredProcedureRootNodeName	ストアドプロシージャの応答メッセージのスキーマの生成に関するページで説明されているように、ストアドプロシージャ用に生成した応答スキーマに追加したルートノードの名前を指定します。このトピックでは、これを [ルート] に設定します。
XmlStoredProcedureRootNodeNamespace	ストアドプロシージャの応答メッセージのスキーマの生成に関するページで説明されているように、ストアドプロシージャ用に生成した応答スキーマのターゲット名前空間を指定します。このトピックでは、これをに設定します `http://ForXmlStoredProcs/namespace`。

バインドプロパティの値の指定の詳細については、「 SQL アダプターのバインドプロパティを構成する」を参照してください。

Note

アダプターサービス BizTalk プロジェクトアドインを使用してスキーマを生成すると、ポートに関する情報と、それらのポートに設定するアクションを含むバインドファイルも作成されます。 BizTalk Server管理コンソールからこのバインドファイルをインポートして、送信ポート (送信呼び出しの場合) または受信ポート (受信呼び出し用) を作成できます。詳細については、「 SQL アダプターを使用するようにポートバインドファイルを使用して物理ポートバインドを構成する」を参照してください。

アプリケーションの起動

データベースでプロシージャを呼び出す場合は、BizTalk アプリケーションSQL Server起動する必要があります。 BizTalk アプリケーションを起動する手順については、「オーケストレーションを開始する方法」を参照してください。

この段階で、次のことを確認します。

オーケストレーションの要求メッセージを受信する FILE 受信ポートが実行されています。
オーケストレーションから応答メッセージを受信する FILE 送信ポートが実行されています。
データベースにメッセージを送信する WCF-Custom または WCF-SQL 送信ポートSQL Server実行されています。
操作の BizTalk オーケストレーションが実行されています。

操作の実行

アプリケーションを実行した後、FILE 受信場所に要求メッセージをドロップする必要があります。要求メッセージのスキーマは、アダプターサービスアドインを使用して生成したプロシージャの要求スキーマに準拠している必要があります。たとえば、GET_EMP_DETAILS_FOR XML を呼び出す要求メッセージは次のとおりです。

<GET_EMP_DETAILS_FOR_XML xmlns="http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo">
  <emp_id>10765</emp_id>
</GET_EMP_DETAILS_FOR_XML>

SQL アダプターを使用してデータベースでプロシージャを呼び出すための要求メッセージスキーマの詳細については、「プロシージャと関数のメッセージスキーマSQL Server参照してください。

オーケストレーションによってメッセージが使用され、データベースに送信SQL Server。 SQL Server データベースからの応答は、オーケストレーションの一部として定義されている他の FILE の場所に保存されます。たとえば、上記の要求メッセージSQL Serverデータベースからの応答は次のようになります。

<?xml version="1.0" encoding="utf-8"?>
<Root xmlns="http://ForXmlStoredProcs/namespace">
  <Adapt_Doc.dbo.Employee Employee_ID="10765" Name="John" Designation="asdfaf" Salary="3434.00" Last_Modified="AAAAAAAANso=" Status="0" xmlns="" />
</Root>

ストアドプロシージャを実行して生成されたのと同じスキーマで応答が受信されていることに注意してください。また、ルートノードと名前空間は、 それぞれ XmlStoredProcedureRootNodeName および XmlStoredProcedureRootNodeNamespace バインドプロパティの値として指定したのと同じであることに注意してください。

ベストプラクティス

BizTalk プロジェクトを展開して構成したら、構成設定をバインドファイルと呼ばれる XML ファイルにエクスポートできます。バインドファイルを生成したら、ファイルから構成設定をインポートして、同じオーケストレーションの送信ポートや受信ポートなどの項目を作成する必要がないようにすることができます。バインドファイルの詳細については、「アダプターバインドを再利用する」を参照してください。

参照

SQL アダプターを使用して BizTalk アプリケーションを開発する

次の方法で共有

BizTalk Serverを使用して、SQL Serverで FOR XML 句を持つストアドプロシージャを実行する

FOR XML 句を使用してストアドプロシージャを呼び出す方法

ストアドプロシージャの応答メッセージのスキーマの生成

BizTalk プロジェクトにスキーマを追加するには

ストアドプロシージャを呼び出す要求メッセージのスキーマの生成

メッセージとメッセージの種類の定義

メッセージを作成し、スキーマにリンクするには

オーケストレーションの設定

メッセージ図形の追加

ポートの追加

アクション図形のメッセージを指定し、ポートに接続する

BizTalk アプリケーションの構成

アプリケーションの起動

操作の実行

ベストプラクティス

参照

フィードバック

フィードバック

その他のリソース

次の方法で共有

BizTalk Serverを使用して、SQL Serverで FOR XML 句を持つストアド プロシージャを実行する

FOR XML 句を使用してストアド プロシージャを呼び出す方法

ストアド プロシージャの応答メッセージのスキーマの生成

BizTalk プロジェクトにスキーマを追加するには

ストアド プロシージャを呼び出す要求メッセージのスキーマの生成

メッセージとメッセージの種類の定義

メッセージを作成し、スキーマにリンクするには

オーケストレーションの設定

メッセージ図形の追加

ポートの追加

アクション 図形のメッセージを指定し、ポートに接続する

BizTalk アプリケーションの構成

アプリケーションの起動

操作の実行

ベスト プラクティス

参照

フィードバック

フィードバック

その他のリソース

BizTalk Serverを使用して、SQL Serverで FOR XML 句を持つストアドプロシージャを実行する

FOR XML 句を使用してストアドプロシージャを呼び出す方法

ストアドプロシージャの応答メッセージのスキーマの生成

ストアドプロシージャを呼び出す要求メッセージのスキーマの生成

アクション図形のメッセージを指定し、ポートに接続する

ベストプラクティス