Exécuter des procédures stockées ayant une clause FOR XML dans SQL Server à l’aide de BizTalk Server

Une instruction SQL SELECT peut avoir une clause FOR XML qui retourne le résultat de la requête au format XML au lieu d’un ensemble de lignes. Vous pouvez également avoir une procédure stockée qui a une instruction SELECT avec une clause FOR XML. FOR XML (SQL Server) contient plus d’informations.

Vous pouvez utiliser l’adaptateur SQL basé sur WCF pour exécuter ces procédures stockées.

Comment appeler des procédures stockées avec une clause FOR XML

Lorsque vous appelez une procédure stockée avec une clause FOR XML dans SQL Server Management Studio ou à l’aide de l’adaptateur SQL disponible avec BizTalk Server, la sortie se présente sous la forme d’un message xml. Pour utiliser ces procédures avec l’adaptateur SQL basé sur WCF, vous devez disposer du schéma du message de sortie. L’adaptateur SQL wcf nécessite ce schéma lors de la réception du message de réponse de SQL Server après l’exécution d’une procédure stockée avec une clause FOR XML. Notez que le message de demande d’appel de cette procédure stockée sera généré par l’adaptateur lui-même.

Outre le schéma du message de réponse, vous devez également effectuer certaines tâches pour appeler une procédure stockée avec une clause FOR XML à l’aide de l’adaptateur SQL basé sur WCF.

1. Générez le schéma du message de réponse pour la procédure stockée avec la clause FOR XML. Si vous disposez déjà du schéma de réponse généré par l’adaptateur SQL « natif » disponible avec BizTalk Server, vous pouvez ignorer cette étape.

2. Créez un projet BizTalk et ajoutez le schéma généré au projet.

3. Générez le schéma pour la procédure stockée avec la clause FOR XML à l’aide de l’adaptateur SQL basé sur WCF. Cela fournit le schéma du message de demande que l’adaptateur envoie à SQL Server pour appeler la procédure stockée.

4. Créez des messages dans le projet BizTalk pour envoyer et recevoir des messages de SQL Server. Le message de demande doit être conforme au schéma du message de demande généré par l’adaptateur. Le message de réponse doit être conforme au schéma du message de réponse obtenu à l’aide de l’adaptateur SQL « natif » ou en exécutant la procédure stockée avec la clause FOR XML dans SQL Server Management Studio.

5. Créez une orchestration pour appeler la procédure stockée dans la base de données SQL Server.

6. Générez et déployez le projet BizTalk.

7. Configurez l’application BizTalk en créant des ports d’envoi et de réception physiques.

8. Démarrez l’application BizTalk.

Génération du schéma pour le message de réponse pour la procédure stockée

Vous pouvez générer le schéma du message de réponse pour la procédure stockée, à condition que l’instruction SELECT de la procédure stockée ait la xmlschema clause avec la for xml clause . Dans cette rubrique, nous utilisons la procédure stockée GET_EMP_DETAILS_FOR_XML qui récupère les détails de l’employé pour un ID d’employé donné. Pour récupérer le schéma en exécutant la procédure stockée, l’instruction SELECT se présente comme suit :

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

Exécutez cette procédure stockée pour obtenir le schéma du message de réponse. Notez que la réponse de la procédure stockée contient le schéma ainsi que les données de l’exécution de la procédure stockée. Vous devez copier le schéma à partir de la réponse et l’enregistrer dans un pavé de texte. Pour cet exemple, vous pouvez nommer ce schéma ResponseSchema.xsd. Vous devez maintenant créer un projet BizTalk dans Visual Studio et ajouter ce schéma au projet.

Pour ajouter le schéma à un projet BizTalk

1. Créez un projet BizTalk dans Visual Studio.

2. Ajoutez le schéma de réponse que vous avez généré pour la procédure stockée au projet BizTalk. Cliquez avec le bouton droit sur le projet BizTalk dans le Explorateur de solutions, pointez sur Ajouter, puis cliquez sur Élément existant. Dans la boîte de dialogue Ajouter un élément existant, accédez à l’emplacement où vous avez enregistré le schéma, puis cliquez sur Ajouter.

