Transforming results from execSQL

, ,
1

Hi all - How do I transform the “results” record list that a pub.db:execSQL returns to a different format? Let’s say the SQL I run is “select * from employee” and it returns records with the person’s first, middle, and last name as such:

Jonathan Douglas Smith
William Thomas Jones
Howard Michael Gordon

The format I’d like to convert this to is records with all first names, all middle names, and all last names as such:

Jonathan William Howard
Douglas Thomas Michael
Smith Jones Gordon

Needless to say, the SQL example I gave is an overly simplified query. If this was always the case, I’d use the the pub.db:query and pub.table:recordListToTable built-in services. I just wanted to see if there is a more elegant solution that I was missing.

Cheers!
Rajesh

2

I use the XSLT module and an XSL stylesheet to transform from one structure to another.

It’s fast even when there are thousands of records.

3

Another option would be to use queryDocument.

I find XQL to be very readable, and it greatly simplifies complex looping and branching logic.

I’m sure there are tradeoffs between using XSLT vs queryDocument.

If anyone has some opinons - I would be interested to know what you think.

Is one clearly faster than another in certain circumstances?

HAL

4

Regarding Tony’s XSLT remarks…

The XSLT Module is not available as part of the standard webMethods installation. You will need to visit the Advantage Web site’s Software Download Center to get it.

If the XSLT module is not included in the Available Software list, contact the Keymaster at keymaster@webmethods.com. The Keymaster can “release” the module to you.