Ejecución de procedimientos almacenados con una cláusula FOR XML en SQL Server mediante BizTalk Server
Una instrucción SELECT de SQL puede tener una cláusula FOR XML que devuelva el resultado de la consulta como XML en lugar de un conjunto de filas. También puede tener un procedimiento almacenado que tenga una instrucción SELECT con una cláusula FOR XML. FOR XML (SQL Server) tiene más información.
Puede usar el adaptador sql basado en WCF para ejecutar estos procedimientos almacenados.
Importante
El adaptador sql "nativo" disponible con BizTalk Server requiere que los procedimientos almacenados tengan la cláusula FOR XML como parte de la instrucción SELECT. Puede usar estos procedimientos almacenados con el adaptador de SQL basado en WCF sin realizar ningún cambio en la definición del procedimiento almacenado.
Siempre puede usar los esquemas generados mediante el adaptador de SQL "nativo" proporcionado con versiones anteriores de BizTalk Server. Para obtener más información, vea Uso de consultas FOR XML con el adaptador WCF-SQL.
Cómo invocar procedimientos almacenados con la cláusula FOR XML
Cuando se invoca un procedimiento almacenado con la cláusula FOR XML en SQL Server Management Studio o mediante el adaptador de SQL disponible con BizTalk Server, la salida tiene el formato de un mensaje xml. Para usar estos procedimientos con el adaptador de SQL basado en WCF, debe tener el esquema para el mensaje de salida. El adaptador de SQL basado en WCF requiere este esquema mientras recibe el mensaje de respuesta de SQL Server después de ejecutar un procedimiento almacenado con la cláusula FOR XML. Tenga en cuenta que el propio adaptador generará el mensaje de solicitud para invocar este procedimiento almacenado.
Además de tener el esquema para el mensaje de respuesta, también debe realizar ciertas tareas para invocar un procedimiento almacenado con la cláusula FOR XML mediante el adaptador SQL basado en WCF.
Genere el esquema para el mensaje de respuesta para el procedimiento almacenado con la cláusula FOR XML. Si ya tiene el esquema de respuesta generado por el adaptador de SQL "nativo" disponible con BizTalk Server, puede omitir este paso.
Cree un proyecto de BizTalk y agregue el esquema generado al proyecto.
Genere el esquema para el procedimiento almacenado con la cláusula FOR XML mediante el adaptador sql basado en WCF. Esto proporciona el esquema para el mensaje de solicitud que el adaptador envía a SQL Server para invocar el procedimiento almacenado.
Cree mensajes en el proyecto de BizTalk para enviar y recibir mensajes de SQL Server. El mensaje de solicitud debe ajustarse al esquema del mensaje de solicitud generado por el adaptador. El mensaje de respuesta debe ajustarse al esquema del mensaje de respuesta obtenido mediante el adaptador sql "nativo" o ejecutando el procedimiento almacenado con la cláusula FOR XML en SQL Server Management Studio.
Cree una orquestación para invocar el procedimiento almacenado en la base de datos SQL Server.
Compile e implemente el proyecto de BizTalk.
Configure la aplicación de BizTalk mediante la creación de puertos físicos de envío y recepción.
Inicie la aplicación de BizTalk.
Generar esquema para el mensaje de respuesta para el procedimiento almacenado
Nota
No es necesario realizar este paso si tiene el esquema de respuesta generado por el adaptador de SQL disponible con BizTalk Server.
Puede generar el esquema para el mensaje de respuesta para el procedimiento almacenado, siempre que la instrucción SELECT del procedimiento almacenado tenga la xmlschema cláusula con la for xml cláusula . En este tema, usamos el procedimiento almacenado GET_EMP_DETAILS_FOR_XML que recupera los detalles del empleado para un identificador de empleado determinado. Para recuperar el esquema ejecutando el procedimiento almacenado, la instrucción SELECT tiene el siguiente aspecto:
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
Ejecute este procedimiento almacenado para obtener el esquema del mensaje de respuesta. Tenga en cuenta que la respuesta del procedimiento almacenado contiene el esquema, así como los datos de la ejecución del procedimiento almacenado. Debe copiar el esquema de la respuesta y guardarlo en un panel de texto. En este ejemplo, puede asignar un nombre a este esquema como ResponseSchema.xsd. Ahora debe crear un proyecto de BizTalk en Visual Studio y agregar este esquema al proyecto.
Importante
Asegúrese de quitar la xmlschema cláusula después de ejecutar el procedimiento almacenado para generar el esquema. Si no lo hace, cuando finalmente ejecute el procedimiento almacenado a través de BizTalk, volverá a generar el esquema en el mensaje de respuesta. Por lo tanto, para obtener el mensaje de respuesta como xml, debe quitar la xmlschema cláusula .
Para agregar el esquema a un proyecto de BizTalk
Cree un proyecto de BizTalk en Visual Studio.
Agregue el esquema de respuesta que generó para el procedimiento almacenado al proyecto de BizTalk. Haga clic con el botón derecho en el proyecto de BizTalk en el Explorador de soluciones, seleccione Agregar y, a continuación, haga clic en Elemento existente. En el cuadro de diálogo Agregar elemento existente, vaya a la ubicación donde guardó el esquema y haga clic en Agregar.
Abra el esquema en Visual Studio y realice los siguientes cambios.
Agregue un nodo al esquema y mueva el nodo raíz existente en este nodo recién agregado. Asigne un nombre al nodo raíz. En este tema, cambie el nombre del nodo raíz a Raíz.
El esquema de respuesta generado para el procedimiento almacenado hace referencia a sqltypes.xsd. Puede obtener el esquema sqltypes.xsd de https://go.microsoft.com/fwlink/?linkid=31850. Agregue el esquema sqltypes.xsd al proyecto de BizTalk. Para obtener más información sobre este esquema, vaya a:
En el esquema generado para el procedimiento almacenado, cambie el valor de
import schemaLocationa lo siguiente.import schemaLocation=”sqltypes.xsd”Esto se debe a que ya ha agregado el esquema sqltypes.xsd al proyecto de BizTalk.
Proporcione un espacio de nombres de destino para el esquema. Haga clic en el <nodo Esquema> y, en el panel de propiedades, especifique un espacio de nombres en la propiedad Espacio de nombres de destino . Para este tema, asigne al espacio de nombres como
http://ForXmlStoredProcs/namespace.
Generar esquema para el mensaje de solicitud para invocar el procedimiento almacenado
Para generar el esquema para el mensaje de solicitud, puede usar el complemento Consumir servicio de adaptador desde un proyecto de BizTalk en Visual Studio. Para este tema, genere el esquema para el procedimiento almacenado de GET_EMP_DETAILS_FOR_XML. Para obtener más información sobre cómo generar el esquema mediante el complemento consumir servicio adaptador, vea Recuperación de metadatos para SQL Server operaciones en Visual Studio mediante el adaptador de SQL.
Importante
Para generar el esquema, seleccione el procedimiento solo en el nodo Procedimientos del complemento Consumir servicio de adaptador.
Definición de mensajes y tipos de mensajes
El esquema que ha generado anteriormente describe los "tipos" necesarios para los mensajes de la orquestación. Normalmente, un mensaje es una variable, el tipo para el que se define mediante el esquema correspondiente. Ahora debe crear mensajes para la orquestación y vincularlos a esquemas que generó en el paso anterior.
Para crear mensajes y vincular al esquema
Agregue una orquestación al proyecto de BizTalk. En Explorador de soluciones, haga clic con el botón derecho en el nombre del proyecto de BizTalk, seleccione Agregar y, a continuación, haga clic en Nuevo elemento. Escriba un nombre para la orquestación de BizTalk y, a continuación, haga clic en Agregar.
Abra la ventana Vista orquestación del proyecto de BizTalk si no está abierta. Para ello, haga clic en Ver, seleccione Otras ventanas y, a continuación, haga clic en Vista de orquestación.
En la vista Orquestación, haga clic con el botón derecho en Mensajes y, a continuación, haga clic en Nuevo mensaje.
Haga clic con el botón derecho en el mensaje recién creado y, a continuación, seleccione Ventana Propiedades.
En el panel Propiedades del Message_1, haga lo siguiente:
Use Para hacer esto Identificador Escriba RequestTipo de mensaje En la lista desplegable, expanda Esquemas y, a continuación, seleccione ForXMLProcedure.Procedure_dbo. GET_EMP_DETAILS_FOR_XML, donde ForXMLProcedure es el nombre del proyecto de BizTalk. Procedure_dbo es el esquema generado para invocar el procedimiento GET_EMP_DETAILS_FOR_XML. Repita el paso 2 para crear un mensaje. En el panel Propiedades del nuevo mensaje, haga lo siguiente:
Use Para hacer esto Identificador Escriba ResponseTipo de mensaje En la lista desplegable, expanda Esquemas y, a continuación, seleccione ForXMLProcedure.ResponseSchema, donde ResponseSchema es el nombre del esquema de respuesta generado mediante la ejecución del procedimiento almacenado tal y como se describe en Generar esquema para el mensaje de respuesta para el procedimiento almacenado.
Configuración de la orquestación
Debe crear una orquestación de BizTalk para usar BizTalk Server para ejecutar un procedimiento almacenado en SQL Server. En esta orquestación, se quita un mensaje de solicitud en una ubicación de recepción definida. El adaptador de SQL consume este mensaje y lo pasa a SQL Server. La respuesta de SQL Server se guarda en otra ubicación. Debe incluir formas enviar y recibir para enviar mensajes a SQL Server y recibir respuestas, respectivamente. Una orquestación de ejemplo para invocar un procedimiento es similar a la siguiente:
Agregar formas de mensaje
Asegúrese de especificar las siguientes propiedades para cada una de las formas de mensaje. Los nombres enumerados en la columna Shape son los nombres de las formas de mensaje que se muestran en la orquestación mencionada.
| Forma | Tipo de forma | Propiedades |
|---|---|---|
| ReceiveMessage | Recepción | - Establecer el nombre en ReceiveMessage - Establecer Activar enTrue |
| SendMessage | Envío | - Establecer el nombre en SendMessage |
| ReceiveResponse | Recepción | - Establecer nombre en ReceiveResponse - Establecer Activar en False |
| SendResponse | Envío | - Establecer nombre en SendResponse |
Adición de puertos
Asegúrese de especificar las siguientes propiedades para cada uno de los puertos lógicos. Los nombres enumerados en la columna Puerto son los nombres de los puertos que se muestran en la orquestación.
| Port | Propiedades |
|---|---|
| MessageIn | - Establecer identificador en MessageIn - Establecer tipo en MessageInType - Establecer el patrón de comunicación en unidireccional - Establecer la dirección de comunicación para recibir |
| LOBPort | - Establecer identificador en LOBPort - Establecer tipo en LOBPortType - Establecer el patrón de comunicación en Solicitud-respuesta - Establecer la dirección de comunicación en enviar y recibir |
| ResponseOut | - Establecer identificador enResponseOut - Establecer tipo en ResponseOutType - Establecer el patrón de comunicación en unidireccional - Establecer la dirección de comunicación en Enviar |
Especificar mensajes para formas de acción y conectarlos a puertos
En la tabla siguiente se especifican las propiedades y sus valores que se deben establecer para especificar mensajes para las formas de acción y para vincular los mensajes a los puertos. Los nombres enumerados en la columna Shape son los nombres de las formas de mensaje tal como se muestran en la orquestación mencionada anteriormente.
| Forma | Propiedades |
|---|---|
| ReceiveMessage | - Establecer mensaje en solicitud - Establecer operaciónen MessageIn.FOR_XML.Request |
| SendMessage | - Establecer mensaje en solicitud - Establecer operaciónen LOBPort.FOR_XML.Request |
| ReceiveResponse | - Establecer mensaje en respuesta - Establecer operaciónen LOBPort.FOR_XML.Response |
| SendResponse | - Establecer mensaje en respuesta - Establecer operaciónen ResponseOut.FOR_XML.Request |
Después de especificar estas propiedades, las formas y los puertos de mensaje están conectados y se completa la orquestación.
Ahora debe compilar la solución de BizTalk e implementarla en un BizTalk Server. Para obtener más información, vea Compilar y ejecutar orquestaciones.
Configuración de la aplicación de BizTalk
Después de implementar el proyecto de BizTalk, la orquestación que creó anteriormente aparece en el panel Orquestaciones de la consola de administración de BizTalk Server. Debe usar la consola de administración de BizTalk Server para configurar la aplicación. Para obtener un tutorial, vea Tutorial: Implementación de una aplicación básica de BizTalk.
La configuración de una aplicación implica:
Selección de un host para la aplicación.
Asignación de los puertos que creó en la orquestación a puertos físicos en la consola de administración de BizTalk Server. Para esta orquestación, debe:
Defina una ubicación en el disco duro y un puerto de archivo correspondiente donde quitará un mensaje de solicitud. La orquestación de BizTalk consumirá el mensaje de solicitud y lo enviará a SQL Server base de datos.
Defina una ubicación en el disco duro y un puerto de archivo correspondiente donde la orquestación de BizTalk quitará el mensaje de respuesta que contiene la respuesta de SQL Server base de datos.
Defina un puerto de envío de WCF-Custom físico o WCF-SQL para enviar mensajes a SQL Server base de datos. Para obtener instrucciones sobre cómo crear un puerto de envío, consulte Configuración manual de un enlace de puerto físico al adaptador de SQL.
También debe especificar la acción en el puerto de envío. Para los procedimientos que contienen la cláusula FOR XML, debe establecer la acción en el formato siguiente.
XmlProcedure/<schema_name>/<procedure_name>Por lo tanto, para este tema en el que estamos ejecutando el procedimiento GET_EMP_DETAILS_FOR_XML, la acción será:
XmlProcedure/dbo/GET_EMP_DETAILS_FOR_XMLPara obtener más información sobre la acción de configuración, vea Configurar la acción SOAP para el adaptador de SQL .
También debe establecer las siguientes propiedades de enlace al ejecutar un procedimiento almacenado con la cláusula FOR XML.
Nombre de propiedad de enlace Establézcalo en XmlStoredProcedureRootNodeName Especifique el nombre del nodo raíz que agregó al esquema de respuesta que generó para el procedimiento almacenado, tal y como se describe en Generar esquema para el mensaje de respuesta para el procedimiento almacenado. Para este tema, establézcalo en Raíz. XmlStoredProcedureRootNodeNamespace Especifique el espacio de nombres de destino para el esquema de respuesta que generó para el procedimiento almacenado, tal y como se describe en Generar esquema para el mensaje de respuesta para el procedimiento almacenado. Para este tema, establézcalo en http://ForXmlStoredProcs/namespace.Para obtener más información sobre cómo especificar valores para las propiedades de enlace, consulte Configuración de las propiedades de enlace para el adaptador de SQL.
Nota
La generación del esquema mediante el complemento de proyecto consumir servicio de adaptador de BizTalk también crea un archivo de enlace que contiene información sobre los puertos y las acciones que se establecerán para esos puertos. Puede importar este archivo de enlace desde la consola de administración de BizTalk Server para crear puertos de envío (para llamadas salientes) o puertos de recepción (para llamadas entrantes). Para obtener más información, consulte Configuración de un enlace de puerto físico mediante un archivo de enlace de puertos para usar el adaptador de SQL.
Iniciar la aplicación
Debe iniciar la aplicación de BizTalk para invocar procedimientos en SQL Server base de datos. Para obtener instrucciones sobre cómo iniciar una aplicación de BizTalk, vea Cómo iniciar una orquestación.
En esta fase, asegúrese de:
El puerto de recepción FILE para recibir mensajes de solicitud para la orquestación se está ejecutando.
El puerto de envío FILE para recibir los mensajes de respuesta de la orquestación se está ejecutando.
El puerto de envío de WCF-Custom o WCF-SQL para enviar mensajes a SQL Server base de datos se está ejecutando.
La orquestación de BizTalk para la operación se está ejecutando.
Ejecución de la operación
Después de ejecutar la aplicación, debe quitar un mensaje de solicitud en la ubicación de recepción del archivo. El esquema del mensaje de solicitud debe ajustarse al esquema de solicitud para el procedimiento que generó mediante el complemento consumir servicio adaptador. Por ejemplo, el mensaje de solicitud para invocar el GET_EMP_DETAILS_FOR XML es:
<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>
Consulte Esquemas de mensajes para procedimientos y funciones para obtener más información sobre el esquema de mensajes de solicitud para invocar procedimientos en SQL Server base de datos mediante el adaptador de SQL.
La orquestación consume el mensaje y lo envía a SQL Server base de datos. La respuesta de SQL Server base de datos se guarda en la otra ubicación FILE definida como parte de la orquestación. Por ejemplo, la respuesta de SQL Server base de datos para el mensaje de solicitud anterior es:
<?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>
Observe que la respuesta se recibe en el mismo esquema que generó al ejecutar el procedimiento almacenado. Tenga en cuenta también que el nodo raíz y el espacio de nombres son los mismos que especificó como valores para las propiedades de enlace XmlStoredProcedureRootNodeName yXmlStoredProcedureRootNodeNamespace respectivamente.
Prácticas recomendadas
Después de haber implementado y configurado el proyecto de BizTalk, puede exportar las opciones de configuración a un archivo XML denominado archivo de enlace. Una vez que genere un archivo de enlace, puede importar los valores de configuración del archivo, de modo que no necesite crear elementos como puertos de envío y puertos de recepción para la misma orquestación. Para obtener más información sobre los archivos de enlace, consulte Reutilización de enlaces de adaptador.