3. Ouvrez le schéma dans Visual Studio et apportez les modifications suivantes.

   1. Ajoutez un nœud au schéma et déplacez le nœud racine existant sous ce nœud nouvellement ajouté. Donnez un nom au nœud racine. Pour cette rubrique, renommez le nœud racine en Racine.

   2. Le schéma de réponse généré pour la procédure stockée fait référence à un sqltypes.xsd. Vous pouvez obtenir le schéma sqltypes.xsd à partir de https://go.microsoft.com/fwlink/?linkid=31850. Ajoutez le schéma sqltypes.xsd au projet BizTalk. Pour plus d’informations sur ce schéma, accédez à :

      • 2.2.5.2 sqlTypes
      • Référencez la collection de schémas XML intégrée (sys)

   3. Dans le schéma généré pour la procédure stockée, remplacez la valeur de import schemaLocation par ce qui suit.

      import schemaLocation=”sqltypes.xsd”
      

      Vous le faites, car vous avez déjà ajouté le schéma sqltypes.xsd à votre projet BizTalk.

   4. Fournissez un espace de noms cible pour le schéma. Cliquez sur le <nœud Schéma> et, dans le volet propriétés, spécifiez un espace de noms dans la propriété Espace de noms cible . Pour cette rubrique, attribuez à l’espace de noms .http://ForXmlStoredProcs/namespace

Génération du schéma pour le message de demande d’appel de la procédure stockée

Pour générer le schéma pour le message de demande, vous pouvez utiliser le complément Consume Adapter Service à partir d’un projet BizTalk dans Visual Studio. Pour cette rubrique, générez le schéma de la procédure stockée GET_EMP_DETAILS_FOR_XML. Pour plus d’informations sur la façon de générer le schéma à l’aide du complément Consume Adapter Service, consultez Récupération des métadonnées pour les opérations SQL Server dans Visual Studio à l’aide de l’adaptateur SQL.

Définition des messages et des types de messages

Le schéma que vous avez généré précédemment décrit les « types » requis pour les messages dans l’orchestration. Un message est généralement une variable, dont le type est défini par le schéma correspondant. Vous devez maintenant créer des messages pour l’orchestration et les lier à des schémas que vous avez générés à l’étape précédente.

Pour créer des messages et créer un lien vers le schéma

1. Ajoutez une orchestration au projet BizTalk. À partir de Explorateur de solutions, cliquez avec le bouton droit sur le nom du projet BizTalk, pointez sur Ajouter, puis cliquez sur Nouvel élément. Tapez un nom pour l’orchestration BizTalk, puis cliquez sur Ajouter.

2. Si la fenêtre Vue Orchestration du projet BizTalk n’est pas déjà ouverte, ouvrez-la. Pour ce faire, cliquez sur Affichage, pointez sur Autres fenêtres, puis cliquez sur Vue d’orchestration.

3. En mode Orchestration, cliquez avec le bouton droit sur Messages, puis cliquez sur Nouveau message.

4. Cliquez avec le bouton droit sur le message nouvellement créé, puis sélectionnez Fenêtre Propriétés.

5. Dans le volet Propriétés du Message_1, procédez comme suit :

   Propriété	Pour
   Identificateur	Saisissez Request
   Type de message	Dans la liste déroulante, développez Schémas, puis sélectionnez ForXMLProcedure.Procedure_dbo. GET_EMP_DETAILS_FOR_XML, où ForXMLProcedure est le nom de votre projet BizTalk. Procedure_dbo est le schéma généré pour l’appel de la procédure GET_EMP_DETAILS_FOR_XML.

