Use WebSphere Federation Server Version 9.1 to integrate XML data

The new pureXML™ support available in WebSphere® Federation Server Version 9.1 allows you to integrate both local and remote XML store data. You can also use the new SQL/XML and XQuery languages to query XML data in your new applications. Learn how this winning combination can work for you!

Aakash Bordia (bordia@us.ibm.com), Staff Software Engineer, IBM

Aakash Bordia photoAakash Bordia is a Staff Software Engineer in Silicon Valley Laboratory in San Jose, California. After graduating from the University of Illinois in January 2000, he joined the IBM DB2 Warehouse Manager development team and contributed to SQL based warehouse transformations. In 2002, he moved to the WebSphere Information Integrator Federation team where he has been a compiler developer and more recently bugs the development team by crashing their code and filing defects (a.k.a. QA Engineer).



Eileen Lin (etlin@us.ibm.com), Senior Technical Staff Member, IBM Silicon Valley Lab

Author photoDr. Eileen Lin is a Senior Technical Staff Member in Silicon Valley Laboratory in San Jose, California. She is one of the original members responsible for the success of DataJoiner, a federated database product that is the predecessor of the federation technology in DB2. Currently, she is the architect for the data federation technology in WebSphere Information Integrator. Dr. Lin has many patents covering areas such as federation technology, query optimization and parallel query processing.



Ioana Ursu (ursu@us.ibm.com), Advisory Software Engineer, IBM Silicon Valley Lab

Author photoIoana Ursu is an Advisory Software Engineer in Silicon Valley Laboratory in San Jose, California. She joined IBM Almaden in 1998 working for the Garlic research project. Since 1999, she has worked in many areas of federated query compilation, including query semantics, query rewrite, pushdown analysis and query optimization. She currently works in the WebSphere Information Integrator Federated Query Compiler team, focusing on general federated query processing.



Sebastian Hahnel, Software Engineering Intern, IBM

Sebastian Hahnel photoSebastian Hahnel is an intern at the IBM Silicon Valley Lab in San Jose, California. He joined the WebSphere Information Integrator Federation team in April 2006 after completing his studies of computer sciences with honors at the University of Applied Sciences of Leipzig, Germany.



08 March 2007

Also available in

Introduction

IBM WebSphere Federation Server provides you with real-time, virtualized access to disparate data sources. As a result, you benefit from delivering new projects with a much shorter time to market, extending your warehouse with ad-hoc queries, and producing a unified view for your enterprise data.

Thanks to the pureXML support in DB2® 9, WebSphere Federation Server Version 9.1 provides you the ability to integrate relational and XML data within a single database. Federation customers can now integrate the new XML data in the native XML storage into the federated database. Though there is no direct support for the remote XML data type yet, federation customers can also integrate remote XML data using XML views.

This article demonstrates how to integrate local and remote XML store data with WebSphere Federation Server Version 9.1. Using a bookstore as an example, let's go over how you can use the newly enhanced SQL/XML and the new XQuery languages to manipulate your local XML store data. Then, we'll discuss the configuration steps necessary to integrate remote XML store data as well as any relevant considerations. Extending the bookstore example to a distributed bookstore, we'll illustrate how to apply the configuration to various data sources. We provide some query examples to walk you through how to use the SQL/XML and XQuery languages to query such a system. The conclusion covers the enhancements we are currently working on to make such a feature much easier to use in the future.

Integrating local XML store data

DB2 supports SQL and XQuery as primary query languages. Both operate on their respective data models and can be used independently. However, database applications can benefit immensely from the integration of the two languages that DB2 supports. Since many applications deal with existing relational data and XML simultaneously, queries need to combine and correlate these two types of data. To support such integration, DB2 9 provides the following functions:

  • SQL/XML functions and predicates that enable applications to access XML data in SQL
  • Functions such as db2-fn:sqlquery that enable applications to access relational data in XQuery

Since DB2 can provide transparent access to heterogeneous relational data using federation, the relational tables specified in SQL/XML and XQuery can be nicknames.

In this section, we will discuss querying local XML columns in combination with nicknames, using SQL/XML and XQuery. During this discussion, we'll refer to the scenario described in Figure 1:

Figure 1. The bookstore scenario

Figure 2 illustrates the configuration of this scenario:

Figure 2. Configuration of the bookstore scenario

Querying local XML data and nicknames with SQL

DB2 supports retrieval and querying of XML data in an SQL statement by providing new SQL/XML functions and predicates like XMLQUERY, XMLEXISTS, and XMLTABLE. Let's start with this simple example. The following query retrieves the information about the book, its quantity in inventory, and the city for the warehouse that lists the book with its ISBN ending in "69":

