I emphasized in my previous post the mechanism through which SmartObject data may be accessed within K2 Server Events. The specific example I gave was the case of a reporting feature for which I had been “awarded” responsibility (“Access Your Workflow SmartObjects Through Server Events”, 7/30/2016).

The feature was comprised of a K2 workflow process consisting of a server event that generates the report in HTML, an event that generates a PDF, and and event that persists the PDF in a document management system. As detailed in the previous post, the server event called SmartObjects to retrieve the data for the report, then used a StringBuilder object to create the HTML through a series of for loops before returning the completed HTML string to a process-level data field in the workflow process.

I began to wonder if perhaps the database engine could handle this transformation more efficiently, so I started digging into the stored procedures that support the report.

I started with the queries already in place. Since I knew they were returning the correct data, I wanted to ensure the result set was essentially the same — just returned looking like HTML. Three commands I used extensively for this purpose were XMLELEMENT, which essentially creates an XML node; XMLFOREST, which converts each of its parameters into a nested XML element; and and XMLAGG, which returns an aggregated XML document.

SELECT XMLAGG(XMLELEMENT("TR",
XMLFOREST(
LIBRARY.RETURNED_ON as "TD",
NVL( USER.FULL_NAME,' ' ) as "TD",
NVL( LIBRARY.BOOK_TITLE,' ' ) as "TD"
)))
FROM LIBRARY
JOIN USER on USER.USERID = LIBRARY.CHECKED_OUT_BY
WHERE LIBRARY.BOOK_ID = v_BOOKID
 


With my XML code in place, my FULL_NAME, BOOK_TITLE and RETURNED_ON properties
should now all be coming back to me in XML that looks like HTML table cells
() nested inside of an HTML table row (). This code will produce my table body. In HTML, this is denoted by the tag.

Next, I’ll need to build a header () and a footer (). The header should contain the names of the columns; the footers can remain blank.


DECLARE
v_COL1 VARCHAR2(20) := 'RETURNED ON';
v_COL2 VARCHAR2(20) := 'FULL NAME';
v_COL3 VARCHAR2(20) := 'BOOK TITLE';

. . .

SELECT XMLAGG(XMLELEMENT("THEAD",XMLELEMENT("TR", XMLELEMENT("TH", v_COL1), XMLELEMENT("TH", v_COL2), XMLELEMENT("TH", v_COL3) )))
FROM DUAL
 


Use the same code for the footer — just set the values for the columns to a single space. Either change the values of the variables or replace the variables with ' ', as I have below.

So now we have our essential elements: our , our , and our . We just need to assemble it into a single query.

The following technique does exactly that for us. Take special note of the emphasized portions of the code:


DECLARE
. . .
v_COL3 VARCHAR2(20) := 'BOOK TITLE';
v_THEAD CLOB;
v_TBODY CLOB;
v_TFOOT CLOB;
v_TABLE CLOB;
. . .

SELECT HEADER.v_THEAD || XMLELEMENT("TBODY",BODY.v_TBODY) || FOOTER.v_TFOOT
INTO v_TABLE
FROM
(
SELECT XMLAGG(XMLELEMENT("THEAD",XMLELEMENT("TR", XMLELEMENT("TH", v_COL1), XMLELEMENT("TH", v_COL2), XMLELEMENT("TH", v_COL3) )))
AS v_THEAD
FROM DUAL) HEADER,
(
SELECT XMLAGG(XMLELEMENT("TR",
XMLFOREST(
LIBRARY.RETURNED_ON as "TD",
NVL( USER.FULL_NAME,' ' ) as "TD",
NVL( LIBRARY.BOOK_TITLE,' ' ) as "TD"
)))
AS v_TBODY
FROM LIBRARY
JOIN USER on USER.USERID = LIBRARY.CHECKED_OUT_BY
WHERE LIBRARY.BOOK_ID = v_BOOKID) BODY,

(
SELECT XMLAGG(XMLELEMENT("THEAD",XMLELEMENT("TR", XMLELEMENT("TH", ' '), XMLELEMENT("TH", ' '), XMLELEMENT("TH", ' ') )))
AS v_TFOOT
FROM DUAL) FOOTER;
 

So let’s take a closer look at that SELECT statement. We’ve concatenated the THEAD, TBODY and TFOOT sections in the query and are returning it in a variable called v_TABLE — but if evaluated as an XML document, it’ll fail validation. Can you guess why? Here’s another look at that statement (emphasis removed):


SELECT HEADER.v_THEAD || XMLELEMENT("TBODY",BODY.v_TBODY) || FOOTER.v_TFOOT
INTO v_TABLE
. . .

The reason why the query would fail XML validation is because a valid XML document must have a single root node. This query assembles a document that has three of them — , , and .

So we need to add a common root node. I can’t think of a better one than

SELECT '

Finally, we went to the trouble of adding that root node because we’re going to convert that CLOB into a proper XML document:


v_XML := XMLTYPE.CREATEXML(v_TABLE);

In this post, we’ve walked through how we can use the database to return our HTML for us, instead of using code in a K2 Server Event to format the data.

In the next installment, I’ll show you how we’ll take this new XML document we just made and rinse it through an XSL stylesheet within the same procedure to return an entire HTML document instead of just the

Hopefully we’ll later turn this souped-up sproc into a SmartObject that can replace the K2 Server Event we were using to build the report.