Eseguire stored procedure con una clausola FOR XML in SQL Server tramite BizTalk Server
Un'istruzione SQL SELECT può avere una clausola FOR XML che restituisce il risultato della query come XML anziché un set di righe. È anche possibile disporre di una stored procedure con un'istruzione SELECT con una clausola FOR XML. FOR XML (SQL Server) contiene altre informazioni.
È possibile usare l'adapter SQL basato su WCF per eseguire tali stored procedure.
Importante
L'adattatore SQL "nativo" disponibile con BizTalk Server richiede che le stored procedure abbiano la clausola FOR XML come parte dell'istruzione SELECT. È possibile utilizzare tali stored procedure con l'adattatore SQL basato su WCF senza apportare modifiche alla definizione della stored procedure.
È sempre possibile usare gli schemi generati usando l'adattatore SQL "nativo" fornito con le versioni precedenti di BizTalk Server. Per altre informazioni, vedere Uso di query FOR XML con l'adapter WCF-SQL.
Come richiamare stored procedure con la clausola FOR XML
Quando si richiama una stored procedure con la clausola FOR XML in SQL Server Management Studio o si usa l'adattatore SQL disponibile con BizTalk Server, l'output è sotto forma di messaggio XML. Per utilizzare queste procedure con l'adapter SQL basato su WCF, è necessario disporre dello schema per il messaggio di output. L'adattatore SQL basato su WCF richiede questo schema durante la ricezione del messaggio di risposta da SQL Server dopo l'esecuzione di una stored procedure con la clausola FOR XML. Si noti che il messaggio di richiesta per richiamare questa stored procedure verrà generato dall'adapter stesso.
Oltre ad avere lo schema per il messaggio di risposta, è anche necessario eseguire determinate attività per richiamare una stored procedure con la clausola FOR XML usando l'adapter SQL basato su WCF.
Generare lo schema per il messaggio di risposta per la stored procedure con la clausola FOR XML. Se lo schema di risposta è già stato generato dall'adattatore SQL "nativo" disponibile con BizTalk Server, è possibile ignorare questo passaggio.
Creare un progetto BizTalk e aggiungere lo schema generato al progetto.
Generare lo schema per la stored procedure con la clausola FOR XML usando l'adapter SQL basato su WCF. In questo modo viene fornito lo schema per il messaggio di richiesta inviato dall'adapter a SQL Server per richiamare la stored procedure.
Creare messaggi nel progetto BizTalk per inviare e ricevere messaggi da SQL Server. Il messaggio di richiesta deve essere conforme allo schema del messaggio di richiesta generato dall'adapter. Il messaggio di risposta deve essere conforme allo schema del messaggio di risposta ottenuto usando l'adattatore SQL "nativo" o eseguendo la stored procedure con la clausola FOR XML in SQL Server Management Studio.
Creare un'orchestrazione per richiamare la stored procedure nel database SQL Server.
Compilare e distribuire il progetto BizTalk.
Configurare l'applicazione BizTalk creando porte di invio e ricezione fisiche.
Avviare l'applicazione BizTalk.
Generazione dello schema per il messaggio di risposta per la stored procedure
Nota
Non è necessario eseguire questo passaggio se lo schema di risposta generato dall'adapter SQL è disponibile con BizTalk Server.
È possibile generare lo schema per il messaggio di risposta per la stored procedure, a condizione che l'istruzione SELECT nella stored procedure disponga della xmlschema clausola con la for xml clausola . In questo argomento viene usata la stored procedure GET_EMP_DETAILS_FOR_XML che recupera i dettagli del dipendente per un DETERMINATO ID dipendente. Per recuperare lo schema eseguendo la stored procedure, l'istruzione SELECT è simile alla seguente:
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
Eseguire questa stored procedure per ottenere lo schema per il messaggio di risposta. Si noti che la risposta della stored procedure contiene lo schema e i dati dall'esecuzione della stored procedure. È necessario copiare lo schema dalla risposta e salvarlo in un riquadro di testo. Per questo esempio, è possibile denominare questo schema come ResponseSchema.xsd. È ora necessario creare un progetto BizTalk in Visual Studio e aggiungere questo schema al progetto.
Importante
Assicurarsi di rimuovere la xmlschema clausola dopo aver eseguito la stored procedure per generare lo schema. Se non si esegue questa operazione, quando si esegue finalmente la stored procedure tramite BizTalk, verrà generato nuovamente lo schema nel messaggio di risposta. Per ottenere il messaggio di risposta come xml, è quindi necessario rimuovere la xmlschema clausola .
Per aggiungere lo schema a un progetto BizTalk
Creare un progetto BizTalk in Visual Studio.
Aggiungere lo schema di risposta generato per la stored procedure al progetto BizTalk. Fare clic con il pulsante destro del mouse sul progetto BizTalk nel Esplora soluzioni, scegliere Aggiungi e quindi fare clic su Elemento esistente. Nella finestra di dialogo Aggiungi elemento esistente passare alla posizione in cui è stato salvato lo schema e fare clic su Aggiungi.
Aprire lo schema in Visual Studio e apportare le modifiche seguenti.
Aggiungere un nodo allo schema e spostare il nodo radice esistente in questo nodo appena aggiunto. Assegnare un nome al nodo radice. Per questo argomento, rinominare il nodo radice in Root.
Lo schema di risposta generato per la stored procedure fa riferimento a sqltypes.xsd. È possibile ottenere lo schema sqltypes.xsd da https://go.microsoft.com/fwlink/?linkid=31850. Aggiungere lo schema sqltypes.xsd al progetto BizTalk. Per altre informazioni su questo schema, vedere:
Nello schema generato per la stored procedure modificare il valore di
import schemaLocationnel modo seguente.import schemaLocation=”sqltypes.xsd”A tale scopo, è già stato aggiunto lo schema sqltypes.xsd al progetto BizTalk.
Specificare uno spazio dei nomi di destinazione per lo schema. Fare clic sul <nodo Schema> e nel riquadro delle proprietà specificare uno spazio dei nomi nella proprietà Spazio dei nomi di destinazione. Per questo argomento assegnare lo spazio dei nomi come
http://ForXmlStoredProcs/namespace.
Generazione dello schema per il messaggio di richiesta per richiamare la stored procedure
Per generare lo schema per il messaggio di richiesta, è possibile usare il componente aggiuntivo Consume Adapter Service da un progetto BizTalk in Visual Studio. Per questo argomento, generare lo schema per la stored procedure GET_EMP_DETAILS_FOR_XML. Per altre informazioni su come generare lo schema usando il componente aggiuntivo Consume Adapter Service, vedere Recupero di metadati per SQL Server operazioni in Visual Studio tramite l'adapter SQL.
Importante
È necessario generare lo schema selezionando la procedura solo dal nodo Procedure in Consume Adapter Service Add-in .
Definizione di messaggi e tipi di messaggio
Lo schema generato in precedenza descrive i "tipi" necessari per i messaggi nell'orchestrazione. Un messaggio è in genere una variabile, il tipo per il quale è definito dallo schema corrispondente. È ora necessario creare messaggi per l'orchestrazione e collegarli agli schemi generati nel passaggio precedente.
Per creare messaggi e collegarsi allo schema
Aggiungere un'orchestrazione al progetto BizTalk. Da Esplora soluzioni fare clic con il pulsante destro del mouse sul nome del progetto BizTalk, scegliere Aggiungi e quindi fare clic su Nuovo elemento. Digitare un nome per l'orchestrazione BizTalk e quindi fare clic su Aggiungi.
Aprire la finestra Visualizzazione orchestrazione del progetto BizTalk, se non è già aperta. A tale scopo, fare clic su Visualizza, scegliere Altre finestre e quindi fare clic su Visualizzazione orchestrazione.
In Visualizzazione orchestrazione fare clic con il pulsante destro del mouse su Messaggi e quindi scegliere Nuovo messaggio.
Fare clic con il pulsante destro del mouse sul messaggio appena creato e quindi scegliere Finestra Proprietà.
Nel riquadro Proprietà per il Message_1 eseguire le operazioni seguenti:
Usare Per Identificatore Digitare RequestTipo di messaggio Nell'elenco a discesa espandere Schemi e quindi selezionare ForXMLProcedure.Procedure_dbo. GET_EMP_DETAILS_FOR_XML, dove ForXMLProcedure è il nome del progetto BizTalk. Procedure_dbo è lo schema generato per richiamare la procedura di GET_EMP_DETAILS_FOR_XML. Ripetere il passaggio 2 per creare un nuovo messaggio. Nel riquadro Proprietà per il nuovo messaggio eseguire le operazioni seguenti:
Usare Per Identificatore Digitare ResponseTipo di messaggio Nell'elenco a discesa espandere Schemi e quindi selezionare ForXMLProcedure.ResponseSchema, dove ResponseSchema è il nome dello schema di risposta generato eseguendo la stored procedure, come descritto in Generazione dello schema per il messaggio di risposta per la stored procedure.
Configurazione dell'orchestrazione
È necessario creare un'orchestrazione BizTalk per usare BizTalk Server per l'esecuzione di una stored procedure in SQL Server. In questa orchestrazione si rilascia un messaggio di richiesta in una posizione di ricezione definita. L'adattatore SQL usa questo messaggio e lo passa a SQL Server. La risposta da SQL Server viene salvata in un'altra posizione. È necessario includere rispettivamente le forme Invia e Ricezione per inviare messaggi a SQL Server e ricevere risposte. Un'orchestrazione di esempio per richiamare una procedura è simile alla seguente:
Aggiunta di forme di messaggio
Assicurarsi di specificare le proprietà seguenti per ognuna delle forme del messaggio. I nomi elencati nella colonna Shape sono i nomi delle forme del messaggio visualizzate nell'orchestrazione appena menzionata.
| Forma | Tipo di forma | Proprietà |
|---|---|---|
| ReceiveMessage | Ricevere | - Impostare nome su ReceiveMessage - Impostare Attiva su True |
| SendMessage | Send | - Impostare nome su SendMessage |
| ReceiveResponse | Ricevere | - Impostare nome su ReceiveResponse - Impostare Attiva su False |
| SendResponse | Send | - Impostare nome su SendResponse |
Aggiunta di porte
Assicurarsi di specificare le proprietà seguenti per ognuna delle porte logiche. I nomi elencati nella colonna Porta sono i nomi delle porte visualizzate nell'orchestrazione.
| Porta | Proprietà |
|---|---|
| MessageIn | - Impostare identificatore su MessageIn - Impostare tipo su MessageInType - Impostare il modello di comunicazionesu unidirezionale - Impostare la direzione di comunicazione su Ricezione |
| LOBPort | - Impostare identificatore su LOBPort - Impostare tipo su LOBPortType - Impostare il modello di comunicazione su Request-Response - Impostare la direzione di comunicazione su Send-Receive |
| ResponseOut | - Impostare identificatore su ResponseOut - Impostare tipo su ResponseOutType - Impostare il modello di comunicazionesu unidirezionale - Impostare la direzione di comunicazione su Invia |
Specificare i messaggi per le forme di azione e connetterli alle porte
La tabella seguente specifica le proprietà e i relativi valori che è necessario impostare per specificare i messaggi per le forme di azione e collegare i messaggi alle porte. I nomi elencati nella colonna Shape sono i nomi delle forme del messaggio visualizzate nella orchestrazione menzionata in precedenza.
| Forma | Proprietà |
|---|---|
| ReceiveMessage | - Impostare messaggio su richiesta - Impostare l'operazione su MessageIn.FOR_XML.Request |
| SendMessage | - Impostare messaggio su richiesta - Impostare l'operazione su LOBPort.FOR_XML.Request |
| ReceiveResponse | - Impostare messaggio su risposta - Impostare l'operazione su LOBPort.FOR_XML.Response |
| SendResponse | - Impostare messaggio su risposta - Impostare l'operazione su ResponseOut.FOR_XML.Request |
Dopo aver specificato queste proprietà, le forme e le porte del messaggio sono connesse e la orchestrazione è stata completata.
È ora necessario compilare la soluzione BizTalk e distribuirla in un BizTalk Server. Per altre informazioni, vedere Creazione e esecuzione di orchestrazioni.
Configurazione dell'applicazione BizTalk
Dopo aver distribuito il progetto BizTalk, l'orchestrazione creata in precedenza è elencata nel riquadro Orchestrazioni nella console di amministrazione di BizTalk Server. È necessario usare la console di amministrazione BizTalk Server per configurare l'applicazione. Per una procedura dettagliata, vedere Procedura dettagliata: Distribuzione di un'applicazione BizTalk di base.
La configurazione di un'applicazione comporta:
Selezione di un host per l'applicazione.
Mapping delle porte create nell'orchestrazione alle porte fisiche nella console di amministrazione di BizTalk Server. Per questa orchestrazione è necessario:
Definire un percorso sul disco rigido e una porta file corrispondente in cui verrà visualizzato un messaggio di richiesta. L'orchestrazione BizTalk utilizzerà il messaggio di richiesta e lo invierà al database SQL Server.
Definire un percorso sul disco rigido e una porta file corrispondente in cui l'orchestrazione BizTalk rilascia il messaggio di risposta contenente la risposta dal database SQL Server.
Definire una porta di trasmissione fisica WCF-Custom o WCF-SQL per inviare messaggi al database SQL Server. Per istruzioni su come creare una porta di invio, vedere Configurare manualmente un'associazione di porte fisiche all'adapter SQL.
È anche necessario specificare l'azione nella porta di invio. Per le procedure che contengono la clausola FOR XML, è necessario impostare l'azione nel formato seguente.
XmlProcedure/<schema_name>/<procedure_name>Quindi, per questo argomento in cui si esegue la procedura di GET_EMP_DETAILS_FOR_XML, l'azione sarà:
XmlProcedure/dbo/GET_EMP_DETAILS_FOR_XMLPer altre informazioni sull'azione di impostazione, vedere Configurare l'azione SOAP per l'adapter SQL .
È anche necessario impostare le proprietà di associazione seguenti durante l'esecuzione di una stored procedure con la clausola FOR XML.
Nome della proprietà binding Impostare il valore su XmlStoredProcedureRootNodeName Specificare il nome del nodo radice aggiunto allo schema di risposta generato per la stored procedure, come descritto in Generazione dello schema per il messaggio di risposta per stored procedure. Per questo argomento impostare questa opzione su Radice. XmlStoredProcedureRootNodeNamespace Specificare lo spazio dei nomi di destinazione per lo schema di risposta generato per la stored procedure, come descritto in Generazione dello schema per il messaggio di risposta per stored procedure. Per questo argomento, impostare questa opzione su http://ForXmlStoredProcs/namespace.Per altre informazioni sulla specifica dei valori per le proprietà di associazione, vedere Configurare le proprietà di associazione per l'adapter SQL.
Nota
La generazione dello schema usando il componente aggiuntivo Del progetto BizTalk del servizio adapter crea anche un file di associazione contenente informazioni sulle porte e sulle azioni da impostare per tali porte. È possibile importare questo file di associazione dalla console di amministrazione di BizTalk Server per creare porte di invio (per le chiamate in uscita) o le porte di ricezione (per le chiamate in ingresso). Per altre informazioni, vedere Configurare un'associazione di porte fisiche usando un file di associazione di porte per l'uso dell'adapter SQL.
Avvio dell'applicazione
È necessario avviare l'applicazione BizTalk per richiamare le procedure nel database di SQL Server. Per istruzioni sull'avvio di un'applicazione BizTalk, vedere Come avviare un'orchestrazione.
In questa fase, assicurarsi di:
La porta di ricezione FILE per ricevere messaggi di richiesta per l'orchestrazione è in esecuzione.
La porta di trasmissione FILE per ricevere i messaggi di risposta dall'orchestrazione è in esecuzione.
La porta di trasmissione WCF-Custom o WCF-SQL per inviare messaggi a SQL Server database è in esecuzione.
L'orchestrazione BizTalk per l'operazione è in esecuzione.
Esecuzione dell'operazione
Dopo aver eseguito l'applicazione, è necessario eliminare un messaggio di richiesta nel percorso di ricezione FILE. Lo schema per il messaggio di richiesta deve essere conforme allo schema di richiesta per la procedura generata tramite il componente aggiuntivo Del servizio adapter di utilizzo. Ad esempio, il messaggio di richiesta per richiamare il codice XML GET_EMP_DETAILS_FOR è:
<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>
Per altre informazioni sullo schema dei messaggi di richiesta per richiamare le procedure nel database SQL Server tramite l'adapter SQL, vedere Message Schemas for Procedures and Functions .See Message Schemas for Procedures and Functions for more information about the request message schema for invoking procedures in SQL Server database using the SQL adapter.
L'orchestrazione utilizza il messaggio e lo invia a SQL Server database. La risposta da SQL Server database viene salvata nell'altro percorso FILE definito come parte dell'orchestrazione. Ad esempio, la risposta da SQL Server database per il messaggio di richiesta precedente è:
<?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>
Si noti che la risposta viene ricevuta nello stesso schema generato eseguendo la stored procedure. Si noti anche che il nodo radice e lo spazio dei nomi sono gli stessi specificati come valori per le proprietà di associazione XmlStoredProcedureRootNodeNameName e XmlStoredProcedureRootNodeNamespace .
Procedure consigliate
Dopo aver distribuito e configurato il progetto BizTalk, è possibile esportare le impostazioni di configurazione in un file XML denominato file di associazione. Dopo aver generato un file di associazione, è possibile importare le impostazioni di configurazione dal file, in modo che non sia necessario creare elementi come le porte di trasmissione e le porte di ricezione per la stessa orchestrazione. Per altre informazioni sui file di associazione, vedere Riutilizzare le associazioni degli adattatori.