Ausführen gespeicherter Prozeduren mit einer FOR XML-Klausel in SQL Server mit BizTalk Server

Eine SQL SELECT-Anweisung kann eine FOR XML-Klausel aufweisen, die das Abfrageergebnis als XML anstelle eines Rowsets zurückgibt. Sie können auch eine gespeicherte Prozedur mit einer SELECT-Anweisung mit einer FOR XML-Klausel haben. FOR XML (SQL Server) enthält weitere Informationen.

Sie können den WCF-basierten SQL-Adapter verwenden, um solche gespeicherten Prozeduren auszuführen.

So wird's gemacht: Aufrufen von gespeicherten Prozeduren mit FOR XML-Klausel

Wenn Sie eine gespeicherte Prozedur mit FOR XML-Klausel in SQL Server Management Studio aufrufen oder den sql-Adapter verwenden, der mit BizTalk Server verfügbar ist, befindet sich die Ausgabe in Form einer XML-Nachricht. Um diese Verfahren mit dem WCF-basierten SQL-Adapter zu verwenden, müssen Sie über das Schema für die Ausgabemeldung verfügen. Der WCF-basierte SQL-Adapter erfordert dieses Schema, während die Antwortnachricht von SQL Server empfangen wird, nachdem eine gespeicherte Prozedur mit FOR XML-Klausel ausgeführt wurde. Beachten Sie, dass die Anforderungsmeldung zum Aufrufen dieser gespeicherten Prozedur vom Adapter selbst generiert wird.

Abgesehen vom Schema für die Antwortnachricht müssen Sie auch bestimmte Aufgaben ausführen, um eine gespeicherte Prozedur mit FOR XML-Klausel mithilfe des WCF-basierten SQL-Adapters aufzurufen.

1. Generieren Sie das Schema für die Antwortnachricht für die gespeicherte Prozedur mit FOR XML-Klausel. Wenn Sie bereits über das Antwortschema verfügen, das vom "nativen" SQL-Adapter generiert wurde, der mit BizTalk Server verfügbar ist, können Sie diesen Schritt überspringen.

2. Erstellen Sie ein BizTalk-Projekt, und fügen Sie dem Projekt das generierte Schema hinzu.

3. Generieren Sie das Schema für die gespeicherte Prozedur mit FOR XML-Klausel mithilfe des WCF-basierten SQL-Adapters. Dadurch wird das Schema für die Anforderungsmeldung bereitgestellt, die der Adapter an SQL Server sendet, um die gespeicherte Prozedur aufzurufen.

4. Erstellen Sie Nachrichten im BizTalk-Projekt, um Nachrichten von SQL Server zu senden und zu empfangen. Die Anforderungsnachricht muss dem Schema der vom Adapter generierten Anforderungsnachricht entsprechen. Die Antwortnachricht muss dem Schema der Antwortnachricht entsprechen, die entweder mithilfe des "nativen" SQL-Adapters oder durch Ausführen der gespeicherten Prozedur mit FOR XML-Klausel in SQL Server Management Studio abgerufen wird.

5. Erstellen Sie eine Orchestrierung, um die gespeicherte Prozedur in der SQL Server-Datenbank aufzurufen.

6. Erstellen und Bereitstellen des BizTalk-Projekts.

7. Konfigurieren Sie die BizTalk-Anwendung, indem Sie physische Sende- und Empfangsports erstellen.

8. Starten Sie die BizTalk-Anwendung.

Generieren des Schemas für die Antwortnachricht für gespeicherte Prozedur

Sie können das Schema für die Antwortnachricht für die gespeicherte Prozedur generieren, vorausgesetzt, die SELECT-Anweisung in der gespeicherten Prozedur weist die xmlschema Klausel mit der for xml Klausel auf. In diesem Thema verwenden wir die gespeicherte Prozedur GET_EMP_DETAILS_FOR_XML, die die Mitarbeiterdetails für eine bestimmte Mitarbeiter-ID abruft. Zum Abrufen des Schemas durch Ausführen der gespeicherten Prozedur sieht die SELECT-Anweisung wie folgt aus:

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

