Freigeben über

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

  • Artikel

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

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

Wichtig

Der mit BizTalk Server verfügbare "native" SQL-Adapter erfordert, dass gespeicherte Prozeduren über die FOR XML-Klausel als Teil der SELECT-Anweisung verfügen. Sie können solche gespeicherten Prozeduren mit dem WCF-basierten SQL-Adapter verwenden, ohne änderungen an der Definition gespeicherter Prozeduren vorzunehmen.

Sie können immer die Schemas verwenden, die mit dem "nativen" SQL-Adapter generiert wurden, der mit früheren Versionen von BizTalk Server bereitgestellt wurde. Weitere Informationen finden Sie unter Verwenden von FOR XML-Abfragen mit dem WCF-SQL-Adapter.

Aufrufen gespeicherter Prozeduren mit der FOR XML-Klausel

Wenn Sie eine gespeicherte Prozedur mit der FOR XML-Klausel in SQL Server Management Studio aufrufen oder den SQL-Adapter verwenden, der mit BizTalk Server verfügbar ist, erfolgt die Ausgabe in Form einer XML-Nachricht. Um diese Prozeduren mit dem WCF-basierten SQL-Adapter verwenden zu können, müssen Sie über das Schema für die Ausgabenachricht verfügen. Der WCF-basierte SQL-Adapter erfordert dieses Schema beim Empfangen der Antwortnachricht von SQL Server nach dem Ausführen einer gespeicherten Prozedur mit der FOR XML-Klausel. Beachten Sie, dass die Anforderungsmeldung zum Aufrufen dieser gespeicherten Prozedur vom Adapter selbst generiert wird.

Neben dem Schema für die Antwortnachricht müssen Sie auch bestimmte Aufgaben ausführen, um eine gespeicherte Prozedur mit der 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 der FOR XML-Klausel. Wenn Sie bereits das vom "nativen" SQL-Adapter generierte Antwortschema mit BizTalk Server verfügbar haben, 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 ein Schema für die gespeicherte Prozedur mit der FOR XML-Klausel mithilfe des WCF-basierten SQL-Adapters. Dadurch wird das Schema für die Anforderungsnachricht 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 der 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 eines Schemas für die Antwortnachricht für gespeicherte Prozedur

Hinweis

Sie müssen diesen Schritt nicht ausführen, wenn Das vom SQL-Adapter generierte Antwortschema mit BizTalk Server verfügbar ist.

Sie können das Schema für die Antwortnachricht für die gespeicherte Prozedur generieren, sofern die SELECT-Anweisung in der gespeicherten Prozedur über die xmlschema -Klausel verfügt for xml . In diesem Thema verwenden wir die GET_EMP_DETAILS_FOR_XML gespeicherten Prozedur, die die Mitarbeiterdetails für eine bestimmte Mitarbeiter-ID abruft. Um das Schema durch Ausführen der gespeicherten Prozedur abzurufen, 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 in 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.

Wichtig

Entfernen Sie die Klausel, nachdem Sie die xmlschema gespeicherte Prozedur ausgeführt haben, um das Schema zu generieren. Wenn Sie dies nicht tun, generieren Sie das Schema erneut in der Antwortnachricht, wenn Sie die gespeicherte Prozedur über BizTalk ausführen. Um die Antwortnachricht als XML abzurufen, müssen Sie die xmlschema -Klausel entfernen.

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

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

  2. Fügen Sie dem BizTalk-Projekt das Antwortschema hinzu, das Sie für die gespeicherte Prozedur generiert haben. 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 Root um.

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

    3. Ändern Sie in dem für die gespeicherten Prozedur generierten Schema den Wert von import schemaLocation wie folgt.

      import schemaLocation=”sqltypes.xsd”

      Sie tun dies, weil 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 <Knoten Schema> , und geben Sie im Eigenschaftenbereich einen Namespace in der Eigenschaft Zielnamespace an . Geben Sie für dieses Thema den Namespace als an http://ForXmlStoredProcs/namespace.

Generieren eines Schemas für die Anforderungsmeldung zum Aufrufen der gespeicherten Prozedur

Zum Generieren eines Schemas für die Anforderungsnachricht können Sie das Add-In Adapterdienst nutzen aus einem BizTalk-Projekt in Visual Studio verwenden. Generieren Sie für dieses Thema das Schema für die GET_EMP_DETAILS_FOR_XML gespeicherten Prozedur. Weitere Informationen zum Generieren des Schemas mithilfe des Add-Ins "Adapterdienst nutzen" finden Sie unter Abrufen von Metadaten für SQL Server Vorgänge in Visual Studio mithilfe des SQL-Adapters.

Wichtig

Sie müssen das Schema generieren, indem Sie die Prozedur nur über den Knoten Prozeduren unter Adapterdienst-Add-In nutzen auswählen.

Definieren von Nachrichten und Nachrichtentypen

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

  1. Fügen Sie dem BizTalk-Projekt eine Orchestrierung hinzu. Klicken Sie in Projektmappen-Explorer mit der rechten Maustaste auf den BizTalk-Projektnamen, 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 dieses nicht bereits geöffnet ist. Klicken Sie hierzu 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. Führen Sie im Bereich Eigenschaften für die Message_1 die folgenden Schritte aus:

    Option Aufgabe
    Bezeichner Geben Sie Request ein
    Nachrichtentyp Erweitern Sie in der Dropdownliste Schemas, und wählen Sie dann ForXMLProcedure.Procedure_dbo aus. GET_EMP_DETAILS_FOR_XML, wobei ForXMLProcedure der Name Ihres BizTalk-Projekts ist. Procedure_dbo ist das Schema, das zum Aufrufen der GET_EMP_DETAILS_FOR_XML-Prozedur generiert wird.

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

    Option Aufgabe
    Bezeichner 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. Bei dieser Orchestrierung legen Sie eine Anforderungsnachricht an einem definierten Empfangsspeicherort ab. Der SQL-Adapter verwendet diese Nachricht und übergibt sie an SQL Server. Die Antwort von SQL Server wird an einem anderen Speicherort gespeichert. Sie müssen Die Formen Senden und Empfangen einschließen, um Nachrichten an SQL Server bzw. Antworten zu senden. Eine Beispielorchestrierung zum Aufrufen einer Prozedur sieht wie folgt aus:

