Greetings,

Consider this code...

SQL
select distinct FIELDNAME
from SQL_MFD
order by FIELDNAME;
END

This works in a .fex because the SQL_MFD is a Master File that points to SQL tables. I like this method because it can return data faster than a TABLE request.

Obviously I can't use ON TABLE PCHOLD FORMAT xxx because this isn't a TABLE request.

My goal is to output the result set to XML but it keeps coming up as an HTML table.

Anybody know how I can set the format of this data WITHOUT holding it first?

Thanks!

Dan

This message has been edited. Last edited by: Dan Pinault,

Something like this:

FILEDEF H001 DISK TEMP/H001.XML
-RUN

SQL
SELECT
APPL_PURP_CD, APPL_PURP_ENG_DS
FROM
BSLC_IB_APPL_PURP_C
ORDER BY APPL_PURP_CD;
TABLE
ON TABLE HOLD AS H001 FORMAT XML
END
-RUN

Excellent! Thanks Francis.

This is what I ended up with and it works great.

SQL
select distinct FIELDNAME
from SQL_MFD
order by FIELDNAME;
TABLE
ON TABLE PCHOLD FORMAT XML
END

That's really not buying you much because the internal matrix still needs to be created in order to output to xml format. And you're not using SQL passthru, either. I'm guessing that

TABLE FILE SQL_MFD
SUM FIELDNAME NOPRINT
BY FIELDNAME
ON TABLE PCHOLD FORMAT XML
END

is going to be just as fast.

Dan,

I'm curious as to what the behaviour of WebFOCUS is when you use PCHOLD instead of HOLD.

On my PC, a "File Download - Security Warning" pops up with a random file name and unknown file type. When I click on the Save button, the "File name" field has that random name and the "Save as type" field has ".document". Not very intuitive for non technical users.

And the file really isn't XML. When I use HOLD FORMAT XML, it does create an XML file on the server.

I wonder if I have to change something somewhere for the PCHOLD to work.

PCHOLD is kind of confusing as you would think that something else should happen, but it really is just returning the data to the browser just like ON TABLE SET ONLINE-FMT XML.
I do not get the error you describe, but my browser also know what a .XML file is. If I clikc on the Save As, it wants to save as a text document, which it really is, but the content of the text document is what makes it an XML file. So you don't need to change anything for the PCHOLD to work. I only use PCHOLD when I'm using REMOTE BEGIN/END, because it returns the data from the remote server back to the local server for further processing.

Francis,

If I run the procedure from a .fex the XML file contents are displayed in my browser. The reason I am using PCHOLD FORMAT XML is that I using this code to populate a listbox in an html page. And it works great!

Dan

Aha! In this case, the PCHOLD format XML is required because it is doing something similar in that the data is being returned (internally) to the server so that it can be used for further processing (use in the drop-down box). Glad it's working for you.

OK, so, I've changed the filetype for XML to be opened by Internet Explorer. Now when I PCHOLD an XML file, it opens in IE. How does one use the PCHOLD result in a list box, if the file opens in a web browser window? I thought you would have to HOLD the file on the server and then build the HTML file before sending it to the web browser.

I am very curious about this.

Thanks,

In the HTML Painter, when you select a procedure to dynamically populate the control, that procedure must include ON TABLE PCHOLD FORMAT XML. The code that is added to the HTML page does the rest of the magic.

Ah yes, that magical "HTML Painter" - I must look into it some day...