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
USE [TSQL2012] --insert database name
SELECT TOP(0) *
FROM HR.Employees --insert individual table name
FOR XML AUTO,XMLSCHEMA
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.
USE [DYNAMICS] --insert database name
s.name AS '@schema',
t.name AS '@name',
t.object_id AS '@objid',
SELECT c.name AS '@name',
c.column_id AS '@objid',
IIF(i.object_id IS NOT NULL,1,0) AS '@IsPrimaryKey',
f.referenced_object_id AS '@ColumnReferencesTableId',
f.referenced_column_id AS '@ColumnReferencesTableColumnId'
FROM sys.columns AS c
LEFT OUTER JOIN sys.index_columns AS i ON c.object_id = i.object_id
AND c.column_id = i.column_id
AND i.index_id = 1
LEFT OUTER JOIN sys.foreign_key_columns AS f ON c.object_id = f.parent_object_id
AND c.column_id = f.parent_column_id
WHERE c.object_id = t.object_id
FOR XML PATH ('Column'),TYPE
FROM sys.schemAS AS s
INNER JOIN sys.tables AS t ON s.schema_id = t.schema_id
FOR XML PATH('Table'),ROOT('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.
As you can see, each column is included and the column
PRODID has the value for
IsPrimaryKey set to
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.