BizTalk & SQL Integration: WCF CUSTOM Adapter - Pooling XML type of data from SQL
There are many adapters using which we can integrate BizTalk with SQL server, for example ‘SQL’, ‘WCF-SQL’,’WCF-CUSTOM’. In this example, we will focus on how we can leverage WCF Custom adapter to consume data from a SQL DB.
For this example, we 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 a known issue with xml/varchar(MAX) data types.
This is what the SQL table looks like:
https://psrathoud.files.wordpress.com/2015/09/database.png?w=300&h=136
The Stored procedure
https://psrathoud.files.wordpress.com/2015/09/storesproc.png?w=300&h=212
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 off 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:
https://psrathoud.files.wordpress.com/2015/09/consumewhatstep0.png?w=300&h=206
https://psrathoud.files.wordpress.com/2015/09/addadapter_1.png?w=300&h=241
https://psrathoud.files.wordpress.com/2015/09/addadapter_2.png?w=300&h=270
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 a 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.
Procedure Category Schema definition will be following:
https://psrathoud.files.wordpress.com/2015/09/anytype.png?w=300&h=184
For Strongly Typed Procedures the schema definition will be following:
https://psrathoud.files.wordpress.com/2015/09/typedschema.png?w=300&h=228
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 though.
https://psrathoud.files.wordpress.com/2015/09/autogeneratedorchestration.png?w=300&h=153
Next step would be to create the orchestration which will invoke the stored procedure. This is how this orchestration looks. Since the stored procedure is expecting an input parameter we are providing this via an another message which is set to be the activation subscription of this orchestration. We are using another a transform to pass on this value to the input request of the stored procedure request schema. Further, we have published the orchestration as service to save ourselves from creating few extra ports.
https://psrathoud.files.wordpress.com/2015/09/orchestration.png?w=300&h=140
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 the 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
https://psrathoud.files.wordpress.com/2015/09/sendport-and-binding.png?w=300&h=210
Bind your orchestration and 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 going to use in case of typed and untyped message:
Untyped:
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()=”])”);
Typed:
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 an expression like msgSQLResponse.xmlload.
For the final response coming out of the orchestration we 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 invoke the service and get the stored procedure response in the form of an Orchestration response. This is how it is going to look when you invoke the orchestration using a web service client.
https://psrathoud.files.wordpress.com/2015/09/soapcall.png?w=300&h=123
