In the second post of this series I showed how you could use XML functions to return your recordset from the database as HTML

row and cell elements (“Make the Database Return Your Recordset as an HTML Table — Using XML”, 8/6/2016). We did this by naming the XML elements after the appropriate HTML elements.

If we stopped at this point, we could create a SmartObject that returns our string of HTML

data to the caller. For example, we can already have the HTML for the rest of the report elsewhere, and this SmartObject simply “plugs in” the data to be displayed. This could be as easy as concatenating strings (say, an HTML report header, the HTML output from the SmartObject, and an HTML report footer) in a Data Event.

We can go further. By using XSL Transform, we should be able to have the database return our entire HTML report — not just the table.

In the last post, our crucial last step was to convert the v_TABLE var — a CLOB — into an XML document:


v_XML := XMLTYPE.CREATEXML(v_TABLE);

 

v_XML is an XMLTYPE datatype which now contains our HTML data as an XML document, with a root node of

. We need our HTML data as an XML document in order to take advantage of the XMLTRANSFORM command (note the emphasis):


SELECT XMLTRANSFORM(
XMLELEMENT("INPUT", V_XML)
,
'

body {font-family:arial, sans-serif;}
tbody > tr:nth-child(even){background-color:#ddd;}
tbody > tr:nth-child(odd){background-color:#fff;}
table {border: 1px solid #ccc; border-collapse:collapse;padding:5px;}
th, td {border: 1px solid #ccc; padding:5px; }
thead, tfoot {background-color:#ddd;text-align:center;}

BOOK LENDING REPORT

'
).GETCLOBVAL() INTO v_OUT
FROM DUAL;

 

In the first argument in the XMLTRANSFORM command, we invoke the XMLELEMENT function to label our XML document "INPUT" so that we can refer to it in our second argument, which is our XSL stylesheet.

This is a simple stylesheet intended to produce a basic HTML document with some CSS applied to give the rows alternating gray and white background colors.

The transform command outputs to a variable called v_OUT, of CLOB datatype — but flavor to taste.

My next steps will be to convert this script into a new stored procedure, which will take BOOK_ID as an input parameter and output our HTML, then to create a SmartObject based upon it.