SELECT b.xmlinfo, i.quantity, w.city  
FROM bookstore b, inventory i, warehouse w  
WHERE b.isbn = i.isbn 
  AND i.warehouse_id = w.warehouse_id 
AND b.isbn LIKE '%69'

In the example above, we are joining two nicknames and this local table containing an XML column.

A sample output (shown as prettyPrint) might look like this:

<book isbn="0201633469" year="1993" category="Computers">
  <title>TCP/IP Illustrated</title>
  <author><last>Stevens</last><first>W. Richard</first></author>
  <publisher>Addison-Wesley Professional</publisher>
  <price>65.00</price>
  </book>,
3,
'San Francisco'

The next example uses the XMLTABLE function to return a result table from a local XML column, and then uses the result table to insert data into the nickname. The purpose is to store the book authors in a separate table by extracting the data from the XML documents in the bookstore table.

INSERT INTO author
  SELECT xb.*
  FROM bookstore b, XMLTABLE(
    '$doc/book' PASSING b.xmlinfo AS "doc" COLUMNS
      "ISBN" CHAR(10) PATH '@isbn',
      "LASTNAME" VARCHAR(30) PATH 'author/last',
      "FIRSTNAME" VARCHAR(30) PATH 'author/first'
    ) AS xb;

The next example returns full bookstore documents with XMLEXISTS for additional filtering. Only those rows in which the book is cheaper than $65.50 and a warehouse has a copy are returned. The passing by clause establishes the binding between the SQL and the XQuery context.

SELECT b.isbn, i.warehouse_id, i.quantity
FROM bookstore b, inventory i
WHERE i.isbn = b.isbn 
  AND i.quantity > 0
  AND XMLEXISTS('$doc/book[price < 65.50]' 
        PASSING BY REF b.xmlinfo AS "doc")

The next example shows the XMLQUERY function in the WHERE clause so that an extracted value from a local XML column can be used to join with a column of a nickname. Here, for each book that contains the keyword "TCP/IP" in its title, we are looking for the total number of books its author has published:

SELECT DISTINCT count(*) as NUMBER_OF_BOOKS, a.firstname, a.lastname
FROM author a, bookstore b
WHERE XMLSERIALIZE(CONTENT XMLQUERY('$doc/book/author/first/text()'
    PASSING BY REF b.xmlinfo AS "doc"
    RETURNING SEQUENCE) AS CHAR(50)) = a.firstname
  AND XMLSERIALIZE(CONTENT XMLQUERY('$doc/book/author/last/text()'
    PASSING BY REF b.xmlinfo AS "doc"
    RETURNING SEQUENCE) AS CHAR(50)) = a.lastname
  AND XMLSERIALIZE(CONTENT XMLQUERY('$doc/book/title/text()'
    PASSING BY REF b.xmlinfo AS "doc"
    RETURNING SEQUENCE) AS CHAR(50)) like '%TCP/IP%'
GROUP BY a.firstname, a.lastname, b.isbn

Querying local XML data and nicknames with XQuery

You can use XQuery in DB2 9 to access and retrieve relational data. This is facilitated by SQL/XML constructor functions that transform relational data into the XML format and produce a single column of type XML which can serve as input to an XQuery. Users can leverage all of their existing relational data to qualify XML documents for XQuery processing. The db2-fn:sqlquery function can be used to not only reduce the input to an XQuery, but also to extend it.

In the following example, an XQuery constructs a result document that contains the book, inventory and information for a specific book. The book information is an XML document retrieved from the XML column XMLINFO. The inventory information comes from a join of two relational nicknames. The SQL/XML statement constructs an XML element warehouse with five child elements whose values are taken from the result of the join of the two nicknames, WAREHOUSE and INVENTORY, such as the warehouse ID, street, city, state, and quantity. The result is an inventory report for a book, annotated with information about the warehouses.

