Example: Extracting Data from IBM DB2 XML Type Columns
This example illustrates how to extract data from IBM DB2 database columns of XML type and write it to a target CSV file. It also illustrates how to use XQuery statements embedded into SQL in order to retrieve XML content conditionally. The example requires access to an IBM DB2 database where you have permission to create and populate tables.
First, let's prepare the database so that it actually contains XML data. This can be done either in a database administration tool specific to your database, or directly in MapForce. To do this directly in MapForce, follow the steps below:
1.Create a new mapping and click the DBQuery tab.
2.Click Quick Connect ( 
) and follow the wizard steps to create a new database connection (see also Database Connection Examples).
3.Paste the following text into the SQL Editor. This SQL query creates a database table called ARTICLES and populates it with data.
-- Create the table |
4.Click the Execute ( 
) button. The query execution result is displayed in the Query Results window. If the query is executed successfully, four rows are added to the newly created table.
Next, we will create a mapping which retrieves XML data from the ARTICLES table created above conditionally. The goal is to retrieve from the ARTICLES column only articles with a price greater than 100.
Step 1: Add the database
1.Click the Mapping tab to switch back to the mapping pane.
2.On the Insert menu, click Database, and follow the wizard steps to connect to the database.
3.When prompted to select the database objects, select the ARTICLES table created previously.
Step 2: Assign the schema to the XML type field
1.Right-click the ARTICLE item of the component, and select Assign XML Schema to field from the context menu.
2.Select File, and browse for the following schema: <Documents>\Altova\MapForce2024\MapForceExamples\Tutorial\DB2xsd.xsd.
Step 3: Add the SQL WHERE/ORDER component
1.On the Insert menu, click SQL WHERE/ORDER.
2.Connect the ARTICLE XML type column to the input of the SQL WHERE/ORDER.
3.In the SQL-WHERE/ORDER Properties dialog box, enter the following text:
XMLEXISTS('\$a/Article[SinglePrice>100]' PASSING ARTICLE as "a") |
The text above represents the "WHERE" part of the SQL query. At mapping runtime, it will be combined with the "SELECT" part displayed on the dialog box. This statement uses the XMLEXISTS function and syntax specific to IBM DB2 databases.
Step 4: Add the target CSV file
1.On the Insert menu, click Text File.
2.When prompted, select Use simple processing for standard CSV... , and click Continue.
3.Click Append Field three times to add three fields which will store the article number, name, and price, respectively. Leave all other settings as is.
4.Draw the mapping connections as shown below.
You can now preview the mapping result, by clicking the Output pane. As expected, only articles with price greater than 100 are shown in the output.
