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 backwards compatibility with the older adapter. There are a few subtle points to using the approach of the classic SQL adapter that are 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.