Systems Engineering and RDBMS

XML in DB2 LUW

Posted by decipherinfosys on November 10, 2007

In one of our previous blog post, we had covered, how in Oracle can we convert relational data to XML in vice-versa. Today we will get basic familiarity with XML functions in DB2 LUW. IBM has also provided very strong XML support in DB2 LUW. DB2 LUW contains certain built-in SQL/XML functions which can be used to generate XML from relational data. In this blog post, we will cover some of the basic SQL/XML functions.

  • XML2CLOB: This function converts XML into CLOB format so that it can be viewed easily in a text format. Input to the function is XML and result is CLOB.
  • XMLSERIALIZE: This function can convert XML into CHAR, VARCHAR or CLOB value. Input should be only XML data.
  • XMLELEMENT: This scalar function constructs the named XML element node. It can be nested and includes an element name, an optional attributes and arguments that make an element’s content. Arguments can be zero or more than zero.
  • XMLFOREST: This is also a scalar function and builds the sequence of XML element nodes. If elements are not named, then column names are used as a tag. Let us connect to DB2 using command editor or any other GUI tool to test our sample scenario. We will first create the table and populate it with some data.

CREATE TABLE TEST(EMP_ID INTEGER, EMP_Name VARCHAR(30),BIRTH_DATE DATE);

INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (1, ‘Jack’,’01/20/1965′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (2, ‘John’,’02/01/1982′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (3, ‘Beth’,’10/11/1970′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (4, ‘Mary’,’09/30/1970′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (5, ‘Allen’,’03/03/1975′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (6, ‘Diana’,’11/02/1968′);
INSERT INTO TEST (EMP_ID, EMP_NAME, BIRTH_DATE) VALUES (7, ‘Don’,’12/31/1980′);

Run the following query to get the XML output of relational data using XMLFOREST.

SELECT XML2CLOB(XMLFOREST(EMP_ID, EMP_NAME)) AS XMLDATA
FROM TEST;

Output is abbreviated for readability purpose.

<EMP_ID>1</EMP_ID><EMP_NAME>Jack</EMP_NAME>
<EMP_ID>2</EMP_ID><EMP_NAME>John</EMP_NAME>
<EMP_ID>3</EMP_ID><EMP_NAME>Beth</EMP_NAME>
<EMP_ID>4</EMP_ID><EMP_NAME>Mary</EMP_NAME>

As shown in the result, column values are wrapped within the tag which is nothing but the column name.

Now let us use XMLELEMT function to create the named element. When using command editor, if we don’t convert XML to VARCHAR or CLOB format, it will not display actual XML data in the query result window instead it will indicate that data is XML data and once we click on it, it will open p the XML document viewer to display the data. This is the reason we are converting it to CLOB. Run any of the following queries to display the data in CLOB format.

SELECT XMLSERIALIZE (XMLELEMENT(name “Employee” ,XMLFOREST(EMP_ID, EMP_NAME)) AS VARCHAR(500)) AS XML_DATA
FROM TEST;

OR

SELECT XML2CLOB (XMLELEMENT(name “Employee” ,XMLFOREST(EMP_ID, EMP_NAME))) AS XML_DATA
FROM TEST;

It will display same result set which is shown as under. Again we have abbreviated the output.

<Employee><EMP_ID>1</EMP_ID><EMP_NAME>Jack</EMP_NAME></Employee>
<Employee><EMP_ID>2</EMP_ID><EMP_NAME>John</EMP_NAME></Employee>
<Employee><EMP_ID>3</EMP_ID><EMP_NAME>Beth</EMP_NAME></Employee>

In this post, we have covered some basic built-in functions used for XML manipulation in DB2. In future, we will cover it in more details along with usage of remaining XML functions.

One Response to “XML in DB2 LUW”

  1. Nicolas said

    Nicolas…

    XML in DB2 LUW « Systems Engineering and RDBMS…

Sorry, the comment form is closed at this time.

 
%d bloggers like this: