BizTalk Server 2004 and Sql Server 2000 -> XML Auto and XML Explicit

BizTalk 2004 provides support for processing XML documents returned from Sql Server
via the Sql Adapter that ships with BizTalk Server 2004.

For example:

Select *
From Customers
For XML Auto

will produce an XML document that can be processed by BizTalk 2004 received via the Sql Receive Adapter.

The BizTalk documentation states that only the XML Auto clause is supported and
not the XML Explicit clause. But in fact BizTalk can process Sql Server XML documents that
are generated using the XML Explicit clause (this will be explained below).

If you have not used the Sql Adapter before, try the following two examples in the BizTalk SDK:

Using the SQL Adapter with a Stored Procedure in an Orchestration

SQL Adapter (BizTalk Server Sample)

For BizTalk to interact with an XML document originating from Sql Server,
one of the first steps is to create a xsd schema that describes the
XML returned from a Select statement.
This xsd schema can be automatically generated in a BizTalk project by Adding a Generated Item, as outlined
HERE  This is only if the Select statement has an XML Auto clause (and XMLData clause, that is removed after the
schema is generated). If a Select Statement with a XML Explicit clause is used to generate the
schema, the xsd schema generation will fail with an error such as : The required attribute 'name' is missing.
An error occurred at , (0,0). Or : Failed to execute sql Statement. Please ensure that the supplied syntax is correct.

This is possibly why the BizTalk documentation states that the XML Explicit is not supported.
But again, BizTalk can use XML returned by XML Explicit (explained below).

So why use the XML Explicit clause over the XML Auto clause? Simply because the XML Explicit clause
gives you much more control of the XML structure that is returned by Sql Server compared to
the XML Auto clause.

For example, an Order is associated with a Shipper, Customer and Order Items. 
The following Select Statement using XML Auto:

Select [Order].OrderID,
 Shipper.ShipperID as ShipperID,  
 Shipper.CompanyName As ShipperName,
 Customer.CompanyName as CustomerName,
 OrderDetails.Quantity as Quantity,
 OrderDetails.UnitPrice as UnitPrice,
 OrderDetails.ProductID as ProductId  
From Orders as [Order]
 Join Shippers as Shipper
 On [Order].ShipVia = Shipper.ShipperID
        Join Customers as Customer
   On [Order].CustomerID = Customer.CustomerID 
        Join [Order Details] as OrderDetails
 On [Order].OrderID = OrderDetails.OrderID 
For Xml Auto

Will return XML in the format of:


<Orders>
 <Order OrderID="11077">
  <Shipper ShipperID="2" ShipperName="United Package">
   <Customer CustomerName="Rattlesnake Canyon Grocery">
      <OrderDetails Quantity="24" UnitPrice="19.0000" ProductId="2" />
      <OrderDetails Quantity="4" UnitPrice="10.0000" ProductId="3" />
   </Customer>
    </Shipper>
 </Order>
 <Order OrderID="11076">
  <Shipper ShipperID="2" ShipperName="United Package">
   <Customer CustomerName="Bon app'">
      <OrderDetails Quantity="20" UnitPrice="25.0000" ProductId="6" />
      <OrderDetails Quantity="20" UnitPrice="23.2500" ProductId="14" />
      <OrderDetails Quantity="10" UnitPrice="9.2000" ProductId="19" />
     </Customer>
  </Shipper>
 </Order>
</Orders>
 
  Note: That the top level parent node Orders is added by the Sql Adapter

The above format is not desired because the parent node
of the Customer node is the Shipper node and the parent node of the
OrderDetails is the Customer node. In this case the desired format of the
XML message will have the Order Node being the parent node of the
Shipper, Customer and OrderDetails nodes as below:


<Orders>
  <Order OrderID="11077">
     <Shipper ShipperID="2" ShipperName="United Package"></Shipper>
     <Customer CustomerName="Rattlesnake Canyon Grocery"></Customer>
     <OrderDetails>
       <OrderDetail Quantity="24" UnitPrice="19.0000" ProductId="2" />
       <OrderDetail Quantity="4" UnitPrice="10.0000" ProductId="3" />
     </OrderDetails>     
  </Order>
  <Order OrderID="11076">
    <Shipper ShipperID="2" ShipperName="United Package"></Shipper>
    <Customer CustomerName="Bon app"></Customer>
    <OrderDetails>
       <OrderDetail Quantity="20" UnitPrice="25.0000" ProductId="6" />
       <OrderDetail Quantity="20" UnitPrice="23.2500" ProductId="14" />
       <OrderDetail Quantity="10" UnitPrice="9.2000" ProductId="19" />
   </OrderDetails> 
 </Order>
