XML to DDL imports, synchronizes database schemata

The Freshmeat.org project known as xml2ddl provides a set of Python programs under the GNU General Public License, or GPL. Given a working Python runtime environment, this set of tools works on many operating systems, including most Windows, Linux, and UNIX platforms. It also works with the following database engines: PostgreSQL, MySQL, Oracle, and Firebird.

Basically, xml2ddl permits users to convert an XML representation of a database into a corresponding set of SQL or DDL statements. According to its creator and custodian, Scott Kirkwood, "XML to DDL strives to be database independent so that the same XML can be used for a variety of databases. This is great for quickly testing out a variety of databases for performance, for example" [quotation comes from the index.html file included in /doc subdirectory within the project ZIP file].

To get the process started, xml2ddl permits users to point to a schema, designate a target database, and produce the requisite DDL or SQL statements necessary to instantiate that database. Given this simple sample XML schema definition in a file named schema1.xml:

And an invocation to output the information for PostgresSQL using the following command line syntax: xml2ddl --dbms postgres schema1.xml, the following output results:

Tailored outputs of the same kind for the other target databases may be generated by substituting firebird, oracle, or mysql instead.

The xml2ddl programs can also examine the differences betwe

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT XML and XML schema SOA pattern of the week (#7): policy centralization Try XML-based Extensible Business Reporting Language (XBRL) for accounting reports What's new at the W3C Ganymede: Modeling tools target SOA, UML Data services mashups emerge for SOA Making sense of data services mashups XML turns 10 SOA helps save 100-year-old business Oracle maps heterogeneous data services strategy for SOA Handling XML with Ajax XML acceleration, transport and messaging Progress/Actional SOA diagnostic tool builds on Mindreef purchase InterSystems Ensemble environment adds binary SOAP messaging UML modeler includes BPMN support What's new at the W3C SOA and BPM speed messages for industrial distributor Verizon uses BPEL app to cut down on code, check for fraud, and go green Data services mashups emerge for SOA Layer 7 adds SPARC Oracle maps heterogeneous data services strategy for SOA Cisco builds app delivery network XML security Verizon uses BPEL app to cut down on code, check for fraud, and go green Layer 7 adds SPARC Oracle maps heterogeneous data services strategy for SOA Partnership aims at governance for SOA and Web 2.0 SOA, Web services create software security challenges Efficient XML Interchange tackles data verbosity The case against WS-Security Layer 7 offers SOA 'virtual soft-appliance' XQuery 1.0: A long time coming, now what? Forum boosts Web services credit security

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary class diagram  (SearchSOA.com) Fast Infoset (FI)  (SearchSOA.com) GeoRSS  (SearchSOA.com) Keyhole Markup Language  (SearchSOA.com) RELAX NG  (SearchSOA.com) state diagram  (SearchSOA.com) Universal Business Language  (SearchSOA.com) Vector Markup Language  (SearchSOA.com) XML infoset  (SearchSOA.com) XML pipeline  (SearchSOA.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

en two different versions of an XML schema and generate the DDL or SQL statements necessary to push those same changes into the target database as well. This requires two related schemas (let's call the second one schema2.xml and recycle schema1.xml) and uses the following syntax: diffxml2ddl --dbms postgres schema1.xml schema2.xml.

If schema2.xml looks as follows:

Then the following DDL output is produced for PostgresSQL:

A complete list of examples is available at the project Web site. What's nice about these tools is that they make it easy to define and manage databases using XML and related structured editing tools, and make short work of translating your structured XML efforts into the proper metalanguage for database implementation and use.

This one's definitely worth checking out, and makes a really nifty data integration and management tool for those lucky enough to be using one of the supported target databases.

[Note: all cited examples here are lifted directly from the xml2ddl help file, as cited earlier in this tip.]

About the author

Ed Tittel is a full-time writer and trainer whose interests include XML and development topics, along with IT Certification and information security topics. Among his many XML projects are XML For Dummies, 4th edition, (Wylie, 2005) and the Shaum's Easy Outline of XML (McGraw-Hill, 2004). E-mail Ed at etittel@techtarget.com with comments, questions, or suggested topics or tools for review.