Creating BizTalk Schemas From Database

Introduction

Sometimes you may need to create your schemas based on database tables, so it would be very tedious if you go to BizTalk Editor, and start building your schemas, while you already have the tabular model built at SQL Server, needless to say that SQL Server 2005 provides a great and inherent support for XML, the magical FOR XML clause has been enhanced to give more flexbility and funcionality, you can read more about the FOR XML new features at What's New in FOR XML in Microsoft SQL Server 2005.

Preparing Sample Data

However, I was trying to automate building BizTalk Schemas from Database tablse, and this
algorithm flashed in my mind:

1. Build a small generator, that will list all the database tables, and then execute one Query using FOR XML to read target table contents, and set XML namespace, and rename root name.
2. Generate one file for each table, that will be named using the same name of the table, considering the SQL Schema, in this case, e.g. Users.Suppliers.
3. Use BizTalk Editor Wizard, hopefully using API , to generate a XSD schema from the generated table contents.
4. Inspect all elements' types manually; in case I get any inconsistent type generated.

That is the algorithm that I tried to follow to generate schemas from database tables, but after some searching, I didn't find any API about how to use the generator wizard, and then I did it manually using a query in SQL Server 2005 Management Studio, so in this post I will point out what I have done in very basic steps to generate BizTalk Schemas from database tables. I will use the AdventureWorks sample database that is shipped with SQL Server 2005, also I will use Production.Product table, and also I will proceed step by step until we acheive our goal that's generating the schema successfully.

First I tried to run this Query against the AdventureWorks database:

SELECT TOP 1 * FROM Production.Product FOR XML AUTO

And it yielded the following results:

<Production.Product ProductID="1" Name="Adjustable Race" ProductNumber="AR-5381" MakeFlag="0" FinishedGoodsFlag="0" SafetyStockLevel="1000" ReorderPoint="750" StandardCost="0.0000" ListPrice="0.0000" DaysToManufacture="0" SellStartDate="1998-06-01T00:00:00" rowguid="694215B7-08F7-4C0D-ACB1-D734BA44C0C8" ModifiedDate="2004-03-11T10:01:36.827" />

If you compare this instance to the table's design you will find so many fields omitted, such as ProductLine, Class, DiscontinuedDate, etc.., these fields are not included because SQL Server omits the null-valued fields, so this is the first problem that should be sorted out, secondly all the fields are returned as attributes, this is because of including the AUTO clause, and mostly you will need to make the properties of your schema as elements, so this should be modified as well.

To convert all the attributes into elements modify the query to be like this:

SELECT TOP 1 * FROM Production.Product FOR XML Path('Product')

This gives the following results:

<Product>

<ProductID>1</ProductID>

<Name>Adjustable Race</Name>

<ProductNumber>AR-5381</ProductNumber>

<MakeFlag>0</MakeFlag>

<FinishedGoodsFlag>0</FinishedGoodsFlag>

<SafetyStockLevel>1000</SafetyStockLevel>

<ReorderPoint>750</ReorderPoint>

<StandardCost>0.0000</StandardCost>

<ListPrice>0.0000</ListPrice>

<DaysToManufacture>0</DaysToManufacture>

<SellStartDate>1998-06-01T00:00:00</SellStartDate>

<rowguid>694215B7-08F7-4C0D-ACB1-D734BA44C0C8</rowguid>

<ModifiedDate>2004-03-11T10:01:36.827</ModifiedDate>

</Product>

To add the root node and rename it to Products, you can run the following query:

SELECT TOP 1 * FROM Production.Product FOR XML Path('Product'),ROOT('Products')

This gives the same recordset but surrounded with :

<Products>

<Product>

</Product>

</Products>

To include all the fieldset for the table even the null valued columns, you can run the following query:

SELECT TOP 1 * FROM Production.Product FOR XML Path('Product'),ROOT('Products'), ELEMENTS XSINIL

This gives the following results :

