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

Posted: Tuesday, March 22, 2005 11:20 PM by matt
Filed under: Newsletter, BizTalk

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:

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.

Comments

matt March 23, 2005 4:43 AM

Well done for blogging on this. I have been using for xml explicit more and more as it provides a far greater degree of control - so I thoroughly recommend BizTalkers to take a look.

Be warned - the syntax can be ugly!
matt August 24, 2005 7:41 AM

Have you used this with a solicit response port? I'm trying to figure out how to (dynamically) pass the parameter values to a sproc.
matt August 24, 2005 8:58 AM

Andy,
Yes I have used this with a Solicit Response Port. It works the same way as a regular for XML Auto Call as defined here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/operations/htm/ebiz_prog_adapt_tbdk.asp

What you might want to try is to initially use a XML Auto clause, in your proc, follow the same pattern that is described in the instructions in the above link. Then switch your proc to use XML explict. But, you will probably have to modify the Schema that was created by the XML Auto clause to now match your XML Explicit clause. One way to switch out the XML Auto schema with the XML Explicit schema, is to follow my instructions in this blog entry to create the schema for the XML Explicit clause. Then to open up the schema with the parameter and result root nodes, and then replace the result root node with the XML Explicit Schema.
matt August 25, 2005 3:02 PM

Matt, this worked perfectly. Thanks a lot!
matt March 7, 2006 5:22 PM

Hi Matt

That was a nice article.I was trying to use SQL adapter with Biztalk Server 2004 for the first time and encountered a problem.

My SQL Stored Proc accepts two parameters(DataType: uniqueidentifier).When I deployed the solution and tried to test, i got the below error:

The adapter "SQL" raised an error message. Details "HRESULT="0x80040e07" Description="Error converting data type nvarchar to uniqueidentifier."
".

I tried changing the datatype for the SP parameters to NVARCHAR but still got the below similar error:

The adapter "SQL" raised an error message. Details "HRESULT="0x80040e14" Description="Syntax error converting from a character string to uniqueidentifier."
".

Any help would be really appreciated.

Thanks
Vivek Khurana
Email: simplyvivek@gmail.com
matt March 20, 2006 6:16 PM

Matt,
Good article but why bother?
For XML Auto will work.

If in the SQL statement, you have ORDER BY parent, child1, child2
matt March 20, 2006 6:25 PM

It is good technique and Todd Sussman taught me that.

The danger:
Generating xsd from well-formed XML REQUIRES you to do extra work of preparing the data.

Imagine you need to map column1 in Order table.

But column1 data could be missing at the time you you generate the well-formed xml(to import this xml instance back and build the xsd).
You see my point?
This xml then will not have column1 data, and the schema will not have column1 attribute (or element).

This means you have to populate every column in every tables that are involved your SQL statement just to have a SAFE and COMPLETE schema.

In xml auto: all you do is:
select table1.* , table2.*
from.....
....
for xml auto
matt March 20, 2006 8:51 PM

Hung,

For your first statement. Why bother.
To bring the XML back using Sql 2000 in the below format with only one level of nesting:

<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>

With no sub nesting of the Sub nodes, XML Explicit must be used in Sql 2000.

Note: With Sql 2005, I have not tried but I am pretty sure I can get the XML to be returned in the above format using the enhancements to the XML Auto Clause in Sql 2005.

Please show me your Sql 2000 Select statement with XML Auto to bring back the XML in the above format, with only one level of nesting.

Please also read the below, for details on the
XML Auto clause in Sql 2000.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_4y91.asp

For your second statement, yes, I did mention that you may to revisit your xsd schema to resolve inaccuracies when generating from well formed XML
matt July 9, 2006 8:30 PM

Hi Matt,
Maybe I am missing something here:
The sample xml you provide still have many-level nesting. What do you mean by one level nesting? I see many in:
<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>
matt November 8, 2006 4:05 PM

Matt, I got you point.

You still have many record type with ONE level of nesting.
That is good.
Let me ask you this though.

Did you have a problem mapping many level nesting?
I had no problem what so ever.

From a good many level nesting xml, I can map to a one-level nesting target xml.

What problem you have with many level nesting xml structure?
matt November 29, 2006 12:26 PM

I must agree with hung. This kind of problems is in Xslt domain, not in SQL code. IMHO
myspace backgrounds html music codes iv September 17, 2007 9:25 PM

myspace backgrounds html music codes iv
Nicole January 25, 2008 1:31 PM

If used in conjunction with SQL Adapter, I am receiving an empty node when there are no more records to retrieve which fails in the XMLReceive pipeline. Is there some way to prevent the empty document from being received by the adapter?

Title (required)
Name (required)
Your URL (optional)
Comments (required)
	Remember Me?

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

ObjectSharp Blogs

Matt Meleski's .Net Blog - The ABC's of .NET

ASP.NET/ADO.NET/Biztalk 2004/C#

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

Comments

Leave a Comment

Syndication

Recent Posts

Archives

Tag Cloud

BizTalk Bloggers

BizTalk Sites

Matt Meleski

ObjectSharp Bloggers

ObjectSharp Consulting