You are an RPG programmer. You could use the XML parser APIs on the iSeries to learn how to do DOM or SAX parsing to generate XML documents. Good idea? No! Learn how to easily generate XML from SQL WITHOUT CODING using WDSC.

We'll look at WDSC's easy-to-use wizards that generate basic applications to move data between XML documents and relational databases. Using WDSC's XML-to-SQL tool, you'll be able to easily extract relational data using SQL into XML documents. On the other hand, you may have XML documents and want to store them in a relational database. The WDSC XML-toSQL tool can help with that, too.

Where you can use XML and SQL data transformations

XML documents can be used for many purposes:

  • Generating Web pages from the data.
  • Reformatting XML data to another XML format.
  • Transmitting data to other applications, systems or companies.

Data stored in an XML format can be generated by many types of source applications and consumed by many types of client applications, such as Web browsers, cell phones, PDAs, Web services clients. In addition to generating Web pages automatically using XML templating software (Apache Cocoon and other software frameworks) for easily publishing of content to the Web, XML documents are used to exchange data between different types of system easily. For example, a file of Open Purchase Orders could be sent as an XML document from a customer to a vendor using Web services or FTP.

The ability to generate XML documents from relational databases automatically and to store XML documents in relational databases is an important capability in most e-business application scenarios that involve transactions or business-to-business (B2B) applications. Here we'll look at generating an XML employees document from an employee table in DB2.

You can use IBM's WDSC toolset (V5R1) to create simple applications that do the following:

  • Generate XML documents from relational data using SQL.
  • Store XML documents in a relational database using SQL.

You'll use WDSC's XML tools and wizards to create the following:

  • XML documents
  • XML schemas
  • XSL transforms
  • HTML pages that display the XML content

You don't need any technical skills to do the examples below. Just follow the directions. It helps your understanding of the examples if you know some basic information about the following:

  • SQL database access
  • XML document concepts, including DTDs, Schemas and XSL
  • Familiarity with the WDSC IDE environment

In addition to WDSC, you'll need our sample SQL Employee table (or other tables of your choice) in a relational database such as DB2, Oracle, MySql or SQL Server.

Basic concepts: XML to SQL transformations

It's becoming reasonably common in many e-business scenarios to transform data between relational databases and XML documents. Conceptually, we need to do two things:

  1. Generate XML documents from a database where we map the columns of a table or tables of one or more rows to elements or element attributes in an XML document.
  2. Load XML documents to a database where we map elements of an XML document to rows in one or more tables of a relational database.

We'll review our XML data and our SQL table to see how they are defined and how they can be mapped to each other. We'll look at the following:

  • The contents of our Employee table
  • The DDL (Data Description Language) definition for the layout of the Employee table
  • The DDS definition for the same Employee table
  • The XML Employee document
  • The XML Schema for the Employee document

Employee table listing
Looking at a partial listing of the Employee table we see rows of data for each employee with columns of data representing each attribute (EMPNO, PHONENO etc).

Here is a subset of our Employee table listing.

/*----------------------------------------------------------------------
/* QueryProcessor.getTextReportForSelectQuery() Run date = Mon Dec 08 12:00:56 EST 2003
/* Database URL = jdbc:db2:SAMPLE /* Select statement = SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT, PHONENO, JOB FROM EMPLOYEE WHERE LASTNAME >= 'A' ORDER BY LASTNAME
/* rowLimit = 999999999
/*---------------------------------------------------------------------

EMPNOFIRSTNMELASTNAMEWORKDEPTPHONENOJOB
000150BRUCEADAMSOND114510DESIGNER
000200DAVIDBROWND114501DESIGNER
000050JOHNGEYERE016789MANAGR5
000340JASONGOUNOTE215698FIELDREP
000010CHRISTINEHAASA003978PRES
000090EILEENHENDERSONE115498MANAGER
000230JAMESJEFFERSOND212094CLERK
000260SYBILJOHNSOND218953CLERK
000210WILLIAMJONESD110942DESIGNER
000030SALLYKWANC014738MANAGER
000330WINGLEEE212103FIELDREP
*/

DDS definition of Employee table

Since the Employee table is also a physical file on the iSeries in DB2, it can be described with DDS as easily as it is with DDL. You can compare the DDS here to the DDL above to see the differences in how columns are described in a table. The CRTPF command would create this physical file. Regardless of whether you use CRTPF and DDS or a CREATE SQL statement with DDL, you wind up with essentially the same object in the same library.

 A*---------------------------------------------------------------------
 A*-- DDS file target = Employee
