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.

XClipboard Vista Gadget

Since I first started using Windows Vista, I liked Windows Side Bar so much, with the breadth of gadgets the user can have in the sidebar, however Gadgets are booming, and everyone can create his own gadget, from the very basic level, if someone has some knowledge with HTML and JavaScript, he can create his unique gadget, publish it to the others to start using it.

So long time ago, I wanted to create my gadget, but I lacked the good idea, that will add some humble functionality to Windows SideBar, then finally I got the idea and implemented it, published at CodeProject, it's simply a multi-item sidebar, like the Office Clipboard, and Visual Studio clipboard, please have a visit to XClipboard Gadget on CodeProject, to get more details and download the source code.

BTS 2006 DB Vocabulary Problem

I have been facing this problem with Business Rules Composer, whenever I try to create a new vocabulary, that's pointing to a database column I get the above error, and actually I wasn't aware about what's going on behind the scenes, I do the following steps to create a new database based vocabulary:

• Launch Business Rules Composer.
• Right click Vocabularies, and select Add New Vocabulary.
• Under the new vocabulary right click Version 1.0 (not saved), and select Add New Definition.
• Select Database Table or Column.
• The wizard shows the settings page for the database table or column definition, click Browse, from Connect to SQL Server window, enter the server configurations you want to connect to, I connect to localhost and use windows authentication.
• Select AdventureWorks database, expand it and select any table, e.g. Address, you will get the above error message box saying: "Invalid object name {TableName}" , after so many inspections and selecting differnet tables, I tried to select another table such as AWBuildVersion, I could add it successfully and selected the field I want to add as a definition, I concluded the following : "If the table you're adding is related to any schema other than dbo, you get the above error.", I tried to find out a resolution to this issue but I couldn't, if anybody has a solution please reply to this post!.

Note: I tried to create a new database, in case AW database has any special configuration, and added 2 tables one with dbo schema and the other with another schema I created, then I tried to add a new definition, but I faced the same problem; I could use the dbo schema table, but for the other table it throws the same error.

VS.NET 2005 SP1 Demystified

VS.NET 2005 SP1 is one of the things that makes you pull your hair out, I have spent so long time trying to figure out why it's taking all this long time, I ran it about 3-4 times and every time it takes about one hour and then throwing one error saying : "The installation source for this product is not available. Verify that the source exists and that you can access it.", and once I click OK, it rolls back everything, that's really irritating, I used to tell the team mates that it's taking longer time than Windows Server 2003 SP1 takes, however after so many trials and searching, I am recaping it all in this post.

VS.NET 2005 will take an ample time up to 2-3 hours depending on your machine, however also the time it's taking depends on the the components you installed during installing VS.NET 2005, for me I have all the features installed and I have installed Team Foundation Server, TFS, Team Explorer, however if you are updating VS.NET 2005 on Windows Vista you will need to install another batch after you finish installing SP1, you can get this update from this location; Visual Studio 2005 Service Pack 1 Update for Windows Vista.

Let's get back to the error message that you may get while installing SP1, "The installation source for this product is not available. Verify that the source exists and that you can access it." After getting this message, I checked the event viewer and found one error under Application category, the error source is MsiInstaller, the error message I have got is saying: "The installation of C:\DOCUME~1\Kareem\LOCALS~1\Temp\ZNW19\VS80sp1-KB926601-X86-ENU.msp is not permitted due to an error in software restriction policy processing. The object cannot be trusted." after some searching I found this on Microsoft Help and Support, you can check it at: Error message when you try to install a large Windows Installer package or a large Windows Installer patch package in Windows Server 2003 or in Windows XP: "Error 1718. File was rejected by digital signature policy, it simply clamis that the error is because of the large size of MSI package, and actually VS.NET 2005 SP1 is 431 MB (452,643,696 bytes), so it may fail if you don't have enough memory, also I tried to increase my virtual memory, despite having 1GB RAM, but it didn't work. second reason that you will find at Microsoft help and support is that the package may be uncorrectly signed, I edited the registry but it didn't work with me, I proceeded through the solution section, and I edited the software restriction policies, please to get the full steps check the above help and support section.