Question

IFS Cloud - XSL XML to Text transformer


Userlevel 6
Badge +12

Hi,
I tried to create XSLT that converts XML into TXT, it works ok in Developer Studio, but after deploy into IFS Cloud 22R1 app, it does not work as expected.
All new lines was removed.
I tried multiple solutions, all failed.

Is here some recommended way how to achieve this via XSL? Or it is BUG? Or TXT output is not allowed in IFS for some reason?

Source XML:

<?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<gesmes:subject>Reference rates</gesmes:subject>
<gesmes:Sender>
<gesmes:name>European Central Bank</gesmes:name>
</gesmes:Sender>
<Cube>
<Cube time='2022-05-11'>
<Cube currency='USD' rate='1.0553'/>
<Cube currency='JPY' rate='137.07'/>
<Cube currency='BGN' rate='1.9558'/>
<Cube currency='CZK' rate='25.365'/>
<Cube currency='DKK' rate='7.4393'/>
<Cube currency='GBP' rate='0.85393'/>
<Cube currency='HUF' rate='379.13'/>
<Cube currency='PLN' rate='4.6575'/>
<Cube currency='RON' rate='4.9470'/>
<Cube currency='SEK' rate='10.5260'/>
<Cube currency='CHF' rate='1.0446'/>
<Cube currency='ISK' rate='139.30'/>
<Cube currency='NOK' rate='10.1793'/>
<Cube currency='HRK' rate='7.5365'/>
<Cube currency='TRY' rate='16.1851'/>
<Cube currency='AUD' rate='1.5055'/>
<Cube currency='BRL' rate='5.3859'/>
<Cube currency='CAD' rate='1.3685'/>
<Cube currency='CNY' rate='7.0893'/>
<Cube currency='HKD' rate='8.2839'/>
<Cube currency='IDR' rate='15308.87'/>
<Cube currency='ILS' rate='3.6148'/>
<Cube currency='INR' rate='81.4935'/>
<Cube currency='KRW' rate='1343.99'/>
<Cube currency='MXN' rate='21.3870'/>
<Cube currency='MYR' rate='4.6185'/>
<Cube currency='NZD' rate='1.6645'/>
<Cube currency='PHP' rate='54.992'/>
<Cube currency='SGD' rate='1.4622'/>
<Cube currency='THB' rate='36.492'/>
<Cube currency='ZAR' rate='16.9275'/>
</Cube>
</Cube>
</gesmes:Envelope>

XSLT 1):
 

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns:a="http://www.ecb.int/vocabulary/2002-08-01/eurofxref">
<xsl:output method="text" indent="yes"/>

<xsl:template match="/">
<xsl:apply-templates select="/gesmes:Envelope/a:Cube/a:Cube" />
</xsl:template>

<xsl:template match="/gesmes:Envelope/a:Cube/a:Cube">
<xsl:variable name="date" select="@time" />

<xsl:for-each select="a:Cube">
<xsl:value-of select="concat(@currency, ';', @rate, ';', $date, ' ')" />
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>


Result in Developer Studio (and wanted result):

USD;1.0553;2022-05-11
JPY;137.07;2022-05-11
BGN;1.9558;2022-05-11
CZK;25.365;2022-05-11
DKK;7.4393;2022-05-11
GBP;0.85393;2022-05-11
HUF;379.13;2022-05-11
PLN;4.6575;2022-05-11
RON;4.9470;2022-05-11
SEK;10.5260;2022-05-11
CHF;1.0446;2022-05-11
ISK;139.30;2022-05-11
NOK;10.1793;2022-05-11
HRK;7.5365;2022-05-11
TRY;16.1851;2022-05-11
AUD;1.5055;2022-05-11
BRL;5.3859;2022-05-11
CAD;1.3685;2022-05-11
CNY;7.0893;2022-05-11
HKD;8.2839;2022-05-11
IDR;15308.87;2022-05-11
ILS;3.6148;2022-05-11
INR;81.4935;2022-05-11
KRW;1343.99;2022-05-11
MXN;21.3870;2022-05-11
MYR;4.6185;2022-05-11
NZD;1.6645;2022-05-11
PHP;54.992;2022-05-11
SGD;1.4622;2022-05-11
THB;36.492;2022-05-11
ZAR;16.9275;2022-05-11


