Question

Dashboarding: Creating HTML tables and formatting it with XMLTemplater and XQuery

fbinneman
Rank

Hi All

This is not a question, but some feedback/tips on how I solved this issue - hopefully it will assist someone that had issues with a similar project as well. I was trying to create a dynamic html dashboard that reads a bunch of real time values from database records and then display them with labels in an HTML table, the order of the values, layout and structure of which I wanted to format to my liking. The HTMLReportGenerator transformer did not give me enough control as to what I wanted to do, so I ended up using the XMLTemplater.

If you want to create an HTML page using the XMLTemplater, you may find that the HTML code does not always parse correctly when running your workspace (the HTML is evaluated to ensure it conforms to XML standards). The reason for this is that some of HTML tags were not properly opened or closed. Since my HTML was still very valid, a workaround for this is to comment out (using "<!--" and "-->") those pieces of HTML code used in the XMLTemplater and then use a StringReplacer transformer afterwards to find and replace all the "<!--" and "-->" strings and replace them with "" (you will need two StringReplacer transformers for this). You can then output this to an HTML writer.

Now, since I wanted to display the data that was read all at the same time (and not one by one as they are processed in the workflow), in order to be able to reference each of them in the order I wanted, I imported the data into a ListBuilder transformer, with two fields of interest, say "Name" and "Value". The ListBuilder creates a list that is by default called "_list". As an example, the list would store something like this.

Name Value

---------------------------

"abc" "1.23456"

"def" "2.06789"

"ghi" "5.54321"

----------------------------

To reference the values in the HTML file, create an HTML table and then add the "Names" and "Values" using the fme:get-attribute XQuery functions. Note here that since I used a list, I first tried to use the fme:get-list-attribute function, but that did not work at all. The correct way to display one of the list items would be something as follows: {fme:get-attribute("_list{39}.Value")}.

Say you would like to use some math functions, like add some of the values together, this is done as follows: {xs:double(fme:get-attribute("_list{69}.Value")) + xs:double(fme:get-attribute("_list{70}.Value"))}. This is called type casting with XQuery. If you want to learn more about XQuery and how to use such functions (like the "xs:double" and "fn:format-number" functions), have a look at https://docs.microsoft.com/en-us/sql/xquery/type-casting-rules-in-xquery

It would most probably be better to limit the number of decimal places displayed. To limit the number of decimal places to two could be done as follows.

{fn:format-number(xs:double(fme:get-attribute("_list{69}.Value")) + xs:double(fme:get-attribute("_list{70}.Value")),'0.00')}

This however may make your HTML code a bit hard to read and maintain, so perhaps rather use an AttributeRounder transformer before your ListBuilder transformer to limit the number of decimal places to two (which is what I eventually did).

Below is a picture of the transformers used to do what I needed.

These steps did what I needed to create my dashboard.Perhaps there is an easier way to do this. I had to do quite a bit of searching the Internet to find all the pieces of the puzzle to do this. But perhaps there are much better ways to do this. If you know of such a method, please post your solution here as well. Also, if what I said was confusing and I need to give more information, let me know.

What is nice here for me is that I am now replacing a 1,800+ lines of coded dashboard with a couple of transformers in FME. This will definitely improve maintenance in the long run.

10 replies

redgeographics
Rank
Userlevel 5
Badge +25

Thanks for sharing! I've ran into some of the same issues with the XMLTemplater not handling HTML nicely.

fbinneman
Rank

Thanks for sharing! I've ran into some of the same issues with the XMLTemplater not handling HTML nicely.

Thanks to you too. I saw some of your replies on other posts that I could use again regarding this.

 

 

takashi
Rank
Userlevel 2
Badge +17

Hi @francois_binnem, it's true that there are many cases where an HTML document cannot be parsed with XML tools since the HTML specifications are lenient than pure XML, but in my understanding, most HTML elements can also be written with syntax strictly conformed to XML specifications. In this case, for example, you can create an HTML <table> element from the list attribute (_list{}.Name, _list{}.Value) using an XMLTemplater with this expression.

<table>
  <thead>
    <th>Name</th>
    <th>Value</th>
  </thead>
  <tbody>{
    let $values := fme:get-list-attribute("_list{}.Value")
    for $name at $i in fme:get-list-attribute("_list{}.Name")
    return
    <tr>
      <td>{$name}</td>
      <td>{fn:format-number(xs:double($values[$i]), "0.00")}</td>
    </tr>
  }</tbody>
</table>

In addition, if you use a sub expression in the XMLTemplater, it would not be essential to create the list attribute 

 

0684Q00000ArKGSQA3.png

ROOT Expression:

