使用 BizTalk Server，在 SQL Server 中執行具有 FOR XML 子句的預存程式

SQL SELECT 語句可以有 FOR XML 子句，以 XML 而非資料列集的形式傳回查詢結果。 您也可以使用 FOR XML 子句具有 SELECT 語句的預存程式。 FOR XML (SQL Server) 有詳細資訊。

您可以使用 WCF 型 SQL 配接器來執行這類預存程式。

重要

BizTalk Server可用的「原生」SQL 配接器需要預存程式讓 FOR XML 子句成為 SELECT 語句的一部分。 您可以搭配 WCF 型 SQL 配接器使用這類預存程式，而不需對預存程式定義進行任何變更。

您一律可以使用舊版BizTalk Server所提供的「原生」SQL 配接器所產生的架構。 如需詳細資訊，請參閱 搭配 WCF-SQL 配接器使用 FOR XML 查詢。

如何使用 FOR XML 子句叫用預存程式

當您在 SQL Server Management Studio中使用 FOR XML 子句叫用預存程式，或使用可搭配BizTalk Server使用的 SQL 配接器時，輸出的格式為 xml 訊息。 若要搭配 WCF 型 SQL 配接器使用這些程式，您必須具有輸出訊息的架構。 使用 FOR XML 子句執行預存程式之後，WCF 型 SQL 配接器需要此架構，同時從SQL Server接收回應訊息。 請注意，要叫用此預存程式的要求訊息將由配接器本身產生。

除了具有回應訊息的架構之外，您也必須執行某些工作，以使用 WCF 型 SQL 配接器搭配 FOR XML 子句叫用預存程式。

1. 使用 FOR XML 子句產生預存程式的回應訊息架構。 如果您已經有「原生」SQL 配接器所產生的回應架構可供BizTalk Server使用，您可以略過此步驟。

2. 建立 BizTalk 專案，並將產生的架構新增至專案。

3. 使用 WCF 型 SQL 配接器，使用 FOR XML 子句產生預存程式的架構。 這會提供配接器傳送給SQL Server叫用預存程式之要求訊息的架構。

4. 在 BizTalk 專案中建立訊息，以傳送和接收來自SQL Server的訊息。 要求訊息必須符合配接器所產生的要求訊息架構。 回應訊息必須符合使用 「native」 SQL 配接器取得的回應訊息架構，或在 SQL Server Management Studio 中使用 FOR XML 子句執行預存程式。

5. 建立協調流程以叫用SQL Server資料庫中的預存程式。

6. 建置和部署 BizTalk 專案。

7. 藉由建立實體傳送和接收埠來設定 BizTalk 應用程式。

8. 啟動 BizTalk 應用程式。

為預存程式的回應訊息產生架構

注意

如果您有 SQL 配接器所產生的回應架構可供BizTalk Server使用，則不需要執行此步驟。

您可以產生預存程式的回應訊息架構，前提是預存程式中的 SELECT 語句具有 xmlschema 子句與 for xml 子句。 在本主題中，我們會使用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 專案

1. 在 Visual Studio 中建立 BizTalk 專案。

2. 將您為預存程式產生的回應架構新增至 BizTalk 專案。 以滑鼠右鍵按一下方案總管中的 BizTalk 專案，指向 [新增]，然後按一下 [現有專案]。 在 [新增現有專案] 對話方塊中，流覽至您儲存架構的位置，然後按一下 [ 新增]。

