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 .

Create XML Schema For Individual Table USE [TSQL2012] --insert database name SELECT TOP(0) * FROM HR.Employees --insert individual table name FOR XML AUTO,XMLSCHEMA 1 2 3 4 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.

Create XML Schema For Database USE [DYNAMICS] --insert database name SELECT 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') 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 USE [ DYNAMICS ] --insert database name SELECT 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 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.