There are many adapters using which we can integrate BizTalk with SQL server, for example ‘SQL’, ‘WCF-SQL’,’WCF-CUSTOM’. In this example I am going to focus on how we can leverage WCF Custom adapter to consume data from a SQL DB.
For this example I will be consuming data from a SQL Database table using a stored proc. The data is being pooled from a table columns which is defined to be of the xml data type.
Note: For this scenario using SQL Adapter will not work because of an known issue with xml/varchar(MAX) data types.
This is how my SQL table looks like:
The Stored procedure
The stored proc is designed to accept parameters and it will return a result based on that. Hence we will be required to consume this stored proc on a two way send port side .
First of we will be required to create the contract for calling the stored procedure. Here is how you can do that: Use the “Add Generated Items” option provided by Visual Studio to generate it. We are going to pick consume Adapter Service here. This is going to give you a binding file and schema files. This binding file you can use to import later to create your send port. After you pick consume Adapter Service you are going to be presented with few windows:
Under the category you are presented with “Procedures” & “Strongly Types Procedures”. You can pick any of them however the first category will generate the schema with node of any type and the later will generate schema a specific data type, in our example it will be string type. Please see the images below.
For Procedure Category Schema definition will be following:
For Strongly Typed Procedures the schema definition will be following
Notice the difference in the data type of the node selected. Based on the type of schema generated you will have to handle accordingly. From usage perspective choosing the strongly typed stored procedure is more convenient.
The Add Generated Item is also going to give you the option of “Add Adapter Metadata”. Using this will result in generation of an orchestration which will have the port type already defined within with request and response message set to stored procedure Request and Response. You wont get the port binding file thou.
Next step would be to create the orchestration which will invoke the stored procedure. This is how my orchestration looks like. Since my stored procedure is expecting an input parameter I am providing this via an another message which is set to be the activation subscription of this orchestration.I am using another a transform to pass on this value to the input request of the stored procedure request schema. Further I have published the orchestration as service to save myself from creating few extra ports :-).
Once you are done deploying your BizTalk solution. You will be required to import the binding file which was generated for us by BizTalk. That will result in creation of a send port. Change the SOAP Action header to following :Procedure/dbo/YourSPName in case of typed you will be using :TypedProcedure/dbo/YourSPName
Bind your orchestration and start start all the artifacts.
Trigger it and you will notice that the data fetched from SQL comes wrapped under CDATA. this is a unique scenario where the response for the untyped schema will be following Notice your actual payload comes wrapped under <![CDATA…
<GetXMLDataResponse xmlns=”http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo”><GetXMLDataResult><DataSet xmlns=”http://schemas.datacontract.org/2004/07/System.Data”><xs:schema id=”NewDataSet” xmlns:xs=”http://www.w3.org/2001/XMLSchema” xmlns:msdata=”urn:schemas-microsoft-com:xml-msdata”><xs:element msdata:IsDataSet=”true” name=”NewDataSet”><xs:complexType><xs:sequence><xs:element minOccurs=”0″ maxOccurs=”unbounded” name=”NewTable”><xs:complexType><xs:sequence><xs:element minOccurs=”0″ name=”id” type=”xs:int”/><xs:element minOccurs=”0″ name=”XMLData” type=”xs:string”/></xs:sequence></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element></xs:schema><diffgr:diffgram xmlns:diffgr=”urn:schemas-microsoft-com:xml-diffgram-v1″><NewDataSet xmlns=””><NewTable><id>1</id><XMLData><![CDATA[YOUR ACTUAL XML PAYLOAD]]></XMLData></NewTable></NewDataSet></diffgr:diffgram></DataSet></GetXMLDataResult><ReturnValue>0</ReturnValue></GetXMLDataResponse>
The response of the typed will not have CDATA section.
Now since we intend to extract the actual xml payload for our further processing we will have to extract it using XPATH (IN case of untyped) or XPATH/Message parameter(In case of typed).
Here is the example of XPATH we are goign to use in case of typed and untyped message:
StrPayload = xpath(MessageOP, “string(/*[local-name()=’GetXMLDataResponse’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo’%5D/*%5Blocal-name()=’GetXMLDataResult’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/Procedures/dbo’%5D/*%5Blocal-name()=’DataSet’ and namespace-uri()=’http://schemas.datacontract.org/2004/07/System.Data’%5D/*%5Blocal-name()=’diffgram’ and namespace-uri()=’urn:schemas-microsoft-com:xml-diffgram-v1′]/*[local-name()=’NewDataSet’ and namespace-uri()=”]/*[local-name()=’NewTable’ and namespace-uri()=”]/*[local-name()=’XMLData’ and namespace-uri()=”])”);
StrPayload = xpath(msgSQLResponse, “string(/*[local-name()=’GetXMLDataResponse’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’%5D/*%5Blocal-name()=’StoredProcedureResultSet0′ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo’%5D/*%5Blocal-name()=’StoredProcedureResultSet0′ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetXMLData’%5D/*%5Blocal-name()=’XMLData’ and namespace-uri()=’http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetXMLData’%5D)”);
Note:In the case of typed we can also fetch the payload using expression like msgSQLResponse.xmlload.
For the final response coming out of the orchestration I have used a message of type Microsoft.XLANGs.BaseTypes.Any which will allow me to plug any kind of XML response in the body. Once done I invoke the service and get the stored procedure response in the form of my Orchestration response. This is how it is going to look when you invoke the orchestration using a web service client.