3. 在 Visual Studio 中開啟架構，並進行下列變更。

   1. 將節點新增至架構，並在這個新增的節點下移動現有的根節點。 為根節點命名。 針對本主題，請將根節點重新命名為 Root。

   2. 為預存程式產生的回應架構會參考 sqltypes.xsd。 您可以從 取得 sqltypes.xsd 架構 https://go.microsoft.com/fwlink/?linkid=31850 。 將 sqltypes.xsd 架構新增至 BizTalk 專案。 如需此架構的詳細資訊，請移至：

      • 2.2.5.2 sqlTypes
      • 參考 sys) 的內建 XML 架構集合 (

   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作業的中繼資料。

重要

您只能從取用配接器服務增益集中的 [ 程式 ] 節點選取程式，以產生架構。

定義訊息和訊息類型

您稍早產生的架構描述協調流程中訊息所需的「類型」。 訊息通常是變數，其類型是由對應的架構所定義。 您現在必須建立協調流程的訊息，並將其連結至您在上一個步驟中產生的架構。

建立訊息並連結至架構

1. 將協調流程新增至 BizTalk 專案。 從 方案總管，以滑鼠右鍵按一下 BizTalk 專案名稱，指向 [新增]，然後按一下 [新增專案]。 輸入 BizTalk 協調流程的名稱，然後按一下 [ 新增]。

2. 如果 BizTalk 專案尚未開啟，請開啟 [協調流程檢視] 視窗。 若要這樣做，請按一下 [ 檢視]，指向 [其他視窗]，然後按一下 [ 協調流程檢視]。

3. 在 [協調流程檢視] 中，以滑鼠右鍵按一下 [ 訊息]，然後按一下 [ 新增訊息]。

4. 以滑鼠右鍵按一下新建立的訊息，然後選取 [ 屬性視窗]。

5. 在Message_1的 [屬性] 窗格中，執行下列動作：

   使用	作法
   識別碼	輸入 Request
   訊息類型	從下拉式清單中，展開 [ 架構]，然後選取 [ForXMLProcedure.Procedure_dbo]。GET_EMP_DETAILS_FOR_XML，其中 ForXMLProcedure 是您 BizTalk 專案的名稱。 Procedure_dbo是用來叫用GET_EMP_DETAILS_FOR_XML程式的架構。

6. 重複步驟 2 以建立新的訊息。 在新訊息的 [ 屬性 ] 窗格中，執行下列動作：

   使用	作法
   識別碼	輸入 Response
   訊息類型	從下拉式清單中，展開 [ 架構]，然後選取 [ForXMLProcedure.ResponseSchema]，其中 ResponseSchema是執行預存程式所產生的回應架構名稱，如為 預存程式產生回應訊息底下所述。

設定協調流程

您必須建立 BizTalk 協調流程，才能使用 BizTalk Server 在 SQL Server 中執行預存程式。 在此協調流程中，您會在定義的接收位置卸載要求訊息。 SQL 配接器會取用此訊息，並將其傳遞至SQL Server。 來自SQL Server的回應會儲存到另一個位置。 您必須包含傳送和接收圖形，以分別將訊息傳送至SQL Server和接收回應。 叫用程式的範例協調流程如下所示：

新增訊息圖形

請確定您為每個訊息圖形指定下列屬性。 Shape 資料行中列出的名稱是訊息圖形的名稱，如剛才提及的協調流程所示。

圖形	圖形類型	屬性
ReceiveMessage	接收	- 將 [名稱 ] 設定為 ReceiveMessage - 將 Activate 設定為 True
SendMessage	傳送	- 將 [名稱 ] 設定為 SendMessage
ReceiveResponse	接收	- 將 名稱 設定為 ReceiveResponse - 將 Activate 設定為 False
SendResponse	傳送	- 將 名稱 設定為 SendResponse

新增埠

請確定您為每個邏輯埠指定下列屬性。 [埠] 資料行中列出的名稱是協調流程中顯示的埠名稱。

連接埠	屬性
MessageIn	- 將 識別碼 設定為 MessageIn - 將 類型 設定為 MessageInType - 將 通訊模式 設定為 單向 - 將 通訊方向 設定為 接收
LOBPort	- 將 識別碼 設定為 LOBPort - 將 類型 設定為 LOBPortType - 將 通訊模式 設定為 Request-Response - 將 通訊方向 設定為 Send-Receive
ResponseOut	- 將 識別碼 設定為 ResponseOut - 將 類型 設定為 ResponseOutType - 將 通訊模式 設定為 單向 - 設定要傳送的通訊方向

指定動作圖形的訊息，並將其連線至埠

下表指定屬性及其值，您應該設定為指定動作圖形的訊息，以及將訊息連結至埠。 Shape 資料行中列出的名稱是訊息圖形的名稱，如先前所述的協調流程所示。

圖形	屬性
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	指定您為預存程式產生的回應架構新增至回應架構的根節點名稱，如 產生預存程式的回應訊息架構中所述。 針對本主題，請將此設定為 Root。
    XmlStoredProcedureRootNodeNamespace	針對預存程式產生的回應架構指定目標命名空間，如為 預存程式產生回應訊息的架構中所述。 針對本主題，請將此設定為 http://ForXmlStoredProcs/namespace 。

    如需指定系結屬性值的詳細資訊，請參閱 設定 SQL 配接器的系結屬性。

    注意

    使用取用配接器服務 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 應用程式