Standard database neutral XSD to describe a relational database schema.

Question

Hi All,

Is anyone aware of a vendor neutral XSD to describe a relational database schema? Our system needs to grab information about the structure of a database:

• Tables
• Columns and types
• Primary and Foreign Keys Constraints
• Indexes
• etc

in a vendor independent manner and store it in an XML file for later processing.

Before we do what we typically do and roll our own. I wanted to do some research and see if there was an existing XSD that people are standardizing on for what I assume is not an uncommon requirement for modeling tools and such. I did not find anything on Google that was not database vendor specific. If you know of an existing public standard I would very much appreciate a link.

Thanks in advance,

Terence

Answer 1

This isn't exactly what you're looking for, but the PostgresSQL Wiki has an interesting section on XML exports, that describes how they are supporting SQL and XML together. It displays a section on how a table would be exported as as XML and the XSD that would support it, which looks rather generic. It could serve as a model for you to create your own.

The Wiki talks about reference to a ISO/IEC 9075-14:2006 standard, that appears to be adopted by a few big vendors as a baseline. I quick browse on the ISO site says that :2006 was updated to 2008. I'm sure you can find a covered spec of this that you don't have to pay for to download.

The article also points to a SQL/XML standard definition that is a bit outdated, but could serve your needs if you're looking for some basics.

Answer 2

Interesting problem - I am not aware of any standard or tool to achieve this.

You would almost have to have some kind of a "neutralized" version with adapters for each individual database system you want to target - even just to map all the various data types (VARCHAR and NVARCHAR in SQL Server, VARCHAR2 in Oracle and so on).

You might just use the types defined in the SQL:2003 standard - but even then you'd probably still have to have some kind of a vendor-specific mapping / adaption of sorts. Not to mention some kind of support for vendor-specific implementation details (like IDENTITY columns in SQL Server vs. SEQUENCE in Oracle and others).

Very interesting question! I hope others will be able to shed more light on the issue and maybe recommend an existing tool.

If not, and you decide to roll your own - consider making it open-source on CodePlex or Google Code! I'm sure a lot of folks would be most interested!

MArc

Answer 3

The UML Information Management Metamodel (IMM) Specification from OMG may worth a try.