<table>
  <thead>
    <th>Name</th>
    <th>Value</th>
  </thead>
  <tbody>{fme:process-features("SUB")}</tbody>
</table>

SUB Expression:

<tr>
  <td>{fme:get-attribute("Name")}</td>
  <td>{fn:format-number(xs:double(fme:get-attribute("Value")), "0.00")}</td>
</tr>

In both cases, you can add other HTML element before and after the table element, in the template expression or a subsequent transformer such as StringConcatenator.

Honestly I'm unclear what issues are there. Am I missing something?

fbinneman
Rank

Hi @francois_binnem, it's true that there are many cases where an HTML document cannot be parsed with XML tools since the HTML specifications are lenient than pure XML, but in my understanding, most HTML elements can also be written with syntax strictly conformed to XML specifications. In this case, for example, you can create an HTML <table> element from the list attribute (_list{}.Name, _list{}.Value) using an XMLTemplater with this expression.

<table>
  <thead>
    <th>Name</th>
    <th>Value</th>
  </thead>
  <tbody>{
    let $values := fme:get-list-attribute("_list{}.Value")
    for $name at $i in fme:get-list-attribute("_list{}.Name")
    return
    <tr>
      <td>{$name}</td>
      <td>{fn:format-number(xs:double($values[$i]), "0.00")}</td>
    </tr>
  }</tbody>
</table>

In addition, if you use a sub expression in the XMLTemplater, it would not be essential to create the list attribute 

 

0684Q00000ArKGSQA3.png

ROOT Expression:

<table>
  <thead>
    <th>Name</th>
    <th>Value</th>
  </thead>
  <tbody>{fme:process-features("SUB")}</tbody>
</table>

SUB Expression:

<tr>
  <td>{fme:get-attribute("Name")}</td>
  <td>{fn:format-number(xs:double(fme:get-attribute("Value")), "0.00")}</td>
</tr>

In both cases, you can add other HTML element before and after the table element, in the template expression or a subsequent transformer such as StringConcatenator.

Honestly I'm unclear what issues are there. Am I missing something?

Hi Takashi

 

 

Thanks for the advice on how to use the fme:get-list-attribute function. My HTML code is very similar to this, but with using the fme:get-attribute function. 

 

 

With regards to HTML parsing: I would also have thought that HTML should contain open and closing tags to conform to XML rules, but found that some statements in the HTML file, such as CSS style declarations and and when working with images in tables caused the XML parser to produce errors on HTML that displays perfectly well in browsers. Commenting out such HTML code and then just removing the comments afterwards saves me the time in trying to figure out how to possilbly write the HTML differently.

 

 

With regards to the sub expression of the XMLTemplater - I could not find a way to reference the incoming features in an order that I wanted so that I can display it in the HTML tables where each feature needs to go. The ListBuilder indexes these features, making this possible.

 

 

takashi
Rank
Userlevel 2
Badge +17

Hi @francois_binnem, it's true that there are many cases where an HTML document cannot be parsed with XML tools since the HTML specifications are lenient than pure XML, but in my understanding, most HTML elements can also be written with syntax strictly conformed to XML specifications. In this case, for example, you can create an HTML <table> element from the list attribute (_list{}.Name, _list{}.Value) using an XMLTemplater with this expression.

<table>
  <thead>
    <th>Name</th>
    <th>Value</th>
  </thead>
  <tbody>{
    let $values := fme:get-list-attribute("_list{}.Value")
    for $name at $i in fme:get-list-attribute("_list{}.Name")
    return
    <tr>
      <td>{$name}</td>
      <td>{fn:format-number(xs:double($values[$i]), "0.00")}</td>
    </tr>
  }</tbody>
</table>

In addition, if you use a sub expression in the XMLTemplater, it would not be essential to create the list attribute 

 

0684Q00000ArKGSQA3.png

ROOT Expression:

<table>
  <thead>
    <th>Name</th>
    <th>Value</th>
  </thead>
  <tbody>{fme:process-features("SUB")}</tbody>
</table>

SUB Expression:

<tr>
  <td>{fme:get-attribute("Name")}</td>
  <td>{fn:format-number(xs:double(fme:get-attribute("Value")), "0.00")}</td>
</tr>

In both cases, you can add other HTML element before and after the table element, in the template expression or a subsequent transformer such as StringConcatenator.

Honestly I'm unclear what issues are there. Am I missing something?

As far as I know, there is no HTML element which should not be closed. You can just close every element if you want to write them within an XQuery expression for the XMLTemplater.

 

This is a valid XML, and also a web browser interprets it as a valid HTML. 

 

