rpbourret.com - XML-DBMS: FAQs

Q: How do I retrieve a result set as XML?

Applies to: 2.0

To retrieve a result set as XML in version 2.0:

Map the result set columns using a ClassMap element. This specifies the table name with which the result set is mapped.
Write a filter document that uses a ResultSetInfo element. This associates a result set name with the table name in step 1.
If you are using Transfer, pass a SELECT statement and the name of the result set to Transfer. This associates a result set name with an actual result set.
If you are calling DBMSToDOM directly, execute a SELECT statement and pass the returned ResultSet to retrieveDocument. This associates a result set name with an actual result set.

These steps are described in more detail below, but I'll explain the confusing bit about names first.

Table names and result set names

Result sets do not have names. Because of this, XML-DBMS cannot look up the ClassMap for a result set without some additional help. That is, it has no table name with which to search through the ClassMaps in the map document and find the ClassMap that maps the result set to XML.

In version 1.x, this problem was solved by requiring the ClassMap for the result set to use the special table name "Result Set". This had two drawbacks. First, it meant you could only map a single result set with a particular map, since the table name "Result Set" could only be used once. Second, it meant that you couldn't use an existing map document that already mapped the table over which you created your result set, since that map document used the real table name.

In version 2.0, this problem is solved by allowing you to use any table name you want in the map document, giving each result set a name, and then associating the result set name with the table name in the filter document. It's a bit awkward, but solves both of the problems described above.

ClassMaps for result sets

A result set is essentially a table, so it can be mapped to XML using a ClassMap. There are several important points here:

The ClassMap can use any database/catalog/schema/table name. If the result set is constructed over a single table, it is a good idea to use the actual database/catalog/schema/table names. If the result set joins two or more tables, then you need to invent a database/catalog/schema/table name.
The list of properties in the ClassMap does not have to match the list of columns in the result set. If a column is in the result set and there is no PropertyMap for it in the ClassMap, the data for that column is not transferred to XML. If there is a PropertyMap for a column but that column is not in the result set, the PropertyMap is ignored. One consequence of the second point is that you can map all columns in a table and then use the resulting ClassMap for any result set built over that table.
XML-DBMS retrieves column names from the result set. If you are unsure what column name will be returned by the result set, use the AS column-name clause in your SELECT list and the same column name in your PropertyMap. For example:
```
   SELECT Price * 0.9 AS DPrice FROM Parts

   <PropertyMap>
      <ElementType Name="DiscountedPrice" />
      <ToColumn Name="DPrice" />
      ...
   </PropertyMap>
```
If the ClassMap for the result set contains any RelatedClass elements, these will be used to query child tables. This allows you to construct a deeply nested XML document based on the rows in a result set. For example, you might construct a result set over the sales order table and use RelatedClass elements to retrieve data from the customer table.

Filter document

The filter document tells XML-DBMS what data to retrieve. To retrieve data from a result set, you must use the ResultSetInfo element. This associates a result set name with a table name. For example, to associate the table name "Parts" (used in the map) with the result set name "PartsRS", use the following ResultSetInfo element:

   <ResultSetInfo Table="Parts" Name="PartsRS" />

Note that if you only have a single result set (the most common case), you can leave off the Name attribute and the code will figure out what to do. (If you're curious, the default result set name is "Default".) In other words, if you have a single result set, you can describe it with the following filter document:

   <!DOCTYPE FilterSet SYSTEM "filters.dtd">
   <FilterSet>
      <Options>
         <Wrapper Name="your-wrapper-name-here" />
      </Options>
      <Filters>
         <Filter>
            <ResultSetInfo Table="your-table-name-here" />
         </Filter>
      </Filters>
   </FilterSet>

Note that this filter document includes a wrapper element. This is necessary if the result set has more than one row.

Result set names are mostly useful when you want your XML document to contain data from multiple result set. For example, suppose you want to retrieve data from result sets built over the Parts and Customers tables. (Note that the result sets will not be nested -- one will follow the other.) Your filter document might look like this:

   <!DOCTYPE FilterSet SYSTEM "filters.dtd">
   <FilterSet>
      <Options>
         <Wrapper Name="PartsAndCustomers" />
      </Options>
      <Filters>
         <Filter>
            <ResultSetInfo Table="Parts" Name="PartsRS" />
         </Filter>
         <Filter>
            <ResultSetInfo Table="Customers" Name="CustomersRS" />
         </Filter>
      </Filters>
   </FilterSet>

Using Transfer

If you are using the Transfer tool, you must first fix this bug in Transfer. After that, retrieving data from a result set is easy -- just pass in the map and the SELECT statement to execute along with the normal database and parser properties. For example, to retrieve data from the Parts table, you might pass in the following properties:

   Method=RetrieveDocumentBySQL
   MapFile=parts.map
   XMLFile=parts.xml
   FilterFile=parts.ftr
   Select=SELECT Number, Price, Description FROM Parts WHERE Number LIKE 'A%'

Note there is no result set name. This is because the default is being used. Now suppose you want to retrieve data from the Parts and Customers tables. You might pass in the following properties:

   Method=RetrieveDocumentBySQL
   MapFile=PartsAndCustomers.map
   XMLFile=PartsAndCustomers.xml
   FilterFile=PartsAndCustomers.ftr
   Select1=SELECT Number, Price, Description FROM Parts WHERE Number LIKE 'A%'
   SelectResultSetName1=PartsRS
   Select2=SELECT Number, Name FROM Customers WHERE City='Seattle'
   SelectResultSetName2=CustomersRS

In this case, we have used result set names (which are also used in the filter document) to distinguish between the two result sets. We have also numbered the Select and SelectResultSetName properties with the suffixes 1 and 2.

Using DBMSToDOM directly

If you call DBMSToDOM directly, you need to create a ResultSet yourself and pass this to retrieveDocument. For example, here's how you might retrieve a single result set as XML:

   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery("SELECT Number, Price, Description
      FROM Parts WHERE Number LIKE 'A%'");

   Document doc = domToDBMS.retrieveDocument(transferInfo, rs,
                                             filterSet, null, null);

And here's how you might retrieve multiple result sets as XML. Note that the result sets are loaded into a hashtable keyed by result set name; this hashtable is then passed to retrieveDocument.

   Hashtable resultSets = new Hashtable();

   Statement stmt1 = conn.createStatement();
   ResultSet rs1 = stmt1.executeQuery("SELECT Number, Price, Description
                                       FROM Parts WHERE Number LIKE 'A%'");
   resultSets.put("PartsRS", rs1);

   Statement stmt2 = conn.createStatement();
   ResultSet rs2 = stmt1.executeQuery("SELECT Number, Name FROM
                                       Customers WHERE City='Seattle'");
   resultSets.put("CustomersRS", r2);

   Document doc = domToDBMS.retrieveDocument(transferInfo, resultSets,
                                             filterSet, null, null);

Back to the XML-DBMS FAQs

Ronald Bourret