</Orders>


To return the XML in this (above) format, the below Select statement with a XML Explicit clause is used:

SELECT  1     as Tag,
        NULL  as Parent,
 Orders.OrderID as [Order!1!OrderId],
 Null as [Shipper!2!ShipperID],
 Null as [Shipper!2!ShipperName],
 Null as [Customer!3!CustomerName],
 Null as [OrderDetails!4!OrderId!hide],
 Null as [OrderDetail!5!Quantity],
 Null as [OrderDetail!5!UnitPrice],
 Null as [OrderDetail!5!ProductId]
From Orders
 Join #OrdersToGet
 On #OrdersToGet.OrderID = Orders.OrderID
Union All
Select  2,
 1,
 #OrdersToGet.OrderID,
 Shippers.ShipperID,
 Shippers.CompanyName,
 Null,
 Null,
 Null,
 Null,
 Null
From Shippers
 Join #OrdersToGet
 On #OrdersToGet.ShipVia = Shippers.ShipperID
Union All
Select  3,
 1,
 #OrdersToGet.OrderID,
 Null,
 Null,
 Customers.CompanyName,
 Null,
 Null,
 Null,
 Null
From Customers
 Join #OrdersToGet
 On #OrdersToGet.CustomerID = Customers.CustomerID
Union All
Select  distinct 4,
 1,
 #OrdersToGet.OrderID,
 Null,
 Null,
 Null,
 OrderDetails.OrderID,
 Null,
 Null,
 Null
From [Order Details] as OrderDetails
 Join #OrdersToGet
 On #OrdersToGet.OrderID = OrderDetails.OrderID
Union All
Select 5,
 4,
 OrderDetails.OrderID,
 Null,
 Null,
 Null,
 OrderDetails.OrderID,
 OrderDetails.Quantity,
 OrderDetails.UnitPrice,
 OrderDetails.ProductId 
From [Order Details] as OrderDetails
 Join #OrdersToGet
 On #OrdersToGet.OrderID = OrderDetails.OrderID
Order By [Order!1!OrderId],Tag,[OrderDetails!4!OrderId!hide]
For XML Explicit  

Note: The above Sql Statement is part of a stored procedure that
can be downloaded at the end of this blog.

The XML Explicit clause is used with Unions of Select statements
and the keywords -> Parent and Tag to generate the desired result.
The Order By clause is also very important, when using XML Explicit.
It's a good idea to  take off the XML Explicit clause to view the regular sql result set (especially if the
format of your returned XML is incorrect or to help debug).
A good description of the XML Explicit clause with some examples is as below:

Using EXPLICIT Mode

Below describes how to create a BizTalk xsd Schema for XML returned using a XML Explicit Sql Statement:

1) Create and test (perhaps using Query Analyzer) a XML Explicit Sql Select statement
that includes the Tag, Parent and Unions of Select Statements.

2) Create a Sql Receive port that will invoke the Select Statement (this could be a stored proc)
that contains the XML Explicit clause. This Sql Receive Port should use the PassThrough Pipeline.
Note: in the Receive Location properties you must include the namespace of the
generated XML, Root Node name, Select Statement or Stored Procedure. In this example it was :
Document Target NameSpace : http://ObjectSharp/Orders
Document Root Element Name: Orders
Sql Command: exec BTSGetOrders 1000

3) Create a File Send Port that subscribes to the Receive Port, for example create
a filter on the Send Port with BTS.ReceivePortName = "Name of Receive Port Here"
This Send Port should also use the PassThrough Pipeline.

4) Turn on the above two ports and let a sample message be placed into the File Location
of the Send Port.

5) Create a new BizTalk Project or use an existing one and add a Generated Item. Use the Generate
Schemas option and Select -> Well-Formed XML. Select the xml file produced in step 4).
A xsd schema is now generated that can be used in a BizTalk Solution. If this BizTalk
project is deployed, the Receive Port and Send Ports created in Step 2) and 3) can now use the
standard XML Pipelines.
Note : It may be necessary to modify the xsd schema to alter the attribute types
min max properties etc.

6) The produced xsd schema in 5) can now be used in a BizTalk Solution for Mapping, Messages etc.
Orchestrations can subscribe to xml messages that are derived from a Select Statement that uses
the XML Explicit Clause.

Conclusion: Select Statements using the XML Explicit clause can generate XML documents
that can be consumed by BizTalk Server 2004.


Download the Sample Stored procedure and XSD file HERE

Note : The sample uses tables found in the NorthWind Database that is optionally installed with
Sql Server.