Excel Output From BI Publisher or XML Publisher is Trimming Leading Zeros

Symptoms
Created an XML Publisher report with Excel output.  When numeric data includes leading zeros, these are dropped in the output e.g. 00123 becomes 123.

Example source XML: 
<root> 
<invoices> 
<invoice> 
<invoice_number>0000123</invoice_number> 
</invoice> 
<invoice> 
</root>

Cause
Excel automatically trims leading and trailing zeros when in what it sees as numeric data.
To keep leading or trailing zeros where the data is not numeric but actually a string,
such as an invoice number, Excel has to see the numbers as a string value not numeric.

Solution

1. In the rtf template, use the “Insert -> Symbol” function to insert a very small
0xA0 (NO-BREAK SPACE) character (font size 1 or 2pt) in front of the number string field.

2. One can also try:

3. Another option:
During XML template design, Instead of the using the tag < ?TAG_NAME ? >
use  ="<?TAG_NAME?>". It wraps the value with double quotes. Now Excel treat value as character and doesn't trim the leading zeros of value.
Note: space between < and ? and tag name is not required.

4. Retest for the issue.

3. BI Publisher Cache Directory Filling Up and Pointing to Invalid Directory Instead of system-tmp-dir Parameter

Symptoms
Temporary and cache files are written to .../j2ee/home/applications/xmlpserver/xmlpserver/xdo/cache but not to the path configured in the file  web.xml file:

<param-name>system-tmp-dir</param-name> <param-value>/servers/bipublisher/XMLP/Cache</param-value> So no matter where has been set the temp directory, it still writes to the cache location. As a result when a report is submitted the file system is filling up.

Cause
The cause of this problem has been identified in unpublishedBug 9560562: BI PUBLISHER CACHE DIRECTORY FILLING UP AND POINTING TO INVALID DIRECTORY.

Fix for unpublished Bug 9560562 has been included in Patch 9791839 : UPDATE FOR BI PUBLISHER ENTERPRISE 10.1.3.4.1 JUNE 2010.

With this fix, Administrator can define System Temporary Directory from Admin->Server Configuration and BIP uses this directory for caching

Solution
1. Please apply the Patch 9791839 

2. Set the temporary directory in Admin->Server Configuration 

3. Bounce the services .

4. Then test again.