mibuso.com
Home› NAV Three Tier
Options

Read actual currencies ECB

denpardenpar Member Posts: 80
edited 2014-12-31 in NAV Three Tier
Hi all,

I'm trying to read the actual currencies from the ECB (European Central Bank) with the following code:

Variables
Xml DotNet 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument
NameTable DotNet 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.NameTable
XmlNameSpaceManager DotNet 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNamespaceManager
XmlNode DotNet 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode
XmlNodes DotNet 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeList
XmlNodeAttribute DotNet 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode
CurrencyDate Date
CurrencyYear Integer
CurrencyMonth Integer
CurrencyDay Integer
NodeCounter Integer
CurrencyName Code20
CurrencyRate Decimal
Output Text1024
Xml := Xml.XmlDocument();
Xml.Load('http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml');
NameTable := NameTable.NameTable;
XmlNameSpaceManager := XmlNameSpaceManager.XmlNamespaceManager(NameTable);
XmlNameSpaceManager.AddNamespace('ns1','http://www.ecb.int/vocabulary/2002-08-01/eurofxref');
XmlNameSpaceManager.AddNamespace('gesmes','http://www.gesmes.org/xml/2002-08-01');

XmlNode := Xml.SelectSingleNode('/gesmes:Envelope/ns1:Cube/ns1:Cube',XmlNameSpaceManager);
XmlNodeAttribute := XmlNode.Attributes.GetNamedItem('time');
MESSAGE(XmlNodeAttribute.InnerXml);
EVALUATE(CurrencyYear,COPYSTR(XmlNodeAttribute.InnerXml,1,4));
EVALUATE(CurrencyMonth,COPYSTR(XmlNodeAttribute.InnerXml,6,2));
EVALUATE(CurrencyDay,COPYSTR(XmlNodeAttribute.InnerXml,9,2));
CurrencyDate := DMY2DATE(CurrencyDay, CurrencyMonth, CurrencyYear);

Output := 'Date: ' + FORMAT(CurrencyDate)+ '\';

XmlNodes := Xml.SelectNodes('/gesmes:Envelope/ns1:Cube/ns1:Cube/ns1:Cube',XmlNameSpaceManager);
FOR NodeCounter := 0 TO XmlNodes.Count - 1 DO BEGIN
  CurrencyName := XmlNodes.Item(NodeCounter).Attributes.GetNamedItem('currency').InnerXml;
  EVALUATE(CurrencyRate,CONVERTSTR(XmlNodes.Item(NodeCounter).Attributes.GetNamedItem('rate').InnerXml,'.',','));
  Output := Output + '\' + CurrencyName + ' = ' + FORMAT(CurrencyRate);
END;

MESSAGE(Output);

But.... this will cause the following error which I don't understand....

Method System.Xml.XmlAttribute.InnerXml is not found

Could someone please help me to understand what's causing the error??

Thx!
If your work becomes a passion...
www.pardaan.com

Comments

  • Options
    ftorneroftornero Member Posts: 522
    Try this code:
    XMLDoc	DotNet	System.Xml.XmlDocument.'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
XMLDocOut	DotNet	System.Xml.XmlDocument.'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
XMLNode	DotNet	System.Xml.XmlNode.'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
XMLNodeList	DotNet	System.Xml.XmlNodeList.'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
XMLAttrib	DotNet	System.Xml.XmlAttributeCollection.'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
i	Integer		
Nodes	Integer		
tDate	Text		
tCurrency	Text		
tRate	Text		


XMLDoc := XMLDoc.XmlDocument;

XMLDoc.Load('http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml');

RemoveNamespace(XMLDoc, XMLDocOut);
XMLNodeList := XMLDocOut.GetElementsByTagName('Cube');
Nodes := XMLNodeList.Count;
FOR i := 0 TO Nodes-1 DO BEGIN
  XMLNode   := XMLNodeList.ItemOf(i);
  XMLAttrib := XMLNode.Attributes;
  XMLNode   := XMLAttrib.GetNamedItem('time');
  IF NOT ISNULL(XMLNode) THEN
    tDate     := XMLNode.InnerText;
  XMLNode   := XMLAttrib.GetNamedItem('currency');
  IF NOT ISNULL(XMLNode) THEN
    tCurrency := XMLNode.InnerText;
  XMLNode   := XMLAttrib.GetNamedItem('rate');
  IF NOT ISNULL(XMLNode) THEN
    tRate     := XMLNode.InnerText;
  MESSAGE('(%1/%2) %3  %4 = %5', i+1, Nodes, tDate, tCurrency, tRate);
END;



