SYSK 247: Generate BizTalk XSD from SQL Server Generated XML

1.) Use the “FOR XML AUTO, ELEMENTS” syntax. You can use joins for any relationships.

For Example:

                SELECT * FROM Table1 ON LEFT OUTER JOIN Table2

                                ON Table1.pk_id = Table2.fk_id

                FOR XML AUTO, ELEMENTS

 

2.) Copy and paste the resulting text into Notepad and save as an xml file.

 

3.) From a BizTalk Project in Visual Studio, do the following:

a. Right-click on Project and click “Add Generated Items…”

b. Pick “Generate Schemas” and then click “Add”.

c. Select “Well-Formed XML” as a Document Type.

d. Browse to the XML file as an Input file.

e. Click OK.

 

Special thanks to John Livingston for this information.

Comments

• Anonymous
  November 29, 2006
  When you take this approach, BizTalk will attempt its best guess for the data types in each column.  However if you have a varchar column that happens to only have numbers in it, then BizTalk might incorrectly assume that column is an int datatype. If you want complete schema information for the data column types in SQL, you can add FOR XML AUTO, ELEMENTS, XMLDATA (the ELEMENTS tag is optional, and is left to your personal preference for attributes or elements). You can then take everything in the <schema> block and paste it into notepad and save that as an XDR schema.  BizTalk can import this and translate it to XSD, and you can be certain your datatypes will be correct. Even better - With SQL 2005, you can generate XSD schemas with the XMLSCHEMA tag: http://msdn2.microsoft.com/en-us/library/ms175113.aspx