Führen Sie diese gespeicherte Prozedur aus, um das Schema für die Antwortnachricht abzurufen. Beachten Sie, dass die Antwort der gespeicherten Prozedur das Schema sowie die Daten aus der Ausführung der gespeicherten Prozedur enthält. Sie müssen das Schema aus der Antwort kopieren und auf einem Textblock speichern. In diesem Beispiel können Sie dieses Schema als ResponseSchema.xsd benennen. Sie müssen nun ein BizTalk-Projekt in Visual Studio erstellen und dieses Schema dem Projekt hinzufügen.

So fügen Sie das Schema einem BizTalk-Projekt hinzu

1. Erstellen Sie ein BizTalk-Projekt in Visual Studio.

2. Fügen Sie das für die gespeicherte Prozedur generierte Antwortschema zum BizTalk-Projekt hinzu. Klicken Sie im Projektmappen-Explorer mit der rechten Maustaste auf das BizTalk-Projekt, zeigen Sie auf "Hinzufügen", und klicken Sie dann auf "Vorhandenes Element". Navigieren Sie im Dialogfeld "Vorhandenes Element hinzufügen" zu dem Speicherort, an dem Sie das Schema gespeichert haben, und klicken Sie auf "Hinzufügen".

3. Öffnen Sie das Schema in Visual Studio, und nehmen Sie die folgenden Änderungen vor.

   1. Fügen Sie dem Schema einen Knoten hinzu, und verschieben Sie den vorhandenen Stammknoten unter diesem neu hinzugefügten Knoten. Geben Sie dem Stammknoten einen Namen. Benennen Sie für dieses Thema den Stammknoten in "Stamm" um.

   2. Das für die gespeicherte Prozedur generierte Antwortschema verweist auf eine sqltypes.xsd. Sie können das sqltypes.xsd-Schema von https://go.microsoft.com/fwlink/?linkid=31850 abrufen. Fügen Sie das sqltypes.xsd-Schema zum BizTalk-Projekt hinzu. Weitere Informationen zu diesem Schema finden Sie unter:

      • 2.2.5.2 sqlTypes
      • Verweisen auf die integrierte XML-Schemaauflistung (sys)

   3. Ändern Sie im Schema, das für die gespeicherte Prozedur erstellt wurde, den Wert von import schemaLocation auf Folgendes.

      import schemaLocation=”sqltypes.xsd”
      

      Dies geschieht, da Sie ihrem BizTalk-Projekt bereits das Schema "sqltypes.xsd" hinzugefügt haben.

   4. Geben Sie einen Zielnamespace für das Schema an. Klicken Sie auf den <Schemaknoten>, und geben Sie im Eigenschaftenbereich einen Namespace in der Eigenschaft Ziel-Namespace an. Geben Sie für dieses Thema den Namespace http://ForXmlStoredProcs/namespace an.

Generieren des Schemas für die Anforderungsnachricht zum Aufrufen der gespeicherten Prozedur

Um ein Schema für die Anforderungsnachricht zu generieren, können Sie das "Consume Adapter Service"-Add-In aus einem BizTalk-Projekt in Visual Studio verwenden. Generieren Sie für dieses Thema das Schema für die gespeicherte GET_EMP_DETAILS_FOR_XML Prozedur. Weitere Informationen dazu, wie Sie das Schema mit dem Consume Adapter Service-Add-in generieren können, finden Sie unter Abrufen von Metadaten für SQL Server-Vorgänge in Visual Studio mithilfe des SQL-Adapters.

Definieren von Nachrichten und Nachrichtentypen

Das schema, das Sie zuvor generiert haben, beschreibt die für die Nachrichten in der Orchestrierung erforderlichen "Typen". Eine Nachricht ist in der Regel eine Variable, deren Typ durch das entsprechende Schema definiert wird. Sie müssen jetzt Nachrichten für die Orchestrierung erstellen und mit Schemas verknüpfen, die Sie im vorherigen Schritt generiert haben.

