XML Construction in Views and User-Defined Functions
August 30, 2010
Many enterprises hold significant amounts of relational data in their databases. New applications and integration scenarios often require this data to be communicated in the form of XML messages, e.g. in service-oriented architectures (SOA), web services, application integration (A2A), or data exchange with internal and external parties (B2B). Fortunately, the SQL language contains a set of functions that let you convert relational data into XML, right in your SQL queries that read the required data. This is easier and more efficient than writing complex application code.
Some of the most important SQL/XML construction functions include:
- XMLELEMENT, to construct an XML element from a relational value
- XMLATTRIBUTES, to add attributes to a constructed element
- XMLFOREST, to construct a sequence of elements
- XMLNAMESPACES, to add a namespace to an element
- XMLAGG, to aggregate multiple XML elements that stem from separate relational rows into a single block of XML
In many cases it is particularly convenient to encapsulate XML construction in a function or a view that applications can simply use in their SQL statements without having to be concerned with the details of the XML construction itself.
Let’s look at a few simple examples. First, assume that we have the following sample table with 3 rows of data:
CREATE TABLE employee(firstname varchar(30), lastname varchar(30), dept varchar(5)); INSERT INTO employee(firstname, lastname, dept) VALUES ('John', 'Doe', 'A01'), ('Mary', 'Jones', 'B02'), ('Peter', 'Pan', 'A01');
In a real application, each employee record would have many more columns but for simplicity let’s just work with these 3 columns f0r now. Assume that we need to retrieve each employee record in a specific XML format with a root element called “emp”, an attribute for the department numbers, and two child elements “first” and “last” for the first name and last, respectively. We can define the following user-defined scalar function. This function takes several relational column values as input returns a result of type XML.
CREATE FUNCTION emplxml(first VARCHAR(30), last VARCHAR(30), dept VARCHAR(5)) RETURNS XML RETURN XMLELEMENT(name "emp", XMLATTRIBUTES(dept AS "dept"), XMLFOREST(first, last) ) ;
This user-defined function (UDF) performs the desired XML construction for a given employee record. An application can then use the UDF to easily retrieve the name and department information in XML format:
SELECT emplxml(firstname, lastname, dept) FROM employee; <emp dept="A01"><FIRST>John</FIRST><LAST>Doe</LAST></emp> <emp dept="B02"><FIRST>Mary</FIRST><LAST>Jones</LAST></emp> <emp dept="A01"><FIRST>Peter</FIRST><LAST>Pan</LAST></emp> 3 record(s) selected.
Now let’s assume we need one XML document per department instead of one XML per employee. That means that we need to group the employee rows by department and aggregate the rows for each department into a single XML document. We can probably do that in a table function, but it can also be done conveniently in a view:
CREATE VIEW deptxmlview(deptID, deptdoc) AS SELECT dept, XMLELEMENT(NAME "Department", XMLATTRIBUTES (dept AS "name" ), XMLAGG( XMLELEMENT(NAME "emp", lastname) ) ) FROM employee GROUP BY dept;
Again, an application does not need to be concerned with details of the XML aggregation and construction. It just needs to know that it can read one XML document per department from the view:
SELECT deptdoc FROM deptxmlview WHERE deptID = 'A01' or deptID = 'B02'; <Department name="A01"><emp>Doe</emp><emp>Pan</emp></Department> <Department name="B02"><emp>Jones</emp></Department> 2 record(s) selected.
If you need to build more complex XML documents from your relational tables, you can certainly join multiple tables and apply the SQL/XML construction functions to columns produced by the join. You can also encapsulate pieces of XML construction in views and UDFs and use them as building blocks to construct bigger pieces of XML in a modular fashion.