Querying and mapping XML data in IBM DB2
|
| Previous Top Next |
Querying and mapping XML data in IBM DB2 |
MapForce allows you to query XML data in IBM DB2 databases using the SQL WHERE component and map the record set data to other components. Please see SQL WHERE Component / condition for more information on how to insert and use the SQL WHERE component. This section discusses how to query, and map, XML data from an IBM DB2 database.
Please note that you can also query databases directly using the Database Query tab, please see Querying databases directly - Database Query tab for more information.
Having inserted the DB2 database and assigned the XML schema to the CONTACTINFO item:
| 1. | Click the SQL WHERE icon  in the icon bar to insert it. |
| 2. | Connect the CONTACTINFO item, of the database source, to the table item of the component. |
| 3. | Connect the result item to an item in the target component e.g. Office. |
This updates the name of the SQL WHERE component to CONTACTINFO.
| 4. | Double click the CONTACTINFO component to create the query. |
| 5. | Enter the SQL/XML WHERE query e.g. xmlexists('$c/Client/Address[zip>"95100"]' passing USER.CLIENTS.CONTACTINFO AS "c") |
Note that the first part of the Select statement, SELECT (fields) FROM USER CLIENTS WHERE, is automatically generated for you when you connect the input and output connectors to the database and target component.
This query outputs those records where the zip code in the XML file is greater than 95100.
The xmlexists function allows you to navigate an XML document using an XPath expression, e.g. '$c/Client/Address[zip>"95100"]', and test a condition. For more information on SQL/XML functions please see the DB2 Information Centre web page.
|