Orchestrierung zum Aufrufen gespeicherter Prozeduren

Hinzufügen von Nachrichten-Shapes

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

Form Shape-Typ Eigenschaften
ReceiveMessage Empfangen – Legen Sie Name auf ReceiveMessage fest.
– Legen Sie Activate auf True fest.
SendMessage Send – Name aufSendMessage festlegen
ReceiveResponse Empfangen – Name aufReceiveResponse festlegen
– Legen Sie Activate auf False fest.
SendResponse Send – Legen Sie Name auf SendResponse fest.

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, wie in der Orchestrierung angezeigt.

Port Eigenschaften
MessageIn – Bezeichner auf MessageIn festlegen
– Legen Sie Type auf MessageInType fest.
– Festlegen des Kommunikationsmusters auf Unidirektionale
– Festlegen der Kommunikationsrichtung auf "Empfangen"
LOBPort – Bezeichner auf LOBPort festlegen
– Legen Sie Type auf LOBPortType fest.
– Festlegen des Kommunikationsmusters auf Request-Response
– Festlegen der Kommunikationsrichtung auf Senden/Empfangen
ResponseOut – Bezeichner auf ResponseOut festlegen
– Legen Sie Type auf ResponseOutType fest.
– Festlegen des Kommunikationsmusters auf Unidirektionale
– Kommunikationsrichtung auf Senden festlegen

Geben Sie Nachrichten für Aktions-Shapes an, und verbinden Sie sie mit Ports.

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

Form Eigenschaften
ReceiveMessage – Nachricht aufAnforderung festlegen
– Legen Sie Operation auf MessageIn.FOR_XML.Request fest.
SendMessage – Nachricht aufAnforderung festlegen
– Festlegen von Operation auf LOBPort.FOR_XML.Request
ReceiveResponse – Nachricht aufAntwort festlegen
– Festlegen von Operation auf LOBPort.FOR_XML.Response
SendResponse – Nachricht aufAntwort festlegen
– Festlegen von Operation auf ResponseOut.FOR_XML.Request

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

Sie müssen nun die BizTalk-Lösung erstellen und in einem BizTalk Server bereitstellen. Weitere Informationen finden Sie unter Erstellen und Ausführen von Orchestrierungen.

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 Exemplarische Vorgehensweise: Bereitstellen einer einfachen BizTalk-Anwendung.

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 nutzt die Anforderungsnachricht und sendet sie an 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 SQL Server Datenbank abgibt.

    • Definieren Sie einen physischen WCF-Custom oder WCF-SQL-Sendeport, um Nachrichten an SQL Server Datenbank zu senden. Anweisungen zum Erstellen eines Sendeports finden Sie unter Manuelles Konfigurieren einer physischen Portbindung an den SQL-Adapter.

      Sie müssen die Aktion auch im Sendeport angeben. Bei 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 die GET_EMP_DETAILS_FOR_XML-Prozedur ausgeführt wird, lautet die Aktion also wie folgt:

      XmlProcedure/dbo/GET_EMP_DETAILS_FOR_XML

      Weitere Informationen zur Einstellungsaktion 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 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 eines Schemas für die Antwortnachricht für gespeicherte Prozedur beschrieben. Legen Sie für dieses Thema diesen Wert auf Stamm fest.
      XmlStoredProcedureRootNodeNamespace Geben Sie den Zielnamespace für das Antwortschema an, das Sie für die gespeicherte Prozedur generiert haben, wie unter Generieren eines Schemas für die Antwortnachricht für gespeicherte Prozedur beschrieben. Legen Sie für dieses Thema dies auf fest http://ForXmlStoredProcs/namespace.

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

      Hinweis

      Beim Generieren des Schemas mithilfe des BizTalk-Projekt-Add-Ins "Consume Adapter Service" 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 für die Verwendung des SQL-Adapters.

Starten der Anwendung

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

Stellen Sie in dieser Phase Folgendes sicher:

  • Der FILE-Empfangsport zum Empfangen von Anforderungsmeldungen für die Orchestrierung wird ausgeführt.

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

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

  • 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 Anforderungsnachricht am File-Empfangsspeicherort ablegen. Das Schema für die Anforderungsnachricht muss dem Anforderungsschema für die Prozedur entsprechen, die Sie mit dem Add-In "Adapterdienst nutzen" generiert haben. Die Anforderungsmeldung 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 Anforderungsnachrichtenschema zum Aufrufen von Prozeduren in SQL Server Datenbank mit dem SQL-Adapter finden Sie unter Nachrichtenschemas für Prozeduren und Funktionen.

Die Orchestrierung nutzt die Nachricht und sendet sie an SQL Server Datenbank. Die Antwort von SQL Server Datenbank wird am anderen FILE-Speicherort gespeichert, der als Teil der Orchestrierung definiert ist. Die Antwort von 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 Ausführen der gespeicherten Prozedur generiert wurde. Beachten Sie außerdem, dass der Stammknoten und der Namespace identisch sind, die 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.

Weitere Informationen

Entwickeln von BizTalk-Anwendungen mithilfe des SQL-Adapters