6. Répétez l’étape 2 pour créer un nouveau message. Dans le volet Propriétés du nouveau message, procédez comme suit :

   Propriété	Pour
   Identificateur	Saisissez Response
   Type de message	Dans la liste déroulante, développez Schémas, puis sélectionnez ForXMLProcedure.ResponseSchema, où ResponseSchema est le nom du schéma de réponse généré par l’exécution de la procédure stockée, comme décrit sous Génération de schéma pour le message de réponse pour la procédure stockée.

Configuration de l’orchestration

Vous devez créer une orchestration BizTalk pour utiliser BizTalk Server pour exécuter une procédure stockée dans SQL Server. Dans cette orchestration, vous supprimez un message de demande à un emplacement de réception défini. L’adaptateur SQL consomme ce message et le transmet à SQL Server. La réponse de SQL Server est enregistrée à un autre emplacement. Vous devez inclure des formes Envoyer et Recevoir pour envoyer des messages à SQL Server et recevoir des réponses, respectivement. Un exemple d’orchestration pour appeler une procédure ressemble à ce qui suit :

Ajout de formes de message

Veillez à spécifier les propriétés suivantes pour chacune des formes de message. Les noms répertoriés dans la colonne Forme sont les noms des formes de message affichées dans l’orchestration qui vient d’être mentionnée.

Ajout de ports

Veillez à spécifier les propriétés suivantes pour chacun des ports logiques. Les noms répertoriés dans la colonne Port sont les noms des ports affichés dans l’orchestration.

Spécifier des messages pour les formes d’action et les connecter aux ports

Le tableau suivant spécifie les propriétés et leurs valeurs que vous devez définir pour spécifier des messages pour les formes d’action et lier les messages aux ports. Les noms répertoriés dans la colonne Forme sont les noms des formes de message affichées dans l’orchestration mentionnée précédemment.

Une fois que vous avez spécifié ces propriétés, les formes et les ports de message sont connectés et votre orchestration est terminée.

Vous devez maintenant générer la solution BizTalk et la déployer sur un BizTalk Server. Pour plus d’informations, consultez Génération et exécution d’orchestrations.

Configuration de l’application BizTalk

Une fois que vous avez déployé le projet BizTalk, l’orchestration que vous avez créée précédemment est répertoriée sous le volet Orchestrations de la console d’administration BizTalk Server. Vous devez utiliser la console d’administration BizTalk Server pour configurer l’application. Pour obtenir une procédure pas à pas, consultez Procédure pas à pas : déploiement d’une application BizTalk de base.

La configuration d’une application implique :

• Sélection d’un hôte pour l’application.

• Mappage des ports que vous avez créés dans votre orchestration à des ports physiques dans la console d’administration BizTalk Server. Pour cette orchestration, vous devez :

  • Définissez un emplacement sur le disque dur et un port de fichier correspondant où vous supprimerez un message de demande. L’orchestration BizTalk consomme le message de requête et l’envoie à SQL Server base de données.

  • Définissez un emplacement sur le disque dur et un port de fichier correspondant où l’orchestration BizTalk supprimera le message de réponse contenant la réponse de SQL Server base de données.

  • Définissez un port d’envoi WCF-Custom physique ou WCF-SQL pour envoyer des messages à SQL Server base de données. Pour obtenir des instructions sur la création d’un port d’envoi, consultez Configurer manuellement une liaison de port physique à l’adaptateur SQL.

    Vous devez également spécifier l’action dans le port d’envoi. Pour les procédures qui contiennent la clause FOR XML, vous devez définir l’action au format suivant.

    XmlProcedure/<schema_name>/<procedure_name>
    

    Par conséquent, pour cette rubrique où nous allons exécuter la procédure GET_EMP_DETAILS_FOR_XML, l’action sera :

    XmlProcedure/dbo/GET_EMP_DETAILS_FOR_XML
    

    Pour plus d’informations sur la définition de l’action, consultez Configurer l’action SOAP pour l’adaptateur SQL .

    Vous devez également définir les propriétés de liaison suivantes lors de l’exécution d’une procédure stockée avec la clause FOR XML.

    Nom de la propriété de liaison	Définissez ceci sur
    XmlStoredProcedureRootNodeName	Spécifiez le nom du nœud racine que vous avez ajouté au schéma de réponse que vous avez généré pour la procédure stockée, comme décrit sous Génération du schéma pour le message de réponse pour la procédure stockée. Pour cette rubrique, définissez cette valeur sur Racine.
    XmlStoredProcedureRootNodeNamespace	Spécifiez l’espace de noms cible pour le schéma de réponse que vous avez généré pour la procédure stockée, comme décrit sous Génération du schéma pour le message de réponse pour la procédure stockée. Pour cette rubrique, définissez cette valeur sur http://ForXmlStoredProcs/namespace.

    Pour plus d’informations sur la spécification de valeurs pour les propriétés de liaison, consultez Configurer les propriétés de liaison pour l’adaptateur SQL.

    Notes

    La génération du schéma à l’aide du complément de projet BizTalk du service d’adaptateur de consommation crée également un fichier de liaison qui contient des informations sur les ports et les actions à définir pour ces ports. Vous pouvez importer ce fichier de liaison à partir de la console d’administration BizTalk Server pour créer des ports d’envoi (pour les appels sortants) ou des ports de réception (pour les appels entrants). Pour plus d’informations, consultez Configurer une liaison de port physique à l’aide d’un fichier de liaison de port pour utiliser l’adaptateur SQL.

