How to Convert a Database to an XML Schema

Written by ian low
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Convert a Database to an XML Schema
An XML Schema can capture the structure of a database. (crystalline structure image by Adrian Hillman from Fotolia.com)

A database can be converted to an XML Schema using Altova XMLSpy 2010. "Data_shape.mdb" is an example Microsoft Access database that comes with XMLSpy 2010. You can use the "Create XML Schema from DB Structure" facility in XMLSpy 2010 to convert this database to an XML Schema. This example assumes that you have XMLSpy 2010 installed on your personal computer.

Skill level:
Moderate

Other People Are Reading

Things you need

  • Altova XMLSpy 2010

Show MoreHide

Instructions

  1. 1

    Open XMLSpy 2010. From the top menu, select "Convert" and "XML Schema from DB Structure"

  2. 2

    When the "Connection Wizard" dialogue appears, select the "Microsoft Access (ODBC)" option, then click on Next.

  3. 3

    When the "Connect to MS Access" dialogue appears, click on Browse, and use the "Open" dialogue to navigate to the XMLSpy 2010 Tutorial folder. This is normally in "C:\ProgramData\Altova\XMLSpy2010\Examples\Tutorial". Then double-click on "Data-shape.mdb". Click on Connect.

  4. 4

    Make sure that the Table radio button is selected when the "Create XML Schema from DB Structure" dialogue shows. Then click on the top item "Data_shape1" to select the entire database. Then click on the System Tables item to deselect the System Tables, so that only the schema or "definitions" of the User Tables "Address," "Company," "Division" and "Person" are converted to XML Schema.

  5. 5

    Click on Import. Wait a few moments for the import to finish. The new XML Schema document will appear in the active document in the main window panel in the middle of the XMLSpy IDE (Integrated Development Environment). It will appear in the Schema Overview (tabular) format listing the four tables as four rows. Click on the "Content Model View" icon to the left of the Address row. This will display a diagrammatic representation of the Address table and its fields.

  6. 6

    Click on the "Text" tab in the Main Window to view the XML Schema that has been converted from the database. Then select "File" and "Save As..." to save the XML Schema as "Data_shape1.xsd."

Tips and warnings

  • Once you have saved the XML Schema of the database definition, you can convert the data in the tables into XML files for each table, by selecting "Convert|Import Database Data..." from the top menu.
  • The "Import Database data" dialogue will appear. Select the "Convert database data into XML" option to export the database data into XML data. A separate XML data file will be created for each table.
  • These files will be stand-alone data. They will not have any reference to any xml schema, so they have will not have any built-in data validation.
  • If you need to add validation for the purposes of further processing, you will need to make alterations to each file as follows:
  • * Select the required data file by clicking on its tab to make it the active document
  • * Select "DTD/Schema|Assign Schema" from the top menu. Click "OK" in the next message box. Then in the following dialogue box, click on "Browse" to select the "Data_shape1.xsd" xml schema that was saved in Step 6. Then click "OK." XMLSpy will then insert the reference tag to "Data_shape1.xsd" near the top of the exported XML file.
  • * Delete the "" and "" tags
  • * Rename the Import root begin and end tags to the same as the table name. For example, in the case of the Address table, "" should be changed to "".
  • * Delete the name and query attributes in the root element, as these are not schema-defined attributes.
  • When you now press F8, the xml data exported from the Address table will now be data-valid in accordance with "Data_shape1.xsd".

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.