RemoveNamespace(XMLDocIn : DotNet "System.Xml.XmlDocument";VAR XMLDocOut : DotNet "System.Xml.XmlDocument")

XMLStyleSheet	DotNet	System.Xml.XmlDocument.'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
XslTransform	DotNet	System.Xml.Xsl.XslTransform.'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
writer	DotNet	System.IO.StringWriter.'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	
nullXsltArgumentList	DotNet	System.Xml.Xsl.XsltArgumentList.'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'	

// before this function your tag looks like this: <namespace:tagname>sample text</tagname>
// after this function your tag looks like this: <tagname>sample text</tagname>
// additionally all namespace references in the head are removed.
XslTransform :=  XslTransform.XslTransform;
XMLStyleSheet := XMLStyleSheet.XmlDocument;
XMLStyleSheet.InnerXml(
'<?xml version="1.0" encoding="UTF-8"?>' +
'<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">' +
'<xsl:output method="xml" encoding="UTF-8" />' +
'<xsl:template match="/">' +
'<xsl:copy>' +
'<xsl:apply-templates />' +
'</xsl:copy>' +
'</xsl:template>' +
'<xsl:template match="*">' +
'<xsl:element name="{local-name()}">' +
'<xsl:apply-templates select="@* | node()" />' +
'</xsl:element>' +
'</xsl:template>' +
'<xsl:template match="@*">' +
'<xsl:attribute name="{local-name()}"><xsl:value-of select="."/></xsl:attribute>' +
'</xsl:template>' +
'<xsl:template match="text() | processing-instruction() | comment()">' +
'<xsl:copy />' +
'</xsl:template>' +
'</xsl:stylesheet>'
);