So erstellen Sie Nachrichten und Verknüpfungen zum Schema

1. Fügen Sie dem BizTalk-Projekt eine Orchestrierung hinzu. Klicken Sie im Projektmappen-Explorer mit der rechten Maustaste auf den Namen des BizTalk-Projekts, zeigen Sie auf "Hinzufügen", und klicken Sie dann auf "Neues Element". Geben Sie einen Namen für die BizTalk-Orchestrierung ein, und klicken Sie dann auf "Hinzufügen".

2. Öffnen Sie das Fenster "Orchestrierungsansicht" des BizTalk-Projekts, wenn es noch nicht geöffnet ist. Klicken Sie dazu auf Ansicht, zeigen Sie auf Andere Fenster, und klicken Sie dann auf Orchestrierungsansicht.

3. Klicken Sie in der Orchestrierungsansicht mit der rechten Maustaste auf "Nachrichten", und klicken Sie dann auf "Neue Nachricht".

4. Klicken Sie mit der rechten Maustaste auf die neu erstellte Nachricht, und wählen Sie dann "Eigenschaftenfenster" aus.

5. Gehen Sie im Eigenschaftenbereich für die Message_1 wie folgt vor:

   Verwenden Sie diese	Aktion
   Kennung	Geben Sie Request ein
   Nachrichtentyp	Erweitern Sie in der Dropdown-Liste Schemas, und wählen Sie dann ForXMLProcedure.Procedure_dbo.GET_EMP_DETAILS_FOR_XML aus, wobei ForXMLProcedure der Name Ihres BizTalk-Projekts ist. Procedure_dbo ist das Schema, das zum Aufrufen der Prozedur GET_EMP_DETAILS_FOR_XML generiert wird.

6. Wiederholen Sie Schritt 2, um eine neue Nachricht zu erstellen. Gehen Sie im Eigenschaftenbereich für die neue Nachricht wie folgt vor:

   Verwenden Sie diese	Aktion
   Kennung	Geben Sie Response ein
   Nachrichtentyp	Erweitern Sie in der Dropdownliste Schemas, und wählen Sie dann ForXMLProcedure.ResponseSchema aus, wobei ResponseSchema der Name des Antwortschemas ist, das durch Ausführen der gespeicherten Prozedur generiert wird, wie unter Generieren des Schemas für die Antwortnachricht für gespeicherte Prozedur beschrieben.

Einrichten der Orchestrierung

Sie müssen eine BizTalk-Orchestrierung erstellen, um BizTalk Server zum Ausführen einer gespeicherten Prozedur in SQL Server zu verwenden. In dieser Orchestrierung legen Sie eine Anforderungsnachricht an einem definierten Empfangsort ab. Der SQL-Adapter verwendet diese Meldung und übergibt sie an SQL Server. Die Antwort von SQL Server wird an einem anderen Speicherort gespeichert. Sie müssen "Senden" und "Empfangen"-Shapes einschließen, um Nachrichten an SQL Server zu senden und Antworten zu empfangen. Eine Beispiel-Orchestrierung für das Aufrufen eines Verfahrens sieht wie folgt aus:

Hinzufügen von Nachrichtenformen

Stellen Sie sicher, dass Sie die folgenden Eigenschaften für die einzelnen Nachrichten-Shapes angeben. Die in der Spalte "Shape" aufgeführten Namen sind die der Nachrichten-Shapes, die in der gerade erwähnten Orchestrierung angezeigt werden.

Hinzufügen von Ports

Stellen Sie sicher, dass Sie die folgenden Eigenschaften für jeden der logischen Ports angeben. Die in der Spalte "Port" aufgeführten Namen sind die Namen der Ports, die in der Orchestrierung angezeigt werden.

Nachrichten für Aktions-Shapes spezifizieren und mit Ports verbinden