Result in IFS Cloud 22R1:
 



XSLT 2):
 

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns:a="http://www.ecb.int/vocabulary/2002-08-01/eurofxref" xmlns:xalan="http://xml.apache.org/xalan">
<xsl:output encoding="utf-8" method="text" indent="no" omit-xml-declaration="yes" xml:space="default" xalan:line-separator="&#xD;&#xA;"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/">
<xsl:apply-templates select="/gesmes:Envelope/a:Cube/a:Cube"/>
</xsl:template>
<xsl:template match="/gesmes:Envelope/a:Cube/a:Cube">
<xsl:variable name="date" select="@time"/>
<xsl:for-each select="a:Cube">
<xsl:value-of select="concat(@currency, ';', @rate, ';', $date)"/>
<xsl:text>&#xA;</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>


XSLT 3):
​​​​​​​

<?xml version="1.0"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01" xmlns:a="http://www.ecb.int/vocabulary/2002-08-01/eurofxref" xmlns:xalan="http://xml.apache.org/xalan">
<xsl:output encoding="utf-8" method="text" indent="no" omit-xml-declaration="yes" xml:space="default" xalan:line-separator="&#xD;&#xA;"/>
<xsl:template match="/">
<xsl:apply-templates select="/gesmes:Envelope/a:Cube/a:Cube"/>
</xsl:template>
<xsl:template match="/gesmes:Envelope/a:Cube/a:Cube">
<xsl:variable name="date" select="@time"/>
<xsl:for-each select="a:Cube">
<xsl:value-of select="concat(@currency, ';', @rate, ';', $date)"/>
<xsl:text>&#xD;&#xA;</xsl:text>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>


Thanks
​​​​​​​BR​​​​​​​


10 replies

Userlevel 6
Badge +12

I did it via Java transformer with use of that XSL and it works this way, so it means there is definitely some Whitespace / NewLine trimming when XSL transformer is used.
 

import ifs.fnd.base.IfsException;
import ifs.fnd.connect.xml.Transformer;
import java.io.ByteArrayInputStream;
import java.io.StringReader;
import java.io.StringWriter;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.transform.Templates;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import javax.xml.transform.stream.StreamSource;
import org.w3c.dom.Document;
import org.xml.sax.InputSource;

public class CEcbCurrencyRates implements Transformer {

@Override
public void init() throws IfsException {
}

@Override
public String transform(String sIn) throws IfsException {
String xslt = "<?xml version=\"1.0\"?>\n" +
"<xsl:stylesheet version=\"1.0\" xmlns:xsl=\"http://www.w3.org/1999/XSL/Transform\" xmlns:gesmes=\"http://www.gesmes.org/xml/2002-08-01\" xmlns:a=\"http://www.ecb.int/vocabulary/2002-08-01/eurofxref\" xmlns:xalan=\"http://xml.apache.org/xalan\">\n" +
" <xsl:output encoding=\"utf-8\" method=\"text\" indent=\"no\" omit-xml-declaration=\"yes\" xml:space=\"default\" xalan:line-separator=\"&#xD;&#xA;\"/>\n" +
"\n" +
" <xsl:template match=\"/\">\n" +
" <xsl:apply-templates select=\"/gesmes:Envelope/a:Cube/a:Cube\" />\n" +
" </xsl:template>\n" +
" \n" +
" <xsl:template match=\"/gesmes:Envelope/a:Cube/a:Cube\">\n" +
" <xsl:variable name=\"date\" select=\"@time\" />\n" +
"\n" +
" <xsl:for-each select=\"a:Cube\">\n" +
" <xsl:value-of select=\"concat(@currency, ';', @rate, ';', $date, '&#10;')\" />\n" +
" </xsl:for-each> \n" +
" </xsl:template>\n" +
"</xsl:stylesheet>";

Document input = convertStringToDocument(sIn);
StringWriter sw = new StringWriter();

TransformerFactory factory = TransformerFactory.newInstance();
Templates template;
try {
template = factory.newTemplates(new StreamSource(new ByteArrayInputStream(xslt.getBytes())));
javax.xml.transform.Transformer transformer = template.newTransformer();
transformer.transform(new DOMSource(input), new StreamResult(sw));
} catch (TransformerException ex) {

}
return sw.toString();
}

private static Document convertStringToDocument(String xmlStr) {
DocumentBuilderFactory factory = DocumentBuilderFactory.newInstance();
factory.setNamespaceAware(true);
DocumentBuilder builder;
try {
builder = factory.newDocumentBuilder();
Document doc = builder.parse(new InputSource(new StringReader(xmlStr)));
return doc;
} catch (Exception e) {
}
return null;
}
}


