Creating An XML Database Schema Of An Existing Database In SQL Server

Though this is not something I have had to do very often I thought this would be a good opportunity to discuss using XML (eXtensible Markup Language) to create a schema of an existing database.  Having an XML schema can be very useful for a variety of functions within SQL Server.  Most commonly, it can be used, in conjunction with SQL Server Integration Services to rebuild a table or run comparative analysis of changes that may have been made to the database or individual tables.

I have included two scripts below that will create an XML schema from and existing database in SQL Server using the FOR XML command.

The first will create an XML schema of an individual table by simply querying the table and using FOR XML.

This script can also be modified, by removing the TOP(0) code, to include the actual data within the table if necessary.  When using FOR XML there are several modes that can be used. In the example above I am using the AUTO mode, which generates nesting in the XML output using logic based on the way the SELECT statement is specified.  Using AUTO will limit the control you have over the layout of the generated XML.

A more useful, robust option is to create a detailed schema for an entire database.  This will include all tables, columns, primary keys and relevant column IDs.  With this information you could easily build an import to recreate the entire database structure, if needed.

The following script will create a full XML database schema by querying various system tables.

In the example above I am using the PATH mode when calling the FOR XML command.  The PATH mode, similar to the EXPLICIT mode allows for more control over the layout of the XML by allowing you to combine attributes when designing the overall layout.

To give you an idea of how the output will appear, if you’re not already familiar with XML, the below screenshot shows the table schema for the AAG00100 table alongside the column drop-down in SQL Management Studio for the same table.

aag00100_schema_compare

As you can see, each column is included and the column PRODID has the value for IsPrimaryKey set to 1.

XML output in SQL Server is well designed and can be used for numerous functions from importing schemas to building data files for applications to creating EDI documents.  Even if you don’t work with XML all that often its a good idea to be familiar with the format and understand the options available when dealing with XML.