Parsing XML from Database

drewsheez

Hi!

I am currently supporting an application that stores results as XML in the database. We are trying to create a report that will display the results, but the XML can be confusing to the average user. I am wondering if there is any way to traverse through a queried result and either filter our characters or easily map the data to something else.

I currently have a few areas of the report mapped correctly so that a result of:

"<selectedItems><selectedItem><code>No</code><value>No</value></selectedItem></selectedItems>"
or "<selectedItems><selectedItem><code>Yes</code><value>Yes</value></selectedItem></selectedItems>"

displays:

"No"
or "Yes"

... respectively. However, the problem lies with fields that display multiple results. Some of the results are using checkboxes where the user has the ability to select multiple values, so the results in the database could be saved as multiple values, such as:

"<selectedItems><selectedItem><code>Chf</code><value>CHF</value></selectedItem><selectedItem><code>Copd</code><value>COPD</value></selectedItem></selectedItems>"
"<selectedItems><selectedItem><code>Copd</code><value>COPD</value></selectedItem><selectedItem><code>Dibete</code><value>Diabetes</value></selectedItem></selectedItems>"
"<selectedItems><selectedItem><code>Adlast</code><value>ADL Assistance</value></selectedItem></selectedItems>"

where the results should display:

"CHF, COPD"
"COPD, Diabetes"
"ADL Assistance"

... respectively. The results are not limited to the above, but these examples should provide enough information. Upon further analysis of the database, it seems that these results, if selected, are saved in logical order according to how they are displayed in the application.

So on to my questions:

Is Birt smart enough to handle multipe mappings in one result? Such as if I were to map "<selectedItem><code>Chf</code><value>CHF</value></selectedItem>" to "CHF " and "<selectedItem><code>Copd</code><value>COPD</value></selectedItem>" to "COPD ", would a result of "<selectedItems><selectedItem><code>Chf</code><value>CHF</value></selectedItem><selectedItem><code>Copd</code><value>COPD</value></selectedItem></selectedItems>" display "CHF COPD "?

If this cannot be done, would it be best to go through and map every single possiblity? If this is the best approach, how many mappings are able to be configured without impacting performance? Since we have upwards of 15-20 possible results, there would be numerous mappings.

Thank you in advance for any information!

Drew

mwilliams

Hi Drew,

If you use a dynamic text box rather than a data element to display your xml, it may already do what you're wanting to do. Let me know.