In der folgenden Tabelle werden die Eigenschaften und deren Werte angegeben, die Sie festlegen sollten, um Nachrichten für Aktions-Shapes anzugeben und die Nachrichten mit den Ports zu verknüpfen. Die in der Spalte "Shape" aufgeführten Namen sind die Namen der Nachrichten-Shapes, wie in der zuvor erwähnten Orchestrierung angezeigt.

Nachdem Sie diese Eigenschaften angegeben haben, sind die Nachrichtenformen und Ports verbunden, und die Orchestrierung ist abgeschlossen.

Sie müssen nun die BizTalk-Lösung erstellen und auf einem BizTalk Server bereitstellen. Weitere Informationen finden Sie unter Building and Running Orchestrations.

Konfigurieren der BizTalk-Anwendung

Nachdem Sie das BizTalk-Projekt bereitgestellt haben, wird die zuvor erstellte Orchestrierung im Bereich "Orchestrierungen" in der BizTalk Server-Verwaltungskonsole aufgeführt. Sie müssen die BizTalk Server-Verwaltungskonsole verwenden, um die Anwendung zu konfigurieren. Eine exemplarische Vorgehensweise finden Sie unter Walkthrough: Deploying a Basic BizTalk Application.

Das Konfigurieren einer Anwendung umfasst Folgendes:

• Auswählen eines Hosts für die Anwendung.

• Zuordnen der ports, die Sie in Ihrer Orchestrierung erstellt haben, zu physischen Ports in der BizTalk Server-Verwaltungskonsole. Für diese Orchestrierung müssen Sie:

  • Definieren Sie einen Speicherort auf der Festplatte und einen entsprechenden Dateiport, an dem Sie eine Anforderungsnachricht ablegen. Die BizTalk-Orchestrierung verwendet die Anforderungsnachricht und sendet sie an die SQL Server-Datenbank.

  • Definieren Sie einen Speicherort auf der Festplatte und einen entsprechenden Dateiport, an dem die BizTalk-Orchestrierung die Antwortnachricht mit der Antwort aus der SQL Server-Datenbank absendet.

  • Definieren Sie einen physischen WCF-Custom- oder WCF-SQL-Sendport zum Senden von Nachrichten an die SQL Server-Datenbank. Anweisungen zum Erstellen eines Sendeports finden Sie unter Manuelles Konfigurieren einer physischen Portbindung an den SQL-Adapter.

    Sie müssen auch die Aktion im Sende-Port angeben. Für Prozeduren, die die FOR XML-Klausel enthalten, müssen Sie die Aktion im folgenden Format festlegen.

    XmlProcedure/<schema_name>/<procedure_name>
    

    Für dieses Thema, in dem wir die GET_EMP_DETAILS_FOR_XML-Prozedur ausführen, lautet die Aktion:

    XmlProcedure/dbo/GET_EMP_DETAILS_FOR_XML
    

    Weitere Informationen zum Festlegen der Aktion finden Sie unter Konfigurieren der SOAP-Aktion für den SQL-Adapter .

    Sie müssen auch die folgenden Bindungseigenschaften festlegen, wenn Sie eine gespeicherte Prozedur mit der FOR XML-Klausel ausführen.

    Name der Bindungseigenschaft	Legen Sie dies auf
    XmlStoredProcedureRootNodeName	Geben Sie den Namen des Stammknotens an, den Sie dem Antwortschema hinzugefügt haben, das Sie für die gespeicherte Prozedur generiert haben, wie unter Generieren des Schemas für die Antwortnachricht für gespeicherte Prozedur beschrieben. Legen Sie dies für dieses Thema auf "Root" fest.
    XmlStoredProcedureRootNodeNamespace	Geben Sie den Zielnamespace für das Antwortschema an, das Sie für die gespeicherte Prozedur generiert haben, wie unter Generieren des Schemas für die Antwortnachricht für gespeicherte Prozedur beschrieben. Setzen Sie dies für dieses Thema auf http://ForXmlStoredProcs/namespace.

    Weitere Informationen zum Angeben von Werten für Bindungseigenschaften finden Sie unter Konfigurieren der Bindungseigenschaften für den SQL-Adapter.

    Hinweis

    Durch das Generieren des Schemas mithilfe des Add-Ins "Verbrauchsadapterdienst BizTalk Project" wird auch eine Bindungsdatei erstellt, die Informationen zu den Ports und den aktionen enthält, die für diese Ports festgelegt werden sollen. Sie können diese Bindungsdatei aus der BizTalk Server-Verwaltungskonsole importieren, um Sendeports (für ausgehende Anrufe) oder Empfangsports (für eingehende Anrufe) zu erstellen. Weitere Informationen finden Sie unter Konfigurieren einer physischen Portbindung mithilfe einer Portbindungsdatei zur Verwendung des SQL-Adapters.

