Welcome Guest! Log in
×

Notice

The forum is in read only mode.
Due to some maintenance operations, stambia.org will be switched to read-only mode during the 13th November. It will be possible to read and download, but impossible to post on the forums or create new accounts. For any question please contact the support team.

Topic-icon Question error Load : XML file with '&'

More
11 Jan 2017 11:22 #1 by asma
asma created the topic: error Load : XML file with '&'
Hello,

I have to load a sql server table with xml file wish contains an ampersand '&'. I have the following error :
Engine Exception : The entity name must immediately follow the '&' in the entity reference.

I have to replace the '&' in the xml by & or 'ET'. i use a stage putting the replace function replace : replace(XML.XML/PLIS/PLI/DOCUMENTS/DOCUMENT/NAME_CLIENT;'&';'ET')
but it does not work.

Could you plz help me or tell me if i could do that with stambia? if any one know?

thanks a lot
Asma
More
11 Jan 2017 14:26 - 11 Jan 2017 14:27 #2 by Vincent
Vincent replied the topic: error Load : XML file with '&'
Hello,

If you have access and can modify your XML file, & is a special character in XML and needs to be escaped with &
So when the Xml file is read by stambia it will be replaced by & in the elements.
If not we will have to find a way to do it with Stambia, I see several solutions, like file transformation scripting, staging, ...

Let me know !
Last Edit: 11 Jan 2017 14:27 by Vincent.
More
12 Jan 2017 10:54 #3 by asma
asma replied the topic: error Load : XML file with '&'
Hello Vincent,
thank you for your reply!
Indeed it's the case..i got to replace the & by the & or just "ET' but automatically with stambia not manually
I actually use the stage to transform the field content..but i have the same error.

I' ll try a scripting, maybe it can resolve the problem.
if it possible, Do you plz have an example of file transformation scripting?
thanks a lot!

Asma
More
12 Jan 2017 16:46 - 12 Jan 2017 16:46 #4 by Vincent
Vincent replied the topic: error Load : XML file with '&'
You're welcome !

Indeed, you will not be able to use a stage because what causes the error is the XML Parser, and it is executed before the stage functions !
I can give you an exemple of transformation scripting (I will write an article about it) but first I need to know if your file is huge, and wether it contains New Lines separators or not.
Last Edit: 12 Jan 2017 16:46 by Vincent.
More
13 Jan 2017 11:45 #5 by asma
asma replied the topic: error Load : XML file with '&'
Thanks again for your reply :-)

my XML does not contain new lines separator and it's not a huge file
bellow an example of my xml

<?xml version="1.0" encoding="utf-8"?>
<LOT>
<DATA_LOT>
<DAT_NUM>05/01/2017</DAT_NUM>
<DAT_RECEP>05/01/2017</DAT_RECEP>
<TYPE_LOT>COUR</TYPE_LOT>
<ID_LOT>COURRI</ID_LOT>
<ID_ARCH>02345/17/01/05/COURRIER</ID_ARCH>
<CANAL>YYYYYY</CANAL>
</DATA_LOT>
<PLIS>
<PLI>
<ID_PLI>1</ID_PLI>
<DOCUMENTS>
<DOCUMENT>
<ID_CLIENT>00000</ID_CLIENT>
<ID_POLICE>XXXXX</ID_POLICE>
<PRODUIT>AAAAA</PRODUIT>
<ID_SINISTRE></ID_SINISTRE>
<NAT_DOC>XXXXXXX</NAT_DOC>
<DAT_DOC>05/12/2016</DAT_DOC>
<NAME_FILE>00000551.pdf</NAME_FILE>
<ID_ENDO></ID_ENDO>
<NAME_CLIENT>ABC & DYZ</NAME_CLIENT>
<SERVICE>GGG</SERVICE>
<TAG></TAG>
<DEST></DEST>
</DOCUMENT>
</DOCUMENTS>
</PLI>
</PLIS>
</LOT>


Asma
More
13 Jan 2017 14:59 - 13 Jan 2017 15:00 #6 by Vincent
Vincent replied the topic: error Load : XML file with '&'
Hello,

Here is the article about transformation scripting.

What you can do is create a File metadata with a unique huge field corresponding to the size of your Xml File. All the content will be in this field. Then you can use a simple transformation Line code :
__string__.replaceAll("&","&amp;");

This will replace and escape all the ampersands. What you can do next is to use this transformed file as a source for a new file, and reverse the new file in a XML metadata that you will use as a source for your load.

Hope it helps. Though, you should contact your Xml provider, since it is not recommended to work with badly encoded Xml files.
Last Edit: 13 Jan 2017 15:00 by Vincent.
More
13 Jan 2017 16:27 #7 by asma
asma replied the topic: error Load : XML file with '&'
I ll try to apply your suggestion and i ll come back to you
Btw, thank you so much for your help!

Asma
More
18 Jan 2017 10:12 #8 by asma
asma replied the topic: error Load : XML file with '&'
Hello Vincent,

I tried to put the content of the xml in a field of a csv file..but
when i use the Action process Integration FILE (fiel export) : 'tstxmltofile/I1-FILETST-Integration/SQLFileExport/SQL_EXP_JAVA_DRIVER' is null !

Do you have plz any idea about how i can do that or resolve it?
thanks a lot!
Asma
More
18 Jan 2017 10:41 - 19 Jan 2017 11:34 #9 by risson
risson replied the topic: error Load : XML file with '&'
hello. maybe your mapping is missing a stage.

There is another solution you can try : a scripting action which takes your invalid xml file and creates a valid xml file from it.

Sample rhino script :
inFilePath = new String("d:/temp/invalid.xml");
outFilePath = new String("d:/temp/valid.xml");
delim = new String(">");

bw = new java.io.BufferedWriter(new java.io.FileWriter(outFilePath));

scan = new java.util.Scanner(new java.io.File(inFilePath));
scan.useDelimiter(java.util.regex.Pattern.compile(delim));

while (scan.hasNext()) {
    sb = new java.lang.StringBuilder();

    sb.append(scan.next());
    sb.append(delim);
    
    out = sb.toString().replaceAll("&(?![#a-zA-Z0-9]+;)", "&amp;");
    bw.write(out);
	
}
bw.close();
scan.close();


Simply include this in a process and define process parameters for inFilePath and outFilePath.
I hope this helps.

[edit] I add a sample process which packages this script.

This message has an attachment file.
Please log in or register to see it.

Last Edit: 19 Jan 2017 11:34 by risson.
More
18 Jan 2017 10:56 #10 by asma
asma replied the topic: error Load : XML file with '&'
thank you so much Vincent!