Standard database neutral XSD to describe a relational database schema

Question

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

Filefish

You're looking to describe the schema, right, and not instances of the schema? Not data conforming to the schema? – Clayborne

Why don't you just store the SQL necessary to build the database? Am I missing something or are you? – Sport

The tricky part is that while the table structure itself may be rather simple, the XSD generated by a specific vendor will most likely have its own namespace extensions to specifically comply with their product. Finding something more generic without having to code it himself if what I believe is the issue. – Yarak

John - Yes I am looking to describe the schema not data. Pesto - Dille-O's answer to your comment is exactly right. Dille-O - Thanks for the clarification. – Filefish

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.

Yarak

I took a look and the standard costs a few hundred bucks so I am just going to leverage what the Postgres guys guys done. Thank you and Marc for your thoughts. – Filefish

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

Jewish

If we have to roll our own and it doesn't stink :-) We will definitely look at making it open source. Cheers Terence – Filefish

Answer 3

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

Ocarina