Starten der Anwendung

Sie müssen die BizTalk-Anwendung zum Aufrufen von Prozeduren in der SQL Server-Datenbank starten. Anweisungen zum Starten einer BizTalk-Anwendung finden Sie unter "So starten Sie eine Orchestrierung".

Stellen Sie in dieser Phase folgendes sicher:

• Der FILE-Empfangsanschluss zum Erhalten von Anforderungsmeldungen für die Orchestrierung ist in Betrieb.

• Der FILE-Sendeport zum Empfangen der Antwortnachrichten von der Orchestrierung wird ausgeführt.

• Der WCF-Custom oder WCF-SQL Port zum Senden von Nachrichten an die SQL Server-Datenbank wird ausgeführt.

• Die BizTalk-Orchestrierung für den Vorgang wird ausgeführt.

Ausführen des Vorgangs

Nachdem Sie die Anwendung ausgeführt haben, müssen Sie eine Anforderungsmeldung am Eingangs-Ort für DATEI ablegen. Das Schema für die Anforderungsnachricht muss dem Anforderungsschema für die Prozedur entsprechen, die Sie mithilfe des Add-Ins "Adapterdienst nutzen" generiert haben. Die Anforderungsnachricht zum Aufrufen der GET_EMP_DETAILS_FOR XML lautet beispielsweise:

<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>

Weitere Informationen zum Anforderungsschemas zum Aufrufen von Prozeduren in der SQL Server-Datenbank finden Sie unter Message Schemas for Procedures and Functions.

Die Orchestrierung verwendet die Nachricht und sendet sie an die SQL Server-Datenbank. Die Antwort aus der SQL Server-Datenbank wird am anderen DATEIspeicherort gespeichert, der als Teil der Orchestrierung definiert ist. Die Antwort aus der SQL Server-Datenbank für die vorherige Anforderungsnachricht lautet beispielsweise:

<?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>

Beachten Sie, dass die Antwort im gleichen Schema empfangen wird, das durch das Ausführen der gespeicherten Prozedur erzeugt wurde. Beachten Sie außerdem, dass der Stammknoten und der Namespace identisch sind, den Sie als Werte für die Bindungseigenschaften "XmlStoredProcedureRootNodeName " bzw. "XmlStoredProcedureRootNodeNamespace " angegeben haben.

Bewährte Methoden

Nachdem Sie das BizTalk-Projekt bereitgestellt und konfiguriert haben, können Sie Konfigurationseinstellungen in eine XML-Datei exportieren, die als Bindungsdatei bezeichnet wird. Nachdem Sie eine Bindungsdatei generiert haben, können Sie die Konfigurationseinstellungen aus der Datei importieren, sodass Sie keine Elemente wie Sendeports und Empfangsports für dieselbe Orchestrierung erstellen müssen. Weitere Informationen zu Bindungsdateien finden Sie unter Wiederverwenden von Adapterbindungen.

Siehe auch

Entwickeln von BizTalk-Anwendungen mithilfe des SQL-Adapters