mapped from DDW source = Employee_ddw.txt
 Mon Dec 08 12:48:43 EST 2003
 A*--
 A*-- Cape Cod Bay Systems - cc 2000
 A*---------------------------------------------------------------------
 A   R EMPLOYEER
 A*--
 A   EMPNO      0006A   COLHDG('EMPNO')
 A                      ALIAS(EMPNO)
 A   FIRSTNME   0012A   COLHDG('FIRSTNME')
 A                      ALIAS(FIRSTNME)
 A   MIDINIT    0001A   COLHDG('MIDINIT')
 A                      ALIAS(MIDINIT)
 A   LASTNAME   0015A   COLHDG('LASTNAME')
 A                      ALIAS(LASTNAME)
 A   WORKDEPT   0003A   COLHDG('WORKDEPT')
 A                      ALIAS(WORKDEPT)
 A   PHONENO    0004A   COLHDG('PHONENO')
 A                      ALIAS(PHONENO)

Employee XML document
An XML document is made up of a hierarchically related set of elements and attributes. If you examine the Employee XML document below, you can see that EMPLOYEE elements are sub elements of the SQLResult element. EMPLOYEE attributes such as EMPNO and PHONENO are sub elements of an EMPLOYEE element.

<?xml version="1.0" encoding="UTF-8"?>
<SQLResult xmlns="http://www.ibm.com/EMPLOYEE"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.ibm.com/EMPLOYEE
 employeeByName.xsd">
  <EMPLOYEE>
     <EMPNO>000150</EMPNO>
     <FIRSTNME>BRUCE</FIRSTNME>
     <MIDINIT/>
     <LASTNAME>ADAMSON</LASTNAME>
     <WORKDEPT>D11</WORKDEPT>
     <PHONENO>4510</PHONENO>
     <HIREDATE>1972-02-12</HIREDATE>
     <JOB>DESIGNER</JOB>
     <EDLEVEL>16</EDLEVEL>
     <SEX>M</SEX>
     <BIRTHDATE>1947-05-17</BIRTHDATE>
     <SALARY>25280.00v/SALARY>
     <BONUS>500.00</BONUS>
     <COMM>2022.00</COMM>
  </EMPLOYEE>
  <EMPLOYEE>
     <EMPNO>000200</EMPNO>
     <FIRSTNME>DAVID</FIRSTNME>
     <MIDINIT/>
     <LASTNAME>BROWN</LASTNAME>
     <WORKDEPT>D11</WORKDEPT>
     <PHONENO>4501</PHONENO>
     <HIREDATE>1966-03-03</HIREDATE>
     <JOB>DESIGNER</JOB>
     <EDLEVEL>16</EDLEVEL>
     <SEX>M</SEX>
     <BIRTHDATE>1941-05-29</BIRTHDATE>
     <SALARY>27740.00</SALARY>
     <BONUS>600.00</BONUS>
     <COMM>2217.00</COMM>
  </EMPLOYEE>

Employee XML schema
The elements of an EMPLOYEE XML document are defined by a DTD (Document Type Definition) or an XML schema. Below is a partial XML schema for the Employee XML document defining each element and its relationship to other elements in the document. Multiple employee elements are stored under the SQLResult element in the Employee document.

Examine the schema and note the hierarchy defined here matches that in the Employee XML document:

SQLResult
 EMPLOYEE
  EMPNO
   FIRSTNME
  (And so on …)

<?xml version="1.0" encoding="UTF-8"?>
<schema targetNamespace="http://www.ibm.com/EMPLOYEE"
 xmlns="http://www.w3.org/2001/XMLSchema" xmlns:EMPLOYEE="http://www.ibm.com/EMPLOYEE">
 <element name="SQLResult">
  <complexType>
   <sequence>
    <element maxOccurs="unbounded" minOccurs="0" ref="EMPLOYEE:EMPLOYEE"/>
   </sequence>
  </complexType>
 </element>
 <element name="EMPLOYEE">
  <complexType>
   <sequence>
    <element ref="EMPLOYEE:EMPNO"/>
    <element ref="EMPLOYEE:FIRSTNME"/>
    <element ref="EMPLOYEE:MIDINIT"/>
    <element ref="EMPLOYEE:LASTNAME"/>
    <element ref="EMPLOYEE:WORKDEPT"/>
    <element ref="EMPLOYEE:PHONENO"/>
    <element ref="EMPLOYEE:HIREDATE"/>
    <element ref="EMPLOYEE:JOB"/>
    <element ref="EMPLOYEE:EDLEVEL"/>
    <element ref="EMPLOYEE:SEX"/>
    <element ref="EMPLOYEE:BIRTHDATE"/>
    <element ref="EMPLOYEE:SALARY"/>
    <element ref="EMPLOYEE:BONUS"/>
    <element ref="EMPLOYEE:COMM"/>
   </sequence>
  </complexType>
 </element>
 <element name="EMPNO">
  <simpleType>
   <restriction base="string">
    <maxLength value="6"/>
   </restriction>
  </simpleType>
 </element>
 <element name="FIRSTNME">
  <simpleType>
   <restriction base="string">
    <maxLength value="12"/>
   </restriction>
  </simpleType>
 </element>
 <element name="MIDINIT">
  <simpleType>
   <restriction base="string">
    <maxLength value="1"/>
   </restriction>
  </simpleType>
 </element>
 <element name="LASTNAME">
  <simpleType>
   <restriction base="string">
    <maxLength value="15"/>
   </restriction>
  </simpleType>
 </element>
