Using FOR XML Queries With the WCF-SQL Adapter
Introduction
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.
Why use FOR XML?
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.
Let's say we have a database that handles purchase orders. A purchase order has some information of its own and then it has line items that are part of the order. The table structure is shown below.
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
With FOR XML, we can leverage SQL Server's built-in heuristics that will create an XML structure for us that better represents the parent-child relationship that is an order and line items. This query turns out to be very easy as seen in the following query.
select * from [Order] JOIN OrderLine ON Id = OrderId FOR XML AUTO, ELEMENTS
The resulting XML is also very simple as shown below.
<Order>
<Id>47</Id>
<OrderNumber>3774632</OrderNumber>
<CustomerName>John Doe</CustomerName>
<CustomerAddress1>123 Fake St</CustomerAddress1>
<CustomerState>IL</CustomerState>
<OrderTotal>247.54</OrderTotal>
<OrderLine>
<OrderLine>49</OrderLine>
<OrderId>47</OrderId>
<ItemNumber>54346</ItemNumber>
<Quantity>1</Quantity>
<UnitCost>2.475400000000000e+002</UnitCost>
<Description>Some Item</Description>
</OrderLine>
<OrderLine>
<OrderLine>50</OrderLine>
<OrderId>47</OrderId>
<ItemNumber>44352</ItemNumber>
<Quantity>2</Quantity>
<UnitCost>1.542500000000000e+002</UnitCost>
<Description>Another Item</Description>
</OrderLine>
</Order>
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.
Generating schemas?
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.
Tying it all together
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.
See Also
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.