<html>
  <head>
    <meta charset="utf-8" />
    <link rel="stylesheet" type="text/css" href="./css/common.css" />
    <title>Title</title>
  </head>
  <body>
    <h1>Title</h1>
    <hr />
    <p>foo<br />bar</p>
    <p><img src="./img/foobar.png" /></p>
  </body>
</html>
Am I wrong?
fbinneman
Rank
As far as I know, there is no HTML element which should not be closed. You can just close every element if you want to write them within an XQuery expression for the XMLTemplater.

 

This is a valid XML, and also a web browser interprets it as a valid HTML. 

 

<html>
  <head>
    <meta charset="utf-8" />
    <link rel="stylesheet" type="text/css" href="./css/common.css" />
    <title>Title</title>
  </head>
  <body>
    <h1>Title</h1>
    <hr />
    <p>foo<br />bar</p>
    <p><img src="./img/foobar.png" /></p>
  </body>
</html>
Am I wrong?
Thanks Takashi

 

 

Your example showed me that I did not properly close my images with the HTML tags, although it displays correct in a browser. The following code however do not want to compile, perhaps something else I am not closing properly?

 

 

<style type="text/css">

 

value {   font-size:13px;

 

 color:blue;

 

 font-weight:bold;

 

} </style>

 

 

It does not really matter that much to me, as I can generate the HTML that I need by commenting this out as with my example, although it would be more correct to have the code to parse correctly in the XMLTemplater in the first place.

 

 

 

takashi
Rank
Userlevel 2
Badge +17
As far as I know, there is no HTML element which should not be closed. You can just close every element if you want to write them within an XQuery expression for the XMLTemplater.

 

This is a valid XML, and also a web browser interprets it as a valid HTML. 

 

<html>
  <head>
    <meta charset="utf-8" />
    <link rel="stylesheet" type="text/css" href="./css/common.css" />
    <title>Title</title>
  </head>
  <body>
    <h1>Title</h1>
    <hr />
    <p>foo<br />bar</p>
    <p><img src="./img/foobar.png" /></p>
  </body>
</html>
Am I wrong?
It's not an issue regarding HTML/XML syntax. In XQuery language, the curly brackets are special characters to denote enclosed expressions. The XQuery processor tries to interpret the text within { }, but naturally it fails since the CSS statements are not valid XQuery expressions.

 

You can just escape them by adding one more same bracket when you need to write them literally in an XQuery expression.

 

<style type="text/css"> 
value {{
font-size:13px; 
color:blue; 
font-weight:bold; 
}} </style>

 

fbinneman
Rank
It's not an issue regarding HTML/XML syntax. In XQuery language, the curly brackets are special characters to denote enclosed expressions. The XQuery processor tries to interpret the text within { }, but naturally it fails since the CSS statements are not valid XQuery expressions.

 

You can just escape them by adding one more same bracket when you need to write them literally in an XQuery expression.

 

<style type="text/css"> 
value {{
font-size:13px; 
color:blue; 
font-weight:bold; 
}} </style>

 

Thanks for this. Now I do not need the StringReplacer transformers at the end any longer.

 

 

M
Badge

i have an issue that seems really similar to the above, but I just can't get it to work and was hoping someone may be able to help? My goal is to populate a HTML table with the values, but colour the background colour depending on the value of PRIORITY_CODE (ie A1-A5).

I've used the XML Templater, the ROOT template looking like...

and the SUB looks like this...

Now the ROOT seems to go through but it gets caught up on the SUB first curly bracket, which is trying to provide the PRIORITY_CODE by which to set the class colour.

Would anyone by kind enough to offer some suggestions, as I am stuck?

Thanks heaps

Matt

takashi
Rank
Userlevel 2
Badge +17

i have an issue that seems really similar to the above, but I just can't get it to work and was hoping someone may be able to help? My goal is to populate a HTML table with the values, but colour the background colour depending on the value of PRIORITY_CODE (ie A1-A5).

I've used the XML Templater, the ROOT template looking like...

0684Q00000ArKBxQAN.png

and the SUB looks like this...

0684Q00000ArK87QAF.png

Now the ROOT seems to go through but it gets caught up on the SUB first curly bracket, which is trying to provide the PRIORITY_CODE by which to set the class colour.

Would anyone by kind enough to offer some suggestions, as I am stuck?

Thanks heaps

Matt

I found two wrong points in the SUB expression

  1. XML attribute value should be quoted by double quotation marks.
  2. XML element should end with exactly same tag as its beginning tag.
<tr class="{fme:get-attribute("PRIORITY_CODE")}">
    ....
</tr> 

Reply


Community Stats

28,692
Posts
109,432
Replies
36,542
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.