How to Create Xml Schema from an Existing Database in SQL Server 2008

how to create XML schema from an existing database in SQL Server 2008

If you only need the xml schema of tables query them with this:

select top 0 * FROM daTable FOR XML AUTO,XMLSCHEMA

If you need the table names and columns in order to create a representation of your database and how tables are connected you can use something like this:

SELECT
s.name as '@Schema'
,t.name as '@Name'
,t.object_id as '@Id'
,(
    SELECT c.name as '@Name'
    ,c.column_id as '@Id'
    ,IIF(ic.object_id IS NOT NULL,1,0) as '@IsPrimaryKey'
    ,fkc.referenced_object_id as '@ColumnReferencesTableId'
    ,fkc.referenced_column_id as '@ColumnReferencesTableColumnId'
    FROM sys.columns as c
    LEFT OUTER JOIN sys.index_columns as ic
        ON c.object_id = ic.object_id
        AND c.column_id = ic.column_id
        AND ic.index_id = 1
    LEFT OUTER JOIN sys.foreign_key_columns as fkc
        ON c.object_id = fkc.parent_object_id
        AND c.column_id = fkc.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')

Let your application use the ColumnReferencesTableId and ColumnReferencesTableColumnId to get table relations. You could also further join back to columns and tables which are referenced if you prefer writing their names out but I thought their Ids would suffice.

Getting XML Schema from MS SQL Database

Is it possible to generate a XML
Schema from a Database?

It sure is, XMLSpy can generate XML Schema from a database.

There's another way, though I've never tested it:

create table Person
(
Age int not NULL check( Age > 0) ,
Height numeric(10,2) not NULL check( Height > 5),
Gender varchar(5) not null check( Gender in ('M', 'F', 'O')),
BirthDate datetime null,
)

DECLARE @schema xml
SET @schema = (SELECT * FROM Person FOR XML AUTO, ELEMENTS, XMLSCHEMA('PersonSchema'))
select @schema

How to build a database from an XSD schema and import XML data

I suggest you use SQL Server Integration Services, which comes with SQL Server 2008 or 2005 (Or Data Transformation Services if your stuck with 2000).

Unfortunately it doesn't come with the free "Express" version of SQL Server, however SQL Server Developer edition can be had for < $100 which has the full SQL Server Standard functionality and would suit your needs.

SSIS is a big topic and I'm not going to go over all of the bells and whistles here but basically you:

• Create a new SSIS project using BIDS (Business Intelligence Development Studio, a modified Visual Studio that comes with SSIS)
• Drag a new Data Flow Task onto the Control Flow surface, then click the data flow tab.
• Drag an "XML source" from toolbox into data flow panel, and then configure the XSD and XML file locations.
• Drag an ADO.NET data destination from the toolbox onto the dataflow and connect one of the the outputs from the XML source to the input of the ADO.NET destination. If you want to create a new table based on the data output from the xml schema as opposed to using an existing one click on "New" when specifying the Connection Manager Settings in the ADO.NET Destination and it generate and execute the appropriate create table statement. Repeat this for any other outputs from the XML source (there will be one for each logical flat table generated from the schema).

You will most probably need to use other data transformation objects first to transform the data before it loaded into SQL server, but that is the general gist of it. If you need to run the process for a large amount of XML files you could put the task in a control loop and use a variable to set the XML file location.

The MS Documentation on using an XML source in SSIS is here: http://msdn.microsoft.com/en-us/library/ms140277(v=SQL.100).aspx

Automatically Generating SQL Schema from XML

You could use XSD. No, I'm serious. Go to a command prompt and type xsd and press Enter.

Here's what you will see (truncated):

I:\>xsd
Microsoft (R) Xml Schemas/DataTypes support utility
[Microsoft (R) .NET Framework, Version 1.0.3705.0]
Copyright (C) Microsoft Corporation 1998-2001. All rights reserved.

xsd.exe -
    Utility to generate schema or class files from given source.

xsd.exe <schema>.xsd /classes|dataset [/e:] [/l:] [/n:] [/o:] [/uri:]
xsd.exe <assembly>.dll|.exe [/outputdir:] [/type: [...]]
xsd.exe <instance>.xml [/outputdir:]
xsd.exe <schema>.xdr [/outputdir:]

Just follow the instructions.

Updating XML column schema with new schema

A schema collection should permit you to have different versions of the XML data without having to ignore validation.

Have a look at XML Schema Collections to start with.

In your T-SQL code, you'd have something like this (this code is sourced from Bob Beauchemin of SQLskills.com):

-- Load XML schema from file
DECLARE @x XML
SET @x = (
SELECT * FROM OPENROWSET(
   BULK 'C:\invoice.xsd',
           SINGLE_BLOB
   ) AS x
)

-- And use it to create an XML schema collection
CREATE XML SCHEMA COLLECTION InvoiceType AS @x

Now you create a table which maps the column to the schema collection:

CREATE TABLE invoice_docs (
   invoiceid INTEGER PRIMARY KEY IDENTITY,
   invoice   XML(document InvoiceType)
)

Now when your schema changes, you modify the schema collection by adding in the new version of the schema:

DECLARE @x XML
SET @x = (
SELECT * FROM OPENROWSET(
   BULK 'C:\invoice_v2.xsd',
           SINGLE_BLOB
   ) AS x
)

-- And use it to create an XML schema collection
-- Allow V1 and V2 invoices
ALTER XML SCHEMA COLLECTION InvoiceType ADD @x

The old data already in the table is validated against the old schema, and any new data will validate against the old or new schema.

If you want to validate only against the newer schema, you'll have to add an additional constraint to the column.

Is there a way to specify multiple SQL Server 2008 Schemas for Liquibase's 'generateChangeLog' command?

No, unfortunately generateChangeLog supports only one schema at a time currently.

---

Related Topics