Result in App IFS Cloud 22R1:

 

Userlevel 7
Badge +20

Hi @InfFilipV 

 

I also noticed tat newline/whitespace was removed during processing file with IFS Connect.

I’m just curious about your setup, how are you loading the file to external file using connect?

 

Cheers!

Damith 

 

Userlevel 6
Badge +12

Hi @dsj , 

DECLARE
company_ company_tab.company%TYPE := :company_;
currency_type_ currency_type_tab.currency_type%TYPE := :currency_type_;

param_string_ VARCHAR2(32000) :=
'!PARAMETER_MESSAGE' || CHR(10) ||
'$FILE_TYPE=ExtCurrency' || CHR(10) ||
'$SET_ID=1' || CHR(10) ||
'$FILE_NAME=' || CHR(10) ||
'$COMPANY=' || company_ || CHR(10) ||
'$CLIENT_SERVER=C' || CHR(10) ||
'$FILE_TEMPLATE_ID=STDCURR' || CHR(10) ||
'$FILE_DIRECTION_DB=1' || CHR(10) ||
'$CURRENCY_TYPE=' || currency_type_ || CHR(10) ||
'$BASE_CURRENCY=CZK' || CHR(10) ||
'$RATES_INVERTED=FALSE' || CHR(10) ||
'$CONVERSION_FACTOR_CONSIDERED=N' || CHR(10) ||
'$PARAM_SET=TRUE';
load_file_id_ NUMBER;
file_template_ ext_file_template_tab.file_template_id%TYPE := 'STDCURR';
file_type_ ext_file_type_tab.file_type%TYPE := 'ExtCurrency';
file_name_ VARCHAR2(2000) := 'cnb_rates.txt';
info_ VARCHAR2(32000);

clob_ CLOB;
blob_ BLOB;
BEGIN
Plsqlap_Server_API.Invoke_Outbound_Message(
input_ => clob_,
sender_ => 'IFS',
receiver_ => 'C_CNB_CURRENCY_RATES',
is_json_ => FALSE,
binary_response_ => blob_);

External_File_Utility_API.Create_Load_Id_Param_(
load_file_id_,
param_string_);
External_File_Utility_API.Load_Client_File_(
'EXE',
load_file_id_,
file_template_,
file_type_,
blob_,
company_,
file_name_);
External_File_Utility_API.Unpack_Ext_Line_(
file_type_,
file_template_,
load_file_id_,
company_ );
External_File_Utility_API.Store_File_Identities_(load_file_id_);
External_File_Utility_API.Start_Api_To_Call_(
info_,
load_file_id_);
END;

 

Userlevel 7
Badge +20

This is great!. If the transformer doesn’t work, may be you could add a control characters between two lines and replace it with line feed inside the script?

<xsl:value-of select="concat(@currency, ';', @rate, ';', $date, 'XXXREPLACE' )"/>

 

Cheers!

Damith

Userlevel 6
Badge +12

This is great!. If the transformer doesn’t work, may be you could add a control characters between two lines and replace it with line feed inside the script?

