SQL Database Integration in XMLSpy
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.
Powerful relational database integration in XMLSpy® 2017 helps reduce interoperability challenges by allowing you to easily query SQL databases and convert back and forth between databases and XML files.
The XMLSpy Database Quick-connect wizard makes it easy to enable connectivity to a SQL database. As you're working, the Database Query Window lets you query database data with full SQL editing and intelligent SQL auto-complete capabilities, so you can move easily between looking at database data and your XMLSpy project. 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).
XMLSpy also provides advanced functionality for converting between XML and relational database structures and easily importing / exporting XML and relational data.
XML & SQL Database Integration:
- Database quick-connect wizard
- Database Query window with SQL editor
- Importing SQL database data to XML
- Creating XML Schemas based on SQL database structures
- Exporting XML to SQL databases
- Creating a database schema from an XML Schema
- Support for XML-enabled databases
Importing Database Data to XML
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.
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. The resulting XML Schema will properly reflect the 1:n relationships expressed in the relational database and contain the corresponding identity constraints.
Exporting XML 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. Once you select which database type you wish to append or create with your data, the data is instantly converted and stored in your database.
You may also want to define a database schema with the same rules as an established XML Schema, and XMLSpy makes this easy, too. Any identity constraints included in the XML Schema will automatically transfer to the database structure. Alternatively, you can define relationships between elements manually. 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 lightning fast, saving hours of manual work.
Supported SQL Databases
XMLSpy interacts with the most popular relational databases in their native interface language, including:
- Microsoft® SQL Server® 2005, 2008, 2012, 2014, 2016
- PostgreSQL 8, 9.0.10, 9.1.6, 9.2.1, 9.4
- Oracle® 9i, 10g, 11g, 12c
- MySQL® 5, 5.1, 5.5, 5.6
- IBM DB2® 8, 9.1, 9.5, 9.7, 10.1, 10.5
- IBM DB2 for iSeries® v6.1, 7.1
- Informix® 11.70
- Sybase® ASE 15
- Microsoft Access™ 2003, 2007, 2010, 2013
- SQLite 3.x
- Firebird 2.5
- Progress OpenEdge 11.6
XML-enabled Database Support
All the powerful SQL database features described above are also available for working with XML-enabled databases: