How to quickly produce XML from relational tables
June 8, 2011
In DB2 for Linux, UNIX, and Windows, the quickest and easiest way to produce XML from relational tables is to use the function XMLROW or XMLGROUP. The following examples illustrates how this works.
Let’s first define a relational table and insert a couple of rows:
create table employee(id int, name varchar(30), dept char(3));
insert into employee values (1, ‘John’, ‘PX8′);
insert into employee values (2, ‘Mary’, ‘KH2′);
select id, name, dept from employees;
ID NAME DEPT
———– —————————— ————————
1 John PX8
2 Mary KH2
2 record(s) selected.
Now, let’s select the same columns with the XMLROW function:
select xmlrow(id, name, dept) from employee;
<row><ID>1</ID><NAME>John</NAME><DEPT>PX8</DEPT></row>
<row><ID>2</ID><NAME>Mary</NAME><DEPT>KH2</DEPT></row>
2 record(s) selected.
You can see that each row is returned as an XML document, using column names as element names. And you can further customize the XML if you like. For example, you can rename the root element for each row:
select xmlrow(id, name, dept OPTION ROW “EMPL”) from employee;
<EMPL><ID>1</ID><NAME>John</NAME><DEPT>PX8</DEPT></EMPL>
<EMPL><ID>2</ID><NAME>Mary</NAME><DEPT>KH2</DEPT></EMPL>
2 record(s) selected.
You can also rename the elements for individual columns, if you want:
select xmlrow(id AS “EMP_NO”, name, dept OPTION ROW “EMPL”) from employee;
<EMPL><EMP_NO>1</EMP_NO><NAME>John</NAME><DEPT>PX8</DEPT></EMPL>
<EMPL><EMP_NO>2</EMP_NO><NAME>Mary</NAME><DEPT>KH2</DEPT></EMPL>
2 record(s) selected.
And if you prefer to combine all selected rows into a single XML document, you would use XMLGROUP instead of XMLROW:
select xmlgroup(id, name, dept OPTION ROW “EMPL”) from employee;
<rowset>
<EMPL><ID>1</ID><NAME>John</NAME><DEPT>PX8</DEPT></EMPL>
<EMPL><ID>2</ID><NAME>Mary</NAME><DEPT>KH2</DEPT></EMPL>
</rowset>
1 record(s) selected.
For more options and information on these functions, see:
SQL/XML publishing functions for constructing XML values:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.xml.doc/doc/c0023909.html