Démarrage de l’application

Vous devez démarrer l’application BizTalk pour appeler des procédures dans SQL Server base de données. Pour obtenir des instructions sur le démarrage d’une application BizTalk, consultez Guide pratique pour démarrer une orchestration.

À ce stade, assurez-vous que :

• Le port de réception FILE pour recevoir les messages de demande pour l’orchestration est en cours d’exécution.

• Le port d’envoi FILE pour recevoir les messages de réponse de l’orchestration est en cours d’exécution.

• Le port d’envoi WCF-Custom ou WCF-SQL pour envoyer des messages à SQL Server base de données est en cours d’exécution.

• L’orchestration BizTalk pour l’opération est en cours d’exécution.

Exécution de l’opération

Après avoir exécuté l’application, vous devez supprimer un message de demande à l’emplacement de réception FILE. Le schéma du message de demande doit être conforme au schéma de demande pour la procédure que vous avez générée à l’aide du complément Consume Adapter Service. Par exemple, le message de demande d’appeler le GET_EMP_DETAILS_FOR XML est :

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

Consultez Schémas de message pour les procédures et les fonctions pour plus d’informations sur le schéma de message de demande pour appeler des procédures dans SQL Server base de données à l’aide de l’adaptateur SQL.

L’orchestration consomme le message et l’envoie à SQL Server base de données. La réponse de SQL Server base de données est enregistrée à l’autre emplacement FILE défini dans le cadre de l’orchestration. Par exemple, la réponse de SQL Server base de données pour le message de demande précédent est la suivante :

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

Notez que la réponse est reçue dans le même schéma que celui généré en exécutant la procédure stockée. Notez également que le nœud racine et l’espace de noms sont les mêmes que vous avez spécifiés en tant que valeurs pour les propriétés de liaison XmlStoredProcedureRootNodeName et XmlStoredProcedureRootNodeNamespace respectivement.

Bonnes pratiques

Une fois que vous avez déployé et configuré le projet BizTalk, vous pouvez exporter les paramètres de configuration vers un fichier XML appelé fichier de liaison. Une fois que vous avez généré un fichier de liaison, vous pouvez importer les paramètres de configuration à partir du fichier, de sorte que vous n’avez pas besoin de créer des éléments tels que des ports d’envoi et des ports de réception pour la même orchestration. Pour plus d’informations sur les fichiers de liaison, consultez Réutiliser les liaisons d’adaptateur.

Voir aussi

Développer des applications BizTalk avec l’adaptateur SQL