ProductsXMLSpyXML / Database IntegrationRelational Database Integration

The XMLSpy® 2012 relational database integration functionality overcomes interoperability challenges, allowing you to easily query database and convert back and forth between databases and XML files.

While most critical enterprise data is stored in relational databases today, XML has become the technology of choice for data exchange and content management. As a result, the importance of working with XML data in harmony with relational databases is paramount.

Relational Database Integration

To help software developers work with XML in conjunction with relational databases, XMLSpy interacts with the most popular relational databases in their native interface language, including:

• Microsoft® SQL Server® 2000, 2005, 2008
• IBM DB2® 8, 9
• IBM DB2 for iSeries® v5.4, 6.1
• Oracle® 9i, 10g, 11g
• Sybase® 12
• MySQL® 4, 5
• PostgreSQL 8
• Microsoft Access™ 2003, 2007

XMLSpy allows you to connect to a relational database, generate an XML Schema based on a relational database, import and export data based on database structures, and generate relational database structures from XML Schemas with ease. The powerful Database Query window allows you to perform queries against the database, edit the data, and even commit your changes to the database (commit currently only enabled for IBM DB2).

 Click here to download a fully functional, free 30-day trial of Altova XMLSpy!

Connecting to a database

XMLSpy® 2012 includes a database quick connect wizard that allows you to easily enable database connectivity before completing the database-related tasks described below.

Querying a database

XMLSpy® 2012 includes the Database Query window for querying and editing database data. When you connect to a database, the Database Query window displays database tables as a hierarchical tree in the browser pane. The SQL editor tab allows you to display, edit, and execute SQL statements to query the database, either by opening existing SQL files or creating SQL scripts from scratch using drag-and-drop and auto-complete functionality.

Once you have executed a database query, you can edit the database data directly in the results window, review changed fields (highlighted in pink), and commit the changes back to the database. Or, if the database data is XML, you can edit it in XMLSpy’s text or grid view, with full validation and entry helper support, and save it back to the database (currently enabled for IBM DB2 9 only).

The Database Query window with direct database editing support makes working with database data in XMLSpy easier and more seamless than ever.

Importing database data

There are two options for importing data from a database into an XML document. The first option is great for simple XML projects that don’t require a schema or are based on a DTD. With this option you select a table and pull in raw data in its basic tabular format. The second option works in conjunction with the ‘Create XML Schema from DB Structure’ function and allows you to import database data maintaining all the relationships and dependencies defined in the content model.

Although they produce different results, both database import methods operate in a similar fashion. You can choose representations for date and number formats, select import defaults, and decide how to handle primary/foreign keys before executing the actual import process. Data is converted instantly when you click the Import button.

Creating an XML Schema from a database

Creating an XML Schema from a database is just as easy and flexible as importing database data. After specifying the source database type and filename, you can select the tables, views and system tables upon which to base the schema, and pick your schema options (just as in the screenshot above). The resulting XML Schema will properly reflect the 1:n relationships expressed in the relational database and contain the corresponding identity constraints.

Exporting XML data to a database

When you need to export XML data into a database, the Export to Database dialog box allows you to specify where to start, how to handle export fields, and which elements to include. Then just select which database type you wish to append or create with your data, and the data is instantly converted and stored in your database.

Creating a database schema from an XML Schema

For times when you want to define a database with the same rules as an established XML Schema, simply select your database type from the comprehensive list, and then open a database to modify, or create a new one from within XMLSpy® 2012. Any identity constraints included in the schema will automatically transfer to the database structure. Alternatively, you can define relationships between elements easily. You can preview the structure and even edit and save the auto-generated SQL statement associated with your conversion. As with all of the database import/export capabilities, the conversion process is as fast as lightning. When you click the Export button your new database structure is ready in a flash.

XMLSpy® 2012 supports the SQL/XML standard, native XML Schema extensions for popular databases, and numerous other options.

XML-enabled databases - specialized support

All the powerful relational database features described above are also available for working with the XML-enabled databases listed below. Visit the links to learn about other specialized features available for each database:

• IBM DB2 pureXML
• Oracle
• SQL Server

Find out why XMLSpy is the ideal tool for database developers working with XML – Download a free 30-day trial of XMLSpy.