XslTransform.Load(XMLStyleSheet);
writer := writer.StringWriter();
XslTransform.Transform(XMLDocIn, nullXsltArgumentList, writer);
XMLDocOut := XMLDocOut.XmlDocument;
XMLDocOut.InnerXml(writer.ToString());
  • Options
    denpardenpar Member Posts: 80
    Unfortunately I get the same error with this code....
    If your work becomes a passion...
    www.pardaan.com
  • Options
    ftorneroftornero Member Posts: 522
    That's odd, the code works without any problem.

    I've tested again and this is the codeunit 50004 in txt format that do the job:

    OBJECT Codeunit 50004 Funcion ECB->Divisa
    {
    OBJECT-PROPERTIES
    {
    Date=29/12/14;
    Time=11:41:01;
    Modified=Yes;
    Version List=WS;
    }
    PROPERTIES
    {
    OnRun=BEGIN
    GetCurrencies;
    END;

    }
    CODE
    {
    VAR
    XMLDoc@1000000000 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
    XMLDocOut@1000000018 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
    XMLNode@1000000013 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNode";
    XMLNodeList@1000000014 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlNodeList";
    XMLAttrib@1000000015 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlAttributeCollection";
    i@1000000016 : Integer;
    Nodes@1000000017 : Integer;
    tDate@1000000021 : Text;
    tCurrency@1000000022 : Text;
    tRate@1000000023 : Text;

    PROCEDURE GetCurrencies@1000000005();
    BEGIN
    XMLDoc := XMLDoc.XmlDocument;

    XMLDoc.Load('http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml');

    RemoveNamespace(XMLDoc, XMLDocOut);
    XMLNodeList := XMLDocOut.GetElementsByTagName('Cube');
    Nodes := XMLNodeList.Count;
    FOR i := 0 TO Nodes-1 DO BEGIN
    XMLNode := XMLNodeList.ItemOf(i);
    XMLAttrib := XMLNode.Attributes;
    XMLNode := XMLAttrib.GetNamedItem('time');
    IF NOT ISNULL(XMLNode) THEN
    tDate := XMLNode.InnerText;
    XMLNode := XMLAttrib.GetNamedItem('currency');
    IF NOT ISNULL(XMLNode) THEN
    tCurrency := XMLNode.InnerText;
    XMLNode := XMLAttrib.GetNamedItem('rate');
    IF NOT ISNULL(XMLNode) THEN
    tRate := XMLNode.InnerText;
    MESSAGE('(%1/%2) %3 %4 = %5', i+1, Nodes, tDate, tCurrency, tRate);
    END;
    END;

    PROCEDURE RemoveNamespace@1000000004(XMLDocIn@1000000000 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";VAR XMLDocOut@1000000001 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument");
    VAR
    XMLStyleSheet@1000000005 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.XmlDocument";
    XslTransform@1000000004 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.Xsl.XslTransform";
    writer@1000000003 : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.StringWriter";
    nullXsltArgumentList@1000000002 : DotNet "'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Xml.Xsl.XsltArgumentList";
    BEGIN
    // before this function your tag looks like this: <namespace:tagname>sample text</tagname>
    // after this function your tag looks like this: <tagname>sample text</tagname>
    // additionally all namespace references in the head are removed.
    XslTransform := XslTransform.XslTransform;
    XMLStyleSheet := XMLStyleSheet.XmlDocument;
    XMLStyleSheet.InnerXml(
    '<?xml version="1.0" encoding="UTF-8"?>' +
    '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">' +
    '<xsl:output method="xml" encoding="UTF-8" />' +
    '<xsl:template match="/">' +
    '<xsl:copy>' +
    '<xsl:apply-templates />' +
    '</xsl:copy>' +
    '</xsl:template>' +
    '<xsl:template match="*">' +
    '<xsl:element name="{local-name()}">' +
    '<xsl:apply-templates select="@* | node()" />' +
    '</xsl:element>' +
    '</xsl:template>' +
    '<xsl:template match="@*&quot;>' +
    '<xsl:attribute name="{local-name()}"><xsl:value-of select="."/></xsl:attribute>' +
    '</xsl:template>' +
    '<xsl:template match="text() | processing-instruction() | comment()">' +
    '<xsl:copy />' +
    '</xsl:template>' +
    '</xsl:stylesheet>'
    );

    XslTransform.Load(XMLStyleSheet);
    writer := writer.StringWriter();
    XslTransform.Transform(XMLDocIn, nullXsltArgumentList, writer);
    XMLDocOut := XMLDocOut.XmlDocument;
    XMLDocOut.InnerXml(writer.ToString());
    END;

    BEGIN
    END.
    }
    }
  • Options
    denpardenpar Member Posts: 80
    I'm afraid it's a twilight-zone-problem....

    Still the same error!?
    If your work becomes a passion...
    www.pardaan.com
  • Options
    ftorneroftornero Member Posts: 522
    What's the NAV version ?
  • Options
    denpardenpar Member Posts: 80
    NAV 2009 R2 33277
    If your work becomes a passion...
    www.pardaan.com
  • Options
    ftorneroftornero Member Posts: 522
    You can try this codeunit (50007) that use Automation instead DotNet and works in RTC and Classic.

    OBJECT Codeunit 50007 Funcion ECB->Divisa 2
    {
    OBJECT-PROPERTIES
    {
    Date=30/12/14;
    Time=12:33:32;
    Modified=Yes;
    Version List=XML-Divisa;
    }
    PROPERTIES
    {
    OnRun=BEGIN
    Client := NOT ISSERVICETIER;
    GetCurrencies;
    END;

    }
    CODE
    {
    VAR
    Client@1000000000 : Boolean;

    PROCEDURE GetCurrencies@1000000012();
    VAR
    XmlHttp@1000000003 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 6.0:{88D96A0B-F192-11D4-A65F-0040963251E5}:'Microsoft XML, v6.0'.ServerXMLHTTP60";
    XMLDoc@1100001 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 3.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v3.0'.DOMDocument";
    XMLDocOut@1100000 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 3.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v3.0'.DOMDocument";
    XMLNode@1000000011 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 3.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v3.0'.IXMLDOMNode";
    XMLNodeList@1000000005 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 3.0:{2933BF82-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v3.0'.IXMLDOMNodeList";
    XMLAttrib@1000000004 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 3.0:{2933BF83-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v3.0'.IXMLDOMNamedNodeMap";
    tFecha@1100004 : Text[30];
    tCurrency@1000000000 : Text[30];
    tRate@1000000001 : Text[30];
    i@1000000002 : Integer;
    BEGIN
    IF ISCLEAR(XmlHttp) THEN
    CREATE(XmlHttp, FALSE, Client);
    IF ISCLEAR(XMLDoc) THEN
    CREATE(XMLDoc, FALSE, Client);
    IF ISCLEAR(XMLDocOut) THEN
    CREATE(XMLDocOut, FALSE, Client);

    XmlHttp.open('GET', 'http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml',0);
    XmlHttp.setTimeouts(10000,10000,10000,100000);
    XmlHttp.send('');
    XMLDoc.async := FALSE;
    XMLDoc.load(XmlHttp.responseBody);

    i := 0;
    RemoveNameSpace(XMLDoc, XMLDocOut);
    XMLNodeList := XMLDocOut.getElementsByTagName('Cube');
    XMLNode := XMLNodeList.nextNode;
    WHILE NOT ISCLEAR(XMLNode) DO BEGIN
    XMLAttrib := XMLNode.attributes;
    XMLNode := XMLAttrib.getNamedItem('time');
    IF NOT ISCLEAR(XMLNode) THEN
    tFecha := XMLNode.text;
    XMLNode := XMLAttrib.getNamedItem('currency');
    IF NOT ISCLEAR(XMLNode) THEN
    tCurrency := XMLNode.text;
    XMLNode := XMLAttrib.getNamedItem('rate');
    IF NOT ISCLEAR(XMLNode) THEN
    tRate := XMLNode.text;
    MESSAGE('%1 %2 = %3', tFecha, tCurrency, tRate);
    XMLNode := XMLNodeList.nextNode;
    END;

    CLEAR(XmlHttp);
    CLEAR(XMLDoc);
    CLEAR(XMLDocOut);
    END;

    PROCEDURE RemoveNameSpace@1000000001(XMLSourceDoc@1000000001 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 3.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v3.0'.DOMDocument";VAR XMLDestinationDoc@1000000000 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 3.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v3.0'.DOMDocument");
    VAR
    OutStreamStylesheet@1000000002 : OutStream;
    InStreamStylesheet@1000000003 : InStream;
    XMLStyleSheet@1000000004 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 3.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v3.0'.DOMDocument";
    TempTable@1000000005 : TEMPORARY Record 79;
    BEGIN
    TempTable.Picture.CREATEOUTSTREAM(OutStreamStylesheet);
    TempTable.Picture.CREATEINSTREAM(InStreamStylesheet);
    OutStreamStylesheet.WRITETEXT('<?xml version="1.0" encoding="UTF-8"?>');
    OutStreamStylesheet.WRITETEXT('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">');
    OutStreamStylesheet.WRITETEXT('<xsl:output method="xml" encoding="UTF-8" />');
    OutStreamStylesheet.WRITETEXT('<xsl:template match="/">');
    OutStreamStylesheet.WRITETEXT('<xsl:copy>');
    OutStreamStylesheet.WRITETEXT('<xsl:apply-templates />');
    OutStreamStylesheet.WRITETEXT('</xsl:copy>');
    OutStreamStylesheet.WRITETEXT('</xsl:template>');
    OutStreamStylesheet.WRITETEXT('<xsl:template match="*">');
    OutStreamStylesheet.WRITETEXT('<xsl:element name="{local-name()}">');
    OutStreamStylesheet.WRITETEXT('<xsl:apply-templates select="@* | node()" />');
    OutStreamStylesheet.WRITETEXT('</xsl:element>');
    OutStreamStylesheet.WRITETEXT('</xsl:template>');
    OutStreamStylesheet.WRITETEXT('<xsl:template match="@*&quot;>');
    OutStreamStylesheet.WRITETEXT('<xsl:attribute name="{local-name()}"><xsl:value-of select="."/></xsl:attribute>');
    OutStreamStylesheet.WRITETEXT('</xsl:template>');
    OutStreamStylesheet.WRITETEXT('<xsl:template match="text() | processing-instruction() | comment()">');
    OutStreamStylesheet.WRITETEXT('<xsl:copy />');
    OutStreamStylesheet.WRITETEXT('</xsl:template>');
    OutStreamStylesheet.WRITETEXT('</xsl:stylesheet>');
    IF ISCLEAR(XMLStyleSheet) THEN
    CREATE(XMLStyleSheet, FALSE, Client);
    XMLStyleSheet.load(InStreamStylesheet);
    IF ISCLEAR(XMLDestinationDoc) THEN
    CREATE(XMLDestinationDoc, FALSE, Client);
    XMLSourceDoc.transformNodeToObject(XMLStyleSheet,XMLDestinationDoc);
    END;

    BEGIN
    END.
    }
    }
  • Options
    ftorneroftornero Member Posts: 522
    Due to when I pasted the code the indentation got away, you need to put some spaces or tabs in front of the 3 lines below OnRun=BEGIN, like this:
    OnRun=BEGIN
               Client := NOT ISSERVICETIER;
               GetCurrencies;
            END;

    So you can import the txt without errors.

    Bye.
  • Options
    denpardenpar Member Posts: 80
    Thx for your help, this one works :)

    Still a bit frustrated not understanding the problem with the first solution...

    Happy new year!!
    If your work becomes a passion...
    www.pardaan.com
  • Options
    ftorneroftornero Member Posts: 522
    The first solution works in NAV2013, NAV2013 R2 and NAV2015.

    In NAV 2009 R2 I had the build 32012 and I get an ambiguity error compiling the codeunit so I used Automation.

    Happy new year to you.

    Regards.
Sign In or Register to comment.