XQUERY 
  let $b := db2-fn:sqlquery("
    SELECT xmlinfo
    FROM bookstore
    WHERE isbn='0201633469'")
  let $w := db2-fn:sqlquery("
    SELECT XMLELEMENT(NAME warehouse, XMLFOREST(
      w.warehouse_id, w.street, w.city, w.state, i.quantity))
    FROM warehouse w, inventory i
    WHERE w.warehouse_id = i.warehouse_id
    AND i.isbn = '0201633469'")  
  return 
    <report>   
     {$b}
     <stock>{$w}</stock>
    </report>

Sample output (shown as prettyPrint) might look like:

<report>
  <book isbn="0201633469" year="1993" category="Computers">
    <title>TCP/IP Illustrated</title>
    <author><last>Stevens</last><first>W. Richard</first></author>
    <publisher>Addison-Wesley Professional</publisher>
    <price>65.00</price>
  </book>
  <stock>
    <WAREHOUSE>
      <WAREHOUSE_ID>1</WAREHOUSE_ID>
      <STREET>1234 Market Street</STREET>
      <CITY>San Francisco</CITY>
      <STATE>CA</STATE>
      <QUANTITY>3</QUANTITY>
    </WAREHOUSE>
    <WAREHOUSE>
      <WAREHOUSE_ID>2</WAREHOUSE_ID>
      <STREET>4012 1st Street</STREET>
      <CITY>San Jose</CITY>
      <STATE>CA</STATE>
      <QUANTITY>1</QUANTITY>
    </WAREHOUSE>
  </stock>
</report>

The next example filters the set of input documents to XQuery by joining with a nickname. We query the titles of those books that cost more than $65, listed by the warehouse with an ID equal to 1.

XQUERY 
  for $e in db2-fn:sqlquery("
    SELECT b.xmlinfo 
    FROM bookstore b, inventory i
    WHERE b.isbn = i.isbn 
     AND i.warehouse_id = 1")/book
  where $e/price > 65.00
  return $e/title

Sample output (shown as prettyPrint) might look like:

<title>Advanced Programming in the UNIX environment</title>

These examples highlight the power of integrating XQuery and SQL, essentially that the relational data accessed through a nickname can be used with the native XML store.

Integrating remote XML store data

In WebSphere Federation Server Version 9.1, a nickname cannot be created over a remote table or view that contains an XML-type column. To integrate XML data from various relational databases, you can use views over serialized XML string data as a mechanism for mapping between remote and local XML data.

Figure 3 illustrates the following steps:

  1. Serializing the XML data on the remote database using a view
  2. Creating a nickname over this remote view
  3. Parsing the serialized XML data from the local nickname into a local view on the federated server
Figure 3. Graphical view of how to integrate remote XML data

Let's assume that we have a remote Oracle table with the following definition:

CREATE TABLE bookstore(isbn CHAR(10), xmlinfo XMLType);

Next, the XML data is serialized into the following view at the remote server:

CREATE VIEW bookstore_blob (isbn, xmlinfo) AS
SELECT b.isbn, b.xmlinfo.getBlobVal(nls_charset_id('utf8'))
FROM bookstore b;

XML data has no defined length. Since it is expected to include large documents, the external XML data type will be treated in a similar way to LOB data. However, for smaller documents, you can also specify a result type like VARCHAR that might be more appropriate and result in better performance than Character Large Objects (CLOBs).

After the remote XML data is serialized, you can create a nickname over the remote view at the federated server as follows:

CREATE NICKNAME bookstore_blob FOR server_name.myuser.bookstore_blob;

Next, at the federated server, you need to convert the serialized string back to the XML type. Let's use the XMLPARSE function which converts the serialized string into an XML value as shown in the following statement:

CREATE VIEW vbookstore (isbn, xmlinfo) AS
SELECT isbn, XMLPARSE(DOCUMENT xmlinfo PRESERVE WHITESPACE)
FROM bookstore_blob;

Now, you can manipulate the remote XML data as any local XML data type using XQuery and SQL/XML functions.

Data validation for remote XML data

The DB2 parser only allows valid XML data as input. If the data provided cannot be parsed, an error is returned. For instance, you can also create a nickname over a remote view that extracts part of an XML document or the value of an element. The Oracle view below will return the content of an XML element:

CREATE VIEW bookstore_blob (isbn, xmlinfo) AS
SELECT extract(xmlinfo, '//book').getBlobVal(nls_charset_id('utf8'))
FROM bookstore;

The result is a sequence of nodes returned as an instance of XML type. If XMLPARSE is applied to the output of the nickname created over the remote view above, the DB2 parser might complain about the XML document that contains an invalid XML document structure (SQL16132N). This would be considered a usage error.

One way to resolve the error is to add a root element as part of the view definition. For example, you can define the following view on the Oracle data source:

CREATE VIEW bookstore_blob (isbn, xmlinfo) AS
SELECT '<root>' || 
       extract(xmlinfo,//book').getBlobVal(nls_charset_id('utf8'))|| 
       '</root>' 
FROM bookstore;

Also, at the federated server, you can use schema validation together with the XMLPARSE:

CREATE VIEW vbookstore (isbn, xmlinfo) AS
SELECT isbn, XMLVALIDATE(XMLPARSE(DOCUMENT xmlinfo WHITESPACE)
                          ACCORDING TO XMLSCHEMA ID bookschema)
FROM bookstore_blob;

bookschema is a local DB2 registered schema object, which may represent a remote data source XML schema. You can use the REGISTER XMLSCHEMA command to register the bookstore schema with DB2 as shown in the following example:

REGISTER XMLSCHEMA schema_URI FROM content_URI AS bookschema COMPLETE;

By default, XMLPARSE only checks for well-formed data without schema validation.

The XMLVALIDATE function validates the given XML value against the given XML schema, or the XML schema obtained from the schema specification in the instance document.

Please note the following restrictions:

  • If the XMLVALIDATE is called without any schema specification, either the internal schema specification is used, or the DTD specification is used in its absence. If the schema location or the external DTD cannot be accessed, the parser issues an error message.
  • If XMLVALIDATE is called with a registered schema or specific URI, the validation is performed against that specific schema. If an external schema is specified, it overwrites the internal schema specification. For federated statements, this will be the mechanism that overwrites the document's internal schema specification if the schema information is not accessible by the DB2 server.

Data encoding considerations for remote XML data

When serializing XML data, the target type determines the type of encoding used. An XML output will contain internal encoding information. However, a non-XML output may not include an XML declaration, in which case it is left to the federated server or the DB2 XML parser to assume the encoding scheme.

In general, if XML data is sent and received using an XML data type, there are no codepage conversion concerns. However, when SQL character types are used to hold serialized XML data, the codepage of the character type can introduce code page conversion issues.

For federated statements, the following code page related issues might occur while manipulating serialized remote XML data:

The remote XML data is serialized into a binary data type:

There is no data loss in transit from the remote client to the federated wrapper.

The DB2 XML parser encoding rules will apply. In addition, if the internal encoding exists and it is not a valid IBM encoding schema, the parser will issue a SQL16168N error message. In such cases, our recommendation is to exclude the XML declaration from the remote serialized string. For example, at Oracle, you can use the following remote view definition that will exclude the XML declaration. Oracle adds the XML declaration by default if it is present in the XML document.

CREATE VIEW bookstore_blob (isbn, xmlinfo) AS
SELECT isbn, 
extract(xmlinfo,'/').getBlobVal(nls_charset_id('utf8'))
FROM bookstore;

The remote XML data is serialized into a character data type:

  • The code page of the XML data will be the federated database code page (Unicode). Data loss is possible in transit from the remote client to the federated wrapper. If conversion results in character substitution, a warning might be generated depending on the data source behavior and wrapper implementation.
  • The DB2 XML parser encoding rules will apply. If the internal encoding exists and is an invalid IBM encoding schema, the parser will issue a SQL16168N. In such cases, our recommendation is to exclude the XML declaration if the serialization allows it. For example, at Oracle, you can use the following remote view definition, to exclude the XML declaration:
CREATE VIEW clob_books (isbn, xmlinfo) AS
SELECT isbn, extract(xmlinfo, '/').getClobVal() 
FROM bookstore;

To avoid code page conversion issues, the recommended approach is to use character FOR BIT DATA or BLOB instead of character types. For federated systems, be aware that the internal XML encoding might be different across data sources. In such cases, excluding the XML declaration is recommended if the serialization allows it.

The distributed bookstore scenario

Based on the scenario introduced in the Integrating local XML store data section, consider the entity relationship diagram shown in Figure 4 that describes an online bookstore scenario. Notice that the BOOKSTORE table is located on both the federated server and an Oracle 10g Release 2 database.

Figure 4. The distributed bookstore scenario

Please refer to Appendix A3 to see the creation of the servers, wrappers and user mappings. The completed setup is described in Figure 5:

Figure 5. Configuration of the bookstore scenario

Serializing the remote XML data with remote views

There is one BOOKSTORE table an Oracle 10gR2 Server. The XML data is serialized to a BLOB data type using the following remote view:

CREATE VIEW bookstore_blob (isbn, xmlinfo) AS
SELECT b.isbn, b.xmlinfo.getBlobVal(nls_charset_id('utf8'))
FROM bookstore2 b;

The REVIEW table exists on the SQL Server 2005. Its XML data is serialized into a BLOB using the following remote view:

CREATE VIEW review_blob (review_id, xmlinfo) AS
SELECT review_id, CAST(xmlinfo AS VARBINARY(MAX))
FROM review;

The table CUSTOMER_INFO is stored in a DB2 9 database, and its XML data is serialized into a BLOB column using the following remote view:

CREATE VIEW customer_info_blob (customer_id, xmlinfo) AS
SELECT customer_id, XMLSERIALIZE(xmlinfo AS BLOB INCLUDING XMLDECLARATION)
FROM customer_info;

Creating nicknames and local XML views

The following statements define relational nicknames for our example:

CREATE NICKNAME bookstore_blob FOR "oracle10g2".myuser.bookstore_blob;
CREATE NICKNAME review_blob FOR "mssql".myuser.review_vlob;
CREATE NICKNAME customer_info_blob 
  FOR "db2v91".myuser.customer_info_blob; 
CREATE NICKNAME inventory FOR "db2v91".myuser.inventory;
CREATE NICKNAME warehouse FOR "db2v91".myuser.warehouse;

Since the customer order in the orders.xml XML document contains a list of items, it is represented using two nicknames:

CREATE NICKNAME order (
  order_id CHAR(10) OPTIONS (XPATH '@id'),
  order_date DATE OPTIONS (XPATH '@date'),
  customer_id CHAR(10) OPTIONS (XPATH '@cid'))  
FOR SERVER "xml_server"
OPTIONS (
  FILE_PATH '/my/path/orders.xml', 
  XPATH '/orders/order');

CREATE NICKNAME order_item (
  order_id VARCHAR(10) OPTIONS (XPATH '../@id'),
  isbn CHAR(10) OPTIONS (XPATH '@isbn'))
FOR SERVER "xml_server"
OPTIONS (
  FILE_PATH '/my/path/orders.xml', 
  XPATH '/orders/order/item');

Next, let's create local XML views over the relational nicknames:

CREATE VIEW vbookstore2 (isbn, xmlinfo) AS
SELECT isbn, XMLPARSE(DOCUMENT xmlinfo PRESERVE WHITESPACE)
FROM bookstore_blob;

CREATE VIEW vreview (review_id, xmlinfo) AS
SELECT review_id, XMLPARSE(DOCUMENT xmlinfo PRESERVE WHITESPACE)
FROM review_blob;

CREATE VIEW vcustomer_info (customer_id, xmlinfo) AS
SELECT customer_id, XMLPARSE(DOCUMENT xmlinfo PRESERVE WHITESPACE)
FROM customer_info_blob;

To model a distributed bookstore, define a UNION ALL view over the table bookstore1 and the view vbookstore2:

CREATE VIEW big_bookstore (isbn, xmlinfo) AS
  SELECT isbn, xmlinfo FROM bookstore1
  UNION ALL
    SELECT isbn, xmlinfo FROM vbookstore2;

Querying with SQL/XML and XQuery

Users can issue the supported SQL/XML or XQuery against the local views that we created above, without knowing where the XML data is located.

First, let's find the review for each book using SQL/XML publishing functions and the XMLTABLE function:

SELECT XMLELEMENT(NAME "book",
         XMLFOREST(
           b."title" AS "title", 
           r."review" AS "review"))
FROM big_bookstore vb, vreview vr,
  XMLTABLE('$doc/book' PASSING vb.xmlinfo AS "doc"
    COLUMNS "isbn" CHAR(10) PATH '@isbn',
            "title" VARCHAR(50) PATH 'title') as b,
  XMLTABLE('$doc/reviews/review' PASSING vr.xmlinfo AS "doc"
    COLUMNS "isbn" CHAR(10) PATH '@isbn',
            "review" VARCHAR(70) PATH '.') as r
WHERE b."isbn" = r."isbn";

Sample output (as prettyPrint):

<book>
  <title>Advanced Programming in the UNIX environment</title>
  <review>A clear and detailed discussion of UNIX programming.</review>
</book>
<book>
  <title>TCP/IP Illustrated</title>
  <review>One of the best books on TCP/IP.</review>
</book>

Next, you can use SQL/XML publishing functions together with other XML constructs to provide a list of books sold at various locations. A formatted output is shown below:

<list>
  <state name="CA">
    <city name="San Francisco">
      <book>TCP/IP Illustrated</book>
    </city>
    <city name="San Jose">
      <book>Advanced Programming in the UNIX environment</book>
      <book>TCP/IP Illustrated</book>
      <book>TCP/IP Illustrated</book>
    </city>
  </state>
  <state name="NY">
    <city name="New York">
      <book>TCP/IP Illustrated</book>
    </city>
  </state>
</list>

The following statement generates the output above (as a sequence):

VALUES (XMLELEMENT(NAME "list", (
  SELECT XMLAGG (s.state)
  FROM ( SELECT XMLELEMENT(NAME "state", 
           XMLATTRIBUTES(c.state AS "name"),
           XMLAGG(c.city)) AS state
         FROM ( SELECT ci."state" AS state, 
                  XMLELEMENT(NAME "city", 
                  XMLATTRIBUTES(ci."city" AS "name"),
                  XMLAGG(XMLELEMENT(NAME "book", b."title"))) AS city
                FROM order o, order_item oi, 
                     big_bookstore vb, vcustomer_info vci,
	          XMLTABLE('$p/customer' PASSING vci.xmlinfo AS "p"
		    COLUMNS "id" VARCHAR(10) PATH '@id',
			    "city" VARCHAR(20) PATH 'address/city',
			    "state" VARCHAR(10) PATH 'address/state') AS ci,
	          XMLTABLE('$p/book' PASSING vb.xmlinfo AS "p"
	            COLUMNS "isbn" VARCHAR(10) PATH '@isbn',
	                    "year" VARCHAR(4) PATH '@year',
	                    "title" VARCHAR(100) PATH 'title') AS b
                     WHERE o.order_id = oi.order_id 
                       AND oi.isbn = b."isbn"
                       AND o.customer_id = ci."id"
                       AND o.order_date <= '10/25/2004'
                     GROUP BY ci."city", ci."state") AS c 
         GROUP BY c.state) AS s
  )));

Users can also employ XQuery to retrieve the list of computer books that have the best reviews and are cheaper than the average price:

XQUERY 
  let $a := fn:avg(
    db2-fn:xmlcolumn("BIG_BOOKSTORE.XMLINFO")
    /book[@category = "Computers"]/price/text())
  for $book in db2-fn:xmlcolumn('BIG_BOOKSTORE.XMLINFO')/book
  for $review in db2-fn:xmlcolumn('VREVIEW.XMLINFO')/reviews/review 
where 
  $book/@isbn = $review/@isbn
  and $book/price/text() <= $a 
  and fn:contains($review/text(), "best")
return 
  <book>{$book/title/text()}</book>

Sample output:

<book>TCP/IP Illustrated</book>

This final example demonstrates the use of SQL inside XQuery and how to nest an XQuery construction. The purpose of this query is to obtain the stock of each book for each warehouse and the total stock for each book:

XQUERY
  for $total_order in db2-fn:sqlquery('
    SELECT 
      XMLELEMENT(NAME "total_order",
        XMLFOREST(
          i.isbn AS "isbn",
          sum(i.quantity) AS "total"
        )
      )
    FROM warehouse w, inventory i
    WHERE w.warehouse_id = i.warehouse_id
    GROUP BY i.isbn
  ')
  let $warehouses := db2-fn:sqlquery('
    SELECT
        XMLAGG(
          XMLELEMENT(NAME "warehouse",        
            XMLFOREST(
              i.isbn AS "isbn",
              i.quantity AS "quantity",
              w.city AS "city",
              w.warehouse_id AS "id"
            ) 
          )
        )
    FROM warehouse w, inventory i      
    WHERE w.warehouse_id = i.warehouse_id
  ')[isbn=$total_order/isbn]
  return
    <book isbn="{$total_order/isbn}">
      <stock total="{$total_order/total}">
        {
          for $wh in $warehouses
          return 
            <warehouse id="{$wh/id}">
              <city>{$wh/city/text()}</city>
              <quantity>{$wh/quantity/text()}</quantity>
            </warehouse>
        }
      </stock>
    </book>

For our example, a sample output could be returned in this format:

<book isbn="0201563177">
  <stock total="7">
    <warehouse id="1">
      <city>San Francisco</city>
      <quantity>2</quantity>
    </warehouse>
    <warehouse id="2">
      <city>San Jose</city>
      <quantity>5</quantity>
    </warehouse>
  </stock>
</book>  
<book isbn="0201633469">
  <stock total="4">
    <warehouse id="1">
      <city>San Francisco</city>
      <quantity>3</quantity>
    </warehouse>
    <warehouse id="2">
      <city>San Jose</city>
      <quantity>1</quantity>
    </warehouse>
  </stock>
</book>

Conclusion

This article demonstrated how you can use WebSphere Federation Server Version 9.1 to integrate both local and remote XML store data. Even though you cannot define nicknames to access remote XML store data yet, you've seen how you can use views to accomplish this same task.

Currently, we are working on enabling federated access to remote XML store data. Not only will this work eliminate the extra configuration described in this article, but it can also improve performance as it uses direct access instead of views. Furthermore, this new federated access would allow you to update remote XML data. We are confident that the pureXML support and federation will prove to be a powerful combination for your ever-evolving enterprise.

Appendix: Configuration scripts

A1. Populating the bookstore example

Assuming that the tables were created as described in the Integrating local XML store data section, you can fill them with data. The following lines show the content of the BOOKSTORE table:

('0201633469', 
<book isbn="0201633469" year="1993" category="Computers">
  <title>TCP/IP Illustrated</title>
  <author>
    <last>Stevens</last>
    <first>W. Richard</first>
  </author>
  <publisher>Addison-Wesley Professional</publisher>
  <price>65.00</price>
</book>)
,
('0201563177', 
<book isbn="0201563177" year="1992" category="Computers">
  <title>Advanced Programming in the UNIX environment</title>
  <author>
    <last>Stevens</last>
    <first>W. Richard</first>
  </author>
  <publisher>Addison-Wesley Professional</publisher>
  <price>65.95</price>
</book>)

Let's assume that the following data is stored in the INVENTORY table:

SELECT * FROM inventory;

INVENTORY_ID WAREHOUSE_ID ISBN       QUANTITY
------------ ------------ ---------- -----------
        1001            1 0201633469           3
        1002            1 0201563177           2
        1003            2 0201633469           1
        1004            2 0201563177           5

4 record(s) selected.

The WAREHOUSE table contains the following data:

SELECT * FROM warehouse;

WAREHOUSE_ID STREET             CITY          STATE
------------ ------------------ ------------- -----------------
           2 4012 1st Street    San Jose      CA
           1 1234 Market Street San Francisco CA

2 record(s) selected.

The AUTHOR table is assumed to be empty.

A2. Creating tables for the distributed bookstore scenario

The XML documents representing the BOOKSTORE relation are stored in two tables. The first one is a local BOOKSTORE table, residing on the federated side in a DB2 database server.

CREATE TABLE bookstore1(isbn CHAR(10), xmlinfo XML);

The other table is located in an Oracle 10g Release 2 database. Its definition is shown below:

CREATE TABLE bookstore2(isbn CHAR(10), xmlinfo XMLTYPE);

The tables in the two databases are constrained using the same remote XML schema.

The table REVIEW resides on a MS SQL Server 2005 database and contains the reviews of the books. The table is defined as follows:

CREATE TABLE review(review_id INT, xmlinfo XML);

Table CUSTOMER_INFO is stored in a DB2 9 database and contains customer information. The table definition is presented below:

CREATE TABLE customer_info(customer_id INT, xmlinfo XML);

The tables INVENTORY and WAREHOUSE are non-XML relational tables located in a DB2 9 database:

CREATE TABLE warehouse(warehouse_id INT, street CHAR(50), city CHAR(30), state CHAR(30));
CREATE TABLE inventory(inventory_id INT, warehouse_id INT, isbn CHAR(10), quantity INT);

The online order information resides in an XML documents on the local file system named orders.xml.

A3. Creating wrappers, servers and user mappings for the distributed bookstore scenario

We need to define four wrappers to access four different types of data sources:

CREATE WRAPPER net8;
CREATE WRAPPER drda;
CREATE WRAPPER mssqlodbc3;
CREATE WRAPPER xml_wrapper LIBRARY 'libdb2lsxml.a';

Note: Our scenario has been tested on AIX 5.2.

After the wrapper libraries are registered with the federated system, each data source needs to be registered as a server:

CREATE SERVER "oracle10g2" TYPE ORACLE VERSION 10.2
  WRAPPER net8 OPTIONS (NODE 'MYORA10');
 
CREATE SERVER "db2v91" TYPE db2/cs VERSION 9 
  WRAPPER drda AUTHORIZATION "myuser" PASSWORD "secret"
  OPTIONS (NODE 'MYDB2', DBNAME 'MYDB');

CREATE SERVER "mssql" TYPE MSSQLSERVER VERSION 2005 
  WRAPPER mssqlodbc3 OPTIONS (NODE 'MYMSSQL', DBNAME 'MYDB');

CREATE SERVER "xml_server" WRAPPER xml_wrapper;

Next, you can create a user mapping to register remote authentication information to the data sources if necessary:

CREATE USER MAPPING FOR USER SERVER "oracle10g2"
  OPTIONS (REMOTE_AUTHID 'myuser', REMOTE_PASSWORD 'secret'); 

CREATE USER MAPPING FOR USER SERVER "db2v91"
  OPTIONS (REMOTE_AUTHID 'myuser', REMOTE_PASSWORD 'secret');

CREATE USER MAPPING FOR USER SERVER "mssql"
  OPTIONS (REMOTE_AUTHID 'myuser', REMOTE_PASSWORD 'secret');

A4. Populating the distributed bookstore example

After the remote tables are created as described in the The distributed bookstore scenario section, they can be filled with example data.

The BOOKSTORE1 table at the federated server contains the following data:

% DB2 Federated Server
INSERT INTO bookstore1 VALUES('0201633469', 
'<book isbn="0201633469" year="1993" category="Computers">
  <title>TCP/IP Illustrated</title>
  <author>
    <last>Stevens</last>
    <first>W. Richard</first>
  </author>
  <publisher>Addison-Wesley Professional</publisher>
  <price>65.00</price>
</book>');

The BOOKSTORE2 table at the Oracle 10g Release 2 server is populated using the following command:

% Oracle 10g Release 2
INSERT INTO bookstore2 VALUES('0201563177', XMLTYPE(
'<book isbn="0201563177" year="1992" category="Computers">
  <title>Advanced Programming in the UNIX environment</title>
  <author>
    <last>Stevens</last>
    <first>W. Richard</first>
  </author>
  <publisher>Addison-Wesley Professional</publisher>
  <price>65.95</price>
</book>'));

The table REVIEW, residing on the MS SQL Server 2005, is populated as follows:

INSERT INTO review VALUES
  (1, 
  '<reviews>
    <review id="10001" isbn="155860622X">A very good discussion of 
semi-structured database systems and XML.</review>
    <review id="10002" isbn="0201563177">A clear and detailed discussion 
of UNIX programming.</review>
    <review id="10003" isbn="0201633469">One of the best books on 
TCP/IP.</review>
  </reviews>');

Data is inserted into the CUSTOMER_INFO table stored in the DB2 9 database with the following statements:

INSERT INTO customer_info VALUES(1, XMLPARSE(DOCUMENT
'<customer id="1">
  <name><last>Smith</last><first>Joe</first></name>
  <address>
    <street>6127 Crown Bld</street>
    <city>San Jose</city>
    <state>CA</state>
  </address>
</customer>' PRESERVE WHITESPACE));

 INSERT INTO customer_info VALUES(2, XMLPARSE(DOCUMENT
'<customer id="2">
  <name><last>Miller</last><first>Bob</first></name>
  <address>
    <street>544 Capitol Expw</street>
    <city>San Jose</city><state>CA</state>
  </address>
</customer>' PRESERVE WHITESPACE));

INSERT INTO customer_info VALUES(3, XMLPARSE(DOCUMENT
'<customer id="3">
  <name><last>Meyer</last><first>Ann</first></name>
  <address>
    <street>8400 Main St</street>
    <city>San Francisco</city><state>CA</state>
  </address>
</customer>' PRESERVE WHITESPACE));

INSERT INTO customer_info VALUES(4, XMLPARSE(DOCUMENT
'<customer id="3">
  <name><last>Apple</last><first>Sue</first></name>
  <address>
    <street>2200 Diamond Blvd</street>
    <city>New York</city><state>NY</state>
  </address>
</customer>' PRESERVE WHITESPACE));

The INVENTORY and WAREHOUSE tables on the same database are populated with data as follows:

INSERT INTO warehouse VALUES (1, '1234 Market Street', 'San Francisco', 'CA');
INSERT INTO warehouse VALUES (2, '4012 1st Street', 'San Jose', 'CA');
INSERT INTO inventory VALUES (1001, 1, '0201633469', 3);
INSERT INTO inventory VALUES (1002, 1, '0201563177', 2);
INSERT INTO inventory VALUES (1003, 2, '0201633469', 1);
INSERT INTO inventory VALUES (1004, 2, '0201563177', 5);

The online order information in the XML document orders.xml on the local file system contains the following data:

<?xml version="1.0" encoding="UTF-8"?>
<orders>
  <order id="101" cid="1" date="2004-10-24">
    <item isbn="0201633469"/>    
    <item isbn="0201633469"/>    
  </order>
  <order id="102" cid="3" date="2004-10-25">
    <item isbn="0201633469"/>    
  </order>
  <order id="103" cid="2" date="2004-10-26">
    <item isbn="0201633469"/>    
    <item isbn="0201563177"/>    
  </order>
  <order id="104" cid="1" date="2004-10-27">
    <item isbn="0201563177"/>    
  </order>
  <order id="105" cid="3" date="2004-10-28">
    <item isbn="0201563177"/>    
    <item isbn="0201563177"/>    
  </order>
</orders>

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, WebSphere, Commerce
ArticleID=201082
ArticleTitle=Use WebSphere Federation Server Version 9.1 to integrate XML data
publish-date=03082007