<Products xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">;

<Product>

<ProductID>1</ProductID>

<Name>Adjustable Race</Name>

<ProductNumber>AR-5381</ProductNumber>

<MakeFlag>0</MakeFlag>

<FinishedGoodsFlag>0</FinishedGoodsFlag>

<Color xsi:nil="true" />

<SafetyStockLevel>1000</SafetyStockLevel>

<ReorderPoint>750</ReorderPoint>

<StandardCost>0.0000</StandardCost>

<ListPrice>0.0000</ListPrice>

<Size xsi:nil="true" />

<SizeUnitMeasureCode xsi:nil="true" />

<WeightUnitMeasureCode xsi:nil="true" />

<Weight xsi:nil="true" />

<DaysToManufacture>0</DaysToManufacture>

<ProductLine xsi:nil="true" />

<Class xsi:nil="true" />

<Style xsi:nil="true" />

<ProductSubcategoryID xsi:nil="true" />

<ProductModelID xsi:nil="true" />

<SellStartDate>1998-06-01T00:00:00</SellStartDate>

<SellEndDate xsi:nil="true" />

<DiscontinuedDate xsi:nil="true" />

<rowguid>694215B7-08F7-4C0D-ACB1-D734BA44C0C8</rowguid>

<ModifiedDate>2004-03-11T10:01:36.827</ModifiedDate>

</Product>

</Products>

Now, the next step is to add namespace to the generated output, you can accomplish this by running WITH XMLNAMESPACES, that accepts a list of named namespaces to your output XML:

WITH XMLNAMESPACES

(

DEFAULT 'http://CairoCafe.Blogspot.com/Production.Product'

)

SELECT TOP 1 * FROM Production.Product FOR XML Path('Product'),ROOT('Products'), ELEMENTS XSINIL

This will append to the root node (Products), this XML namespace:

xmlns="http://CairoCafe.Blogspot.com/Production.Product"

One last thing is that, for all null valued columns, the BizTalk Schema generator, tries to assign a proper data type for one element, but whenever all the column values are null, the generator will not be able to reveal the data type of the element and it assignes xs:anytype to the element's data type, so it is always better to feed the generator with the maximum number of XML records, so this will make the job of the generator easier, so to work this out, we need to remove the TOP 1 from the previous queries and then run the last SQL query save the contents into one XML file, let's call it Production.Product_Instance.xml, we will use this file to generate the XSD schema.

Generating Schema using Generator Wizard

Now, our sample data XML file is ready, and all we need to do is few steps to get the schema created:

1. Launch VS.NET 2005, and create an Empty BizTalk Project, and give it any name.
2. Right click your project from solution explorer and select "Add Generated Items.."
   
   
   
3. Add Generated Items Wizard Will launch, Select Generate Schemas from left tree and press OK
   
   


4. You can generate schemas from DTD Schema, XDR Schema, or Well-Formed XML, select Well-Formed XML, and browse and select the Production.Product_Instance.xml file
   
   Note: XDR Schema is the only installed feature, for the other 2 document types, you have to install manually, browse to C:\Program Files\Microsoft BizTalk Server 2006\SDK\Utilities\Schema Generator , and you will find two script files, .vbs, run the first one InstallWFX.vbs , to install the Well-Formed XML document type, and for DTD Schema, run InstallDTD.vbs.


5. Press OK to get the schemca generated.


6. Schema is generated if you go through all the elements, you will find that the data types are set accurately, and with proper memory allocation, but feel free to change the data types as per your design, one important thing to mention is the DiscontinuedDate field, it has datatype as xs:anytype, because it is null for all the XML rows in the generated XML file, you will need to change this manually, if you try to do it from the properties window, VS.NET is not listing the other datatypes if the datatype is xs:anytype, so you will need to change it either in notepad or open using XSD schema editor and viewing code, you can do this by right clicking the schema from the solution explorer and selecting : Open With.. , then XML Schema Editor.