<xsl:value-of select="concat(@currency, ';', @rate, ';', $date, 'XXXREPLACE' )"/>

 

Cheers!

Damith

Good idea  it didn't occur to me. But I prefer clean (reusable) Java solution.

Userlevel 4

I have this kind of solution, what will create csv file so that there is line breaks

Event what will create message

DECLARE
   pick_list_no_ VARCHAR2(300) := '&NEW:PICK_LIST_NO';
   
   l_refcursor SYS_REFCURSOR;
   l_xmltype XMLTYPE;
   bxml_    CLOB;
   
BEGIN
   OPEN l_refcursor FOR 
    select 
    '2' ||';' 
    || pick_list_no || ';'
    || Source_ref2 || ';'
    || Source_ref3 || ';'
    || Source_ref4 || ';'
    || part_no || ';'
    || -(qty_reserved - qty_picked)|| ';'
    || Fnd_Session_API.Get_Fnd_User || ';'
    || to_char(SYSDATE, 'DD.MM.YYYY') || ';' || to_char(SYSDATE, 'HH24:MI:SS') || ';'
    || contract as "C00"
from  PICK_SHIPMENT_RES_STOCK_UIV
      WHERE location_no = '3'
      and pick_list_no=  pick_list_no_;
 
   l_xmltype := XMLTYPE(l_refcursor);

   bxml_ :=l_xmltype.getStringVal();

plsql_rest_sender_api.Call_rest_Endpoint(
rest_service_ => 'SEND_TO_KARDEX',
xml_ => bxml_,
http_method_ => 'POST',
http_req_headers_ => 'Content-Type:application/xml',
message_type_ => 'KARDEX'
);
   
      EXCEPTION
   WHEN OTHERS THEN
      Dbms_Output.Put_Line('SQLERRM:'||SQLERRM);
      ROLLBACK;
      RAISE;
   
END; 


Then my xsl transformer connected to routing address

 

<?xml version="1.0" encoding="UTF-8"?>

-<xsl:stylesheet xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="2.0">

<xsl:output indent="no" encoding="UTF-8" method="xml"/>

<xsl:output method="xml" omit-xml-declaration="yes"/>


-<xsl:template match="/">


-<xsl:for-each select="/ROWSET/ROW">

<xsl:value-of select="C00"/>

<xsl:text> </xsl:text>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>

Userlevel 6
Badge +12

Hi @Jouni,
thanks, it really works! (despite of it is “wrong” configured)

it didn't occur to me because I want to create noXML output, so I used output method “text”.

<?xml version="1.0" ?>
<xsl:stylesheet
version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:gesmes="http://www.gesmes.org/xml/2002-08-01"
xmlns:a="http://www.ecb.int/vocabulary/2002-08-01/eurofxref"
>
<xsl:output method="xml" indent="no" omit-xml-declaration="yes" />

<xsl:template match="/">
<xsl:apply-templates select="/gesmes:Envelope/a:Cube/a:Cube" />
</xsl:template>

<xsl:template match="/gesmes:Envelope/a:Cube/a:Cube">
<xsl:variable name="date" select="@time" />

<xsl:for-each select="a:Cube">
<xsl:value-of select="concat(@currency, ';', @rate, ';', $date, '&#xA;')" />
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Only some notes:
You have used <xsl:output /> twice, I don’t know, how it is interpreted, if it is merged or some occurrence is ignored.
And correct me, if I’m wrong, but only supported XSLT version is 1.0 (because of used Java implicit XALAN processor).

Userlevel 5
Badge +11

Hello to all,

I have a similar problem.
When importing a .TXT file through IFS Connect, all the lines are deleted to form one.
Could you help me to build the transformer file?


Thank you in advance for your help.
 

Userlevel 3
Badge +7

Could it have to do with the encoding on the file reader? It seems FILE_READER1 has ISO-8859-1 as default encoding.

Userlevel 6
Badge +12

Hi @AussieAnders ,
this parameter is configurable, and I tried use UTF-8.

BR

Reply