The WCF-SQL adapter is the official replacement for the old SQL Adapter in BizTalk and is available across three versions of the product (2006 R2, 2009 and 2010). This adapter greatly increases the capabilities of interacting with SQL Server from BizTalk. It also advertises backward compatibility with the older adapter. There are a few subtle points to using the approach of the classic SQL adapter that is covered in this article.
FOR XML is a relatively little known feature of SQL Server that makes reading normalized records vastly easier. The basic concept is that some query keywords will enable SQL Server to output XML as the result of a query, instead of tabular data. This is extremely useful when dealing with parent-child records that you need to read from a database.
Using classic T-SQL if we wanted to get all this data out we could join the two tables and then we would get a longer flat record that repeated the data for the order in every record as shown below.
select
*
from
[
Order
]
JOIN
OrderLine
ON
Id = OrderId
FOR
XML AUTO, ELEMENTS
The resulting XML is also very simple as shown below.
<
>
Id
>47</
OrderNumber
>3774632</
CustomerName
>John Doe</
CustomerAddress1
>123 Fake St</
CustomerState
>IL</
OrderTotal
>247.54</
>49</
OrderId
ItemNumber
>54346</
Quantity
>1</
UnitCost
>2.475400000000000e+002</
Description
>Some Item</
</
>50</
>44352</
>2</
>1.542500000000000e+002</
>Another Item</
We can see that this XML is a much better representation of the order construct. There are also other options available with FOR XML including the ability to completely define the resulting XML structure. For our purposes, this will work fine.
At first, this part was extremely frustrating to me. The new wizard does now allow us to generate schemas the way the old wizard did. The approach is much more manual. Although not that difficult to do; BizTalk has made so many other things easy that I just expected this to be easier and wizard based. It turns out that it still is. We can still use the classic SQL Adapter wizard to generate the schemas that we use just like we used to. Again, like with the classic SQL Adapter, we must use the XMLDATA directive on the query in order to run the wizard and have the schema generated for us. For more information on using the classic SQL Adapter see How to Add SQL Adapter Schemas to a BizTalk Project.
We’re almost done. This last part is the only subtle change between the two adapters. After the schema is generated we must set the Element Form Default on the schema to Unqualified. The classic adapter wizard sets this as Qualified. This setting is set at the schema level in the BizTalk schema editor within Visual Studio. After this everything works exactly as you would expect and we can continue using a pattern that has been well developed and tested over time.
Read suggested related topics:
Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.