PL/SQL Script to Generate XML Tags for XMLP Report(How to Display Leading Zeros in XMLP Report – Excel Output)

There are many ways to generate output in XML tags format, dbms_xmlgen is one of the way to generate.

Lets consider an example to display EMP table output in XMLP report in excel format, below is the scrip to generate xml tags

DECLARE -- --Cursor to fetch the data -- CURSOR data_cur IS -- SELECT empno,ename,job,hiredate,sal FROM emp; -- output_row data_cur%rowtype; BEGIN -- -- dbms_output.put_line('<?xml version="1.0" encoding="US-ASCII" standalone="no"?>'); fnd_file.put_line(fnd_file.output,'<?xml version="1.0" encoding="US-ASCII" standalone="no"?>'); dbms_output.put_line('<OUTPUT>'); fnd_file.put_line(fnd_file.output,'<OUTPUT>'); -- OPEN data_cur; LOOP -- FETCH data_cur INTO output_row; EXIT WHEN data_cur%notfound; -- dbms_output.put_line('<ROW>'); fnd_file.put_line(fnd_file.output,'<ROW>'); -- dbms_output.put_line('<ENUM>'||dbms_xmlgen.CONVERT(output_row.empno)||'</ENUM>'); fnd_file.put_line(fnd_file.output,'<ENUM>'||dbms_xmlgen.CONVERT(output_row.empno)||'</ENUM>'); -- dbms_output.put_line('<ENAME>'||dbms_xmlgen.CONVERT(output_row.ename )||'</ENAME>'); fnd_file.put_line(fnd_file.output,'<ENAME>'||dbms_xmlgen.CONVERT(output_row.ename )||'</ENAME>'); -- dbms_output.put_line('<JOB>'||dbms_xmlgen.CONVERT(output_row.job )||'</JOB>'); fnd_file.put_line(fnd_file.output,'<JOB>'||dbms_xmlgen.CONVERT(output_row.job )||'</JOB>'); -- dbms_output.put_line('<HIRE_DATE>'||dbms_xmlgen.CONVERT(output_row.hiredate )||'</HIRE_DATE>'); fnd_file.put_line(fnd_file.output,'<HIRE_DATE>'||dbms_xmlgen.CONVERT(output_row.hiredate )||'</HIRE_DATE>'); -- dbms_output.put_line('<SAL>'||dbms_xmlgen.CONVERT(output_row.sal )||'</SAL>'); fnd_file.put_line(fnd_file.output,'<SAL>'||dbms_xmlgen.CONVERT(output_row.sal )||'</SAL>'); -- dbms_output.put_line('</ROW>'); fnd_file.put_line(fnd_file.output,'</ROW>'); -- END LOOP; CLOSE data_cur; -- dbms_output.put_line('</OUTPUT>'); fnd_file.put_line(fnd_file.output,'</OUTPUT>'); -- END; /

below is the generated output in XML tags

<?xml version="1.0" encoding="US-ASCII" standalone="no"?> <OUTPUT> <ROW> <ENUM>007369</ENUM> <ENAME>SMITH</ENAME> <JOB>CLERK</JOB> <HIRE_DATE>17-12-80</HIRE_DATE> <SAL>800</SAL> </ROW> <ROW> <ENUM>007499</ENUM> <ENAME>ALLEN</ENAME> <JOB>SALESMAN</JOB> <HIRE_DATE>20-02-81</HIRE_DATE> <SAL>1600</SAL> </ROW> <ROW> <ENUM>007521</ENUM> <ENAME>WARD</ENAME> <JOB>SALESMAN</JOB> <HIRE_DATE>22-02-81</HIRE_DATE> <SAL>1250</SAL> </ROW> <ROW> <ENUM>7566</ENUM> <ENAME>JONES</ENAME> <JOB>MANAGER</JOB> <HIRE_DATE>02-04-81</HIRE_DATE> <SAL>2975</SAL> </ROW> </OUTPUT>

Below is the screenshot of the output layout (XMLP Template)

Below is the screen shot of output generated

You might have observed that the xml content has employee number with 00 as prefix but the excel output is not showing the 00 prefix, to know why and how to resolve this.

Microsoft Excel has a tendency to display number format based columns with no prefix of ZEROs. For example, if there is a value ’007′ excel displays it as ’7′, excel display it as ’007′ only if the column is set in text format. You can see the difference in the below screenshot

Please go through the below URL for an example to generate an XMLP report:

In the example mentioned in the above URL you could see the output as shown in the below screenshot

But if you observe the data in the emp table, you could see three rows have leading zeros for employee number( Please note, I have updated the data of seeded emp table with prefix of 00 and also changed the data type of empno column to varchar2(6) for the sake of example.)

As the output is of excel type the empno column is considered as Number column (as the entire column consists of number format data) and the leading zeros are removed in display.

We have multiple ways to resolve this, I have listed them below.

Method 1

1. Open the RTF Template in MS Word.
2. Go to Data -> Load XML Data.
3. Once the data is loaded successfully, double click on that field.
4. Under field properties window set the field formatting type as “Regular Text” and set the check box “Force LTR”.

After following the above said steps, save the template and preview the output which is as shown below

Method 2

Add Ctrl+Shift+Space after/before emplyee number on template to create a non-breaking space. The non-breakable space converts the number column to string column

Disadvantage of this method is that the non-breakable space is visible in the output as well, you can see in the screenshot below

Method 3

This is the best method I have found, In this method we need to use an equal-to symbol before the field and enclose the field in double quotes for example:- =”ENUM”

This works only in excel however it will allow you to cut and paste (ie to use the value to search in Oracle) and also to do vlookups.

The theory is that excel will concatenate the values together because it has quotes around it, it will treat it as a string rather than simply a value.

For any other formats this method will not work.

You can see in the above screenshot that Enum column values are turned to blue color as the content is explicit converted to text format from number format.

Hope this article is useful for those who are in need to show leading zeros for number columns in XMLP reports. If you have any best solution, please leave a comment and share with the readers.