<< and so on.. >>

The challenge for generating XML from a table or storing XML in a table comes down to a plan to map the elements of an XML document to an SQL table or vice versa. Inspecting the above XML schema and the DDL for the Employee table, it's fair to say an easy mapping plan is to map every selected column name to an XML Employee sub element of the same name. This is the strategy the XML-to-SQL wizard in WDSC uses to generate an XML document. It uses the same strategy to store the elements in an XML document into the Employee table as well.

Mapping SQL table columns to XML Employee sub elements
The table below shows the simple strategy of mapping columns in an SQL table to XML elements by name:

SQL table column XML element
EMPNO EMPLOYEE:EMPNO
FIRSTNME EMPLOYEE:FIRSTNME
MIDINIT EMPLOYEE:MIDINIT
LASTNAME EMPLOYEE:LASTNAME
WORKDEPT EMPLOYEE:WORKDEPT
PHONENO EMPLOYEE:PHONENO
HIREDATE EMPLOYEE:HIREDATE
JOB EMPLOYEE:JOB
EDLEVEL EMPLOYEE:EDLEVEL
SEX EMPLOYEE:SEX
BIRTHDATE EMPLOYEE:BIRTHDATE
SALARY EMPLOYEE:SALARY
BONUS EMPLOYEE:BONUS
COMM EMPLOYEE:COMM

WDSC support for XML-to-SQL transformations

So why is WDSC important for moving XML data to and from relational databases? Because it saves A LOT of time and money in building these applications compared with the alternative: learning to program with XML parsers. Also, iSeries developers new to WDSC and XML can do the work.

Let's use the WDSC XML-to-SQL wizard to generate the Employee XML document from the Employee table.

Begin by opening the Web perspective in WDSC to your Web project. Then do the following:

  • Create a new XML-to-SQL query.
  • Create XML from SQL Query.
  • Select an existing SQL SELECT statement that defines our SQL query.
  • Define how the XML is generated from the SQL query.
  • Specify any selection values for the SELECT statement WHERE clause.

The Employee XML document, schema, HTML page, XSL transform and XML query template are created in the project's Web-Inf folder.

Summary of steps to create XML document

  1. >> Workbench > File > New > XML > XML to SQL query
    << the XML to SQL query wizard notebook opens
  2. >> Select the option to: Create XML from SQL Query > Next
    << the select SQL statement page is shown
  3. >> Select an existing SQL SELECT statement defined in the project for the Employee table > Next
    << the define XML page is shown
  4. >> Specify the parameters for how the XML is generated from the SQL query > Next
    << a prompt is shown to specify any selection values for the SQL WHERE clause
  5. >> Specify the selection values to use for the SELECT statement > Finish
    << the XML document, schema, HTML page, XSL transform and XML query template is created

When prompted on how to generate an XML stream from the SQL query, choose to show table columns as elements IF you are just creating an XML document. If you will be using a Java-to-XML binding to generate Java beans for the mapping, choose to show table columns as attributes.

For our purposes, we'll choose to generate the table columns as XML elements.

We also choose the option to create an XML schema that defines our XML document externally (just as DDS externally describes a physical data file on the iSeries).

Finally, we'll save our XML to SQL query specification as an .xst template file.

Here, we need to set a value ('A') for the starting LASTNAME to search on for our SQL SELECT statement. Note the LASTNAME, here HAS to be enclosed in single quotes.

