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:

XMLROW:
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0050587.html

XMLGROUP:

http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.sql.ref.doc/doc/r0050588.html

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 51 other followers

%d bloggers like this: