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:
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:
You'll use WDSC's XML tools and wizards to create the following:
You don't need any technical skills to do the examples below. Just follow the directions. It helps you
To continue reading for free, register below or login
To read more you must become a member of Search400.com
r understanding of the examples if you know some basic information about the following:
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:
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:
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
/*---------------------------------------------------------------------
[TABLE]
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:
[TABLE]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:
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
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:
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
==================================
