Mapping XML data to / from databases generically
|
| Previous Top Next |
Mapping XML data to / from databases generically |
MapForce supports the mapping of XML data to / from several databases, including MS Access and IBM DB2 version 9. This section describes the mapping xml2access.mfd file available in the ...\Tutorial folder.
XML documents can be mapped to/from all string, varchar and memo fields of sufficient length. Please note that the character encoding of such documents is always that of the underlying string field in the particular database. If the field does not store text as Unicode, some characters cannot be represented. Full support for all XML encodings is only possible in native IBM DB2 version 9 XML fields.
For the XML nodes to appear in the database component, which allows mapping to and from the XML items, a schema must be assigned. Once this is done the mapping process proceeds as usual.
MapForce currently supports the assigning of one XML Schema per database column, and the selection of a single "root" element of that schema.
Open the mapping file xml2access.mfd file in the ...\Tutorial folder.
What this mapping example does:
| • | Updates the XML data in the target database in the Comment column, if: |
| • | The Number content of the PhoneExt and Number fields are identical. |
To insert the data source component:
| 1. | Click the Insert XML Schema/File icon  , and select the MFCompany.xsd schema. |
| 2. | Click Browse.. when the prompt for a sample XML file appears, and select xml2access.xml |
To insert the database target and map data to it:
| 1. | Click the Insert Database icon  in the icon bar, click MS Access (ADO), then click Next. |
| 2. | Click the Browse button and select the xml2access.mdb file in the ...\Tutorial folder, then click Next. |
| 3. | Click the articles_with_comments table check box then click OK. |
To assign an XML schema to a database:
| 1. | Right click the Comment item/column in the database target and select Assign XML Schema to Field... |
| 3. | Click the File radio button if necessary, select xml2access.xsd and click OK. |
| 4. | Expand the Comment item to be able to create connectors to the respective items. |
The XML schema node tree containing all mappable items appears below the Comment item/column. You are now ready to map XML data to the database.
| 5. | Create the mapping connectors as seen in the top screenshot. |
To define the table actions and map the data:
| 1. | Right click the articles_with_comments table item and select Database Table actions. |
| 2. | Click the Insert All combo box and change it to Update if... |
| 3. | Click the combo box next to Number and select equal, then click OK. |
| 4. | Click the Output tab, then the Word Wrap icon  to see more of the SQL script. |
| 5. | Click the "Run SQL-script" icon  to insert the XML data into the database. |
|