Press Finish, and congratulations! Look what you've created:

  • EmployeeByName.html -- a Web page showing the XML document
  • EmployeeByName.xml -- an XML document file
  • EmployeeByName.xsd -- an XML Schema definition
  • EmployeeByName.xsl -- an XSL transform to generate the XML document file
  • EmployeeByName.xst -- the XML template for this query


XML-to-SQL wizard template
Here is the generated XML template used as a definition by the XML-to-SQL wizard to run an XML query. It is an XML document also. It's easy to see the database connection information and the SQL SELECT statement used in the query at runtime It's not critical to understand all of the XML elements in this document because the XML-to-SQL query wizard needs to interpret the document, NOT you (another plus).

Note the sections marked by ! [CDATA [ ..… ] ] are handled as literals by the XML parser that reads this definition. Literals are strings that are NOT translated as XML strings (those containing XML control characters like < and > ) by a parser. You can see the WHERE clause in the SELECT statement contains a > symbol. IF this section was not marked as CDATA (literal character data), the parser would incorrectly assume the > symbol in the where clause would be the start of a new XML element.

<?xml version="1.0" encoding="UTF-8"?>
<SQLGENERATEINFORMATION>
 <DATABASEINFORMATION>
  <LOGINID>jem</LOGINID>
  <PASSWORD><![CDATA[jem]]></PASSWORD>
  <JDBCDRIVER>COM.ibm.db2.jdbc.app.DB2Driver</JDBCDRIVER>
  <JDBCSERVER>jdbc:db2:SAMPLE</JDBCSERVER>
 </DATABASEINFORMATION>
 <STATEMENT>
  <![CDATA[ SELECT JEM.EMPLOYEE.EMPNO, JEM.EMPLOYEE.FIRSTNME, JEM.EMPLOYEE.MIDINIT,JEM.EMPLOYEE.LASTNAME, JEM.EMPLOYEE.WORKDEPT, JEM.EMPLOYEE.PHONENO, JEM.EMPLOYEE.HIREDATE, JEM.EMPLOYEE.JOB, JEM.EMPLOYEE.EDLEVEL, JEM.EMPLOYEE.SEX, JEM.EMPLOYEE.BIRTHDATE, JEM.EMPLOYEE.SALARY, JEM.EMPLOYEE.BONUS, JEM.EMPLOYEE.COMM FROM JEM.EMPLOYEE WHERE JEM.EMPLOYEE.LASTNAME >= :name ORDER BY LASTNAME ASC, FIRSTNME ASC ]]>
 </STATEMENT>
 <OPTIONS>
  <FORMATOPTION>GENERATE_AS_ELEMENTS</FORMATOPTION>
  <RECURSE>FALSE</RECURSE>
 </OPTIONS>
</SQLGENERATEINFORMATION>

Key tips

Handling errors on storing documents in tables
If you have an error running the store XML document in a table service, you'll usually get some fairly specific details on the cause of the error in the WDSC console window. If it's a simple data error in the XML document, you can use the XML editor to correct that.

Using Java classes vs. the XML-to-SQL wizard
Eventually you'll want to learn to use the Java classes SQLToXML and XMLToSQL to do the same thing we did with the XML to SQL wizard. Why? Because the Java classes allow you to dynamically specify the SQL information. One Java bean could read or write XML data to and from any SQL table.

Use an XSL transform to map XML to another format
When loading XML documents to a target SQL table, it is common to have to change, add or drop elements to fit the target table definition. XSL can be used nicely to do that when needed.

In practice, you would probably be storing the XML document in a database on another system in the network and, optionally, running an XSL transform to map the Employee XML document to a format that matched the target database. The XSL transform is where you can rename an element (PHONENO might be renamed PHONE_NBR, for example). You can add or drop elements. You can even redefine an element in the target schema. PHONENO might be 10 positions, numeric. PHONE_NBR in the target XML schema might be 20 positions, character.

---------------------------------------

About the author: Jim Mason, president of ebt-now, is an iSeries WebSphere engineer. ebt-now provides iSeries WebSphere, WebFacing project management, engineering, development and training services. You can reach Jim at jemason@ebt-now.com or call 508-888-0344.

==================================
MORE INFORMATION
==================================

  • Other options for generating XML or storing XML in a database
    Search400.com expert Jim Mason provides a few more ways to generate or store XML in a database.
  • WDSC lets you tap into the power of XML
    When it comes to Web development, XML is a powerful tool. But you don't need to be a highly skilled Java XML developer to make use of it. WebSphere Development Studio Client (WDSC) for iSeries Version 5 (both the Standard and Advanced editions) offers several options to create Web database and XML applications, including a number of helpful wizards.


This was first published in December 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.