Creating and consuming a BizTalk message through SQL Server without using SQL Server Adapter
Sometimes using the SQL Server adapter is not possible due to some kind of requirement and developers must deal with a mechanism to query SQL Server data in order to go through the business flow.
This is just another way to achieve this requirement fitting the results with an XML schema defined by the data itself.
In this scenario the BizTalk XML schema doesn’t exists and developers are going to take advantage of FOR XML Clause and the SQL Server Management Studio Console to automatically create the BizTalk schema based on the data retrieved by a SELECT statement.
Steps
#1# Returning a SQL Server query as XML with T-SQL
The query needed by the business flow could be as follows
SELECT CustomerID, Name, Bank, Type
FROM customers
ORDER BY CustomerID
Running this simple query in the SQL Server Management Studio shows the following results :
With SQL Server 200X you can automatically transform this standard output to XML format by simply using the FOR XML T-SQL clause:
SELECT CustomerId, Name, Bank, Type
FROM customers
ORDER BY CustomerID
FOR XML AUTO
which produces the following output:
by clicking on the link you will get the following XML data:
As can be seen, the AUTO modifier creates the element name by using the following naming convention:
DataBaseName.owner.TableName
and represents table fields as element attributes.
To customize the element name on your own (not the attributes) we need to replace the AUTO modifier using the RAW (‘DesiredElementName’) modifier. In our case,
SELECT CustomerId, Name, Bank, Type
FROM customers
ORDER BY CustomerID
FOR XML RAW (‘Customer’)
By default, FOR XML represents fields as attributes. In our case we want each field to be and independent XML element. We can achieve this by using the ELEMENTS modifier
SELECT CustomerId, Name, Bank, Type
FROM customers
ORDER BY CustomerID
FOR XML RAW (‘Customer’), ELEMENTS
#2# Assigning a target namespace to identify the message
Microsoft BizTalk server recognizes messages used with the combination of the following things:
- Target namespace
- Root name
Which internally represents the messagetype property (target Namespace # RootName). Then, in order to allow BizTalk to consume the message, we need to assign:
- a xml target namespace
- and a root name
to the xml already retrieved by the SQL query.
We can use the WITH XMLNAMESPACES option to identify the message (do not forget to include de ns0:FieldName alias in order to assign the elements to the schema specification)
WITH XMLNAMESPACES (‘https://BizTalk/SQLXMLTest.XMlQuery’AS ns0)
SELECT CustomerId AS ‘ns0:CustomerId’
, Name AS ‘ns0:Name’
, Bank AS ‘ns0:Bank’
, Type ‘ns0:Type’
FROM customers
ORDER BY CustomerID
FOR XML RAW(‘ns0:Customer’),ELEMENTS, ROOT(‘ns0:Customers’)
The resultant XML document it is now identified with the targetnamespace ‘https://BizTalk.SQLXMLTest.XMLQuery’ and the root name ‘Customers’
#3# Creating the BizTalk message (based on the resultant XSD)
With the following procedure, you will save time by avoiding to create the whole XSD file (schema) manually.
Within the SQLServer Management Studio, click on XML menu and then CreateSchema option. This will automatically show the generated schema inferred from the XML data previously obtained.
You can now save the XSD file in an accessible location by the BizTalk project and use it as needed :-)
#4# Consuming the message within an orchestration using a .NET component
Remember we are not using the SQL Server adapter?
Right, to achieve this, we need to create a .NET component that runs the stored procedure (or just a query directly) which will return the XML Data designed in previous steps.
Pretty sure you are able to deal with a .net component or to find references out of this thread :-) and even assuming you have already created a .NET component ready to be consumed by BizTalk , i have just decided to include an example:
Using System.Xml;
.
.
.
Public XmlDocument RunSP (string sp) //we are planning to return the XML data retrieved by the query loaded in an XML Document
{
//code for calling the stored procedure that will return the XML Data. May be calling a SQL Server helper????
.
.
.
string _sXml = “content of the XML data returned by the execution of the stored procedure”;
XmlDocument _xmlDoc = new XmlDocument();
_xmlDoc.LoadXml ( _sXml);
Return _xmlDoc;
}
Create the Message msgCustomers based on the schema saved on step 3. The last step would be to insert a message assignment shape within the orchestration and typing the consuming code:
msgCustomers = SQLServerHelper.RunSP ( “GetCustomers”);
If everything works as expected you will have a completed orchestration in the tracking database, for sure, if tracking is enabled ;-)
Good Luck!