Retrieve data from XML file

Gurn69 · 2021-04-08

Hi everyone
I am new to the world of soap webservice response files and XML. I was wondering if anyone could advice on how to retrieve data like accno, carriername etc from the following file

<GetDeliveryOptionsResult><deliveryoptionresults>
<recommended><DeliveryOption><accno>123456</accno><carriername>TEST CARRIER1</carriername><carrzone>ZONE 1</carrzone><condate>1/04/2021</condate><datelong>20210401</datelong><fromzone>ZONE 1</fromzone><optionid>NDozOjEwOi0xOjMy</optionid><primarypricing>65.90</primarypricing><secondarypricing>13.18</secondarypricing><service>NEXT DAY</service></DeliveryOption>
</recommended>
<otheroptions>
<DeliveryOption><accno>789456</accno><carriername>TEST CARRIER2</carriername><carrzone>ZONE 1</carrzone><condate>1/04/2021</condate><datelong>20210401</datelong><fromzone>ZONE 1</fromzone><optionid>NTozOjEzOi0xOjMy</optionid><primarypricing>69.00</primarypricing><secondarypricing>13.80</secondarypricing><service>NEXT DAY</service></DeliveryOption>
<DeliveryOption><accno>565656</accno><carriername>TEST CARRIER3</carriername><carrzone>ZONE 1</carrzone><condate>1/04/2021</condate><datelong>20210401</datelong><fromzone>ZONE 1</fromzone><optionid>NjozOjc6LTE6MzI=</optionid><primarypricing>72.50</primarypricing><secondarypricing>14.50</secondarypricing><service>NEXT DAY</service></DeliveryOption>
</otheroptions>
<distance></distance>
<istopobox>False</istopobox>
<selectedtype>cost</selectedtype>
<updates><ObjectUiFormContents><_nonratingzonesdone>-196865584</_nonratingzonesdone></ObjectUiFormContents>
</updates>
</deliveryoptionresults>
</GetDeliveryOptionsResult>.

I have removed the namespaces and and now having trouble extracting data from the DeliveryOption. To be honest i am struggling with understanding xml node lists, nodes and elements. Is there any documentation (apart from Microsoft Docs) that i can read to get a better understanding of how to use these xml variables.

I appreciate the time any takes to read this and would be very grateful for any advice/assistance.

Please accept my apologies if i have incorrectly posted as this is my first time actually posting

Regards
Gurnek

ftornero · 2021-04-09

Hello @Gurn69

Your line

if xmlNod2.SelectNodes('//*[local-name()="DeliveryOption"]', xmlNodList) then begin

must be

if xmlDoc2.SelectNodes('//*[local-name()="DeliveryOption"]', xmlNodList) then begin

Regards.

ftornero · 2021-04-08

Hello @Gurn69,

What NAV/BC version are you using ?

And if you wrap your code, xml, etc, with \[code\] \[/code\] , without the backslash, it looks better.

<GetDeliveryOptionsResult>
    <deliveryoptionresults>
        <recommended>
            <DeliveryOption>
                <accno>123456</accno>
                <carriername>TEST CARRIER1</carriername>
                <carrzone>ZONE 1</carrzone>
                <condate>1/04/2021</condate>
                <datelong>20210401</datelong>
                <fromzone>ZONE 1</fromzone>
                <optionid>NDozOjEwOi0xOjMy</optionid>
                <primarypricing>65.90</primarypricing>
                <secondarypricing>13.18</secondarypricing>
                <service>NEXT DAY</service>
            </DeliveryOption>
        </recommended>
        <otheroptions>
            <DeliveryOption>
                <accno>789456</accno>
                <carriername>TEST CARRIER2</carriername>
                <carrzone>ZONE 1</carrzone>
                <condate>1/04/2021</condate>
                <datelong>20210401</datelong>
                <fromzone>ZONE 1</fromzone>
                <optionid>NTozOjEzOi0xOjMy</optionid>
                <primarypricing>69.00</primarypricing>
                <secondarypricing>13.80</secondarypricing>
                <service>NEXT DAY</service>
            </DeliveryOption>
            <DeliveryOption>
                <accno>565656</accno>
                <carriername>TEST CARRIER3</carriername>
                <carrzone>ZONE 1</carrzone>
                <condate>1/04/2021</condate>
                <datelong>20210401</datelong>
                <fromzone>ZONE 1</fromzone>
                <optionid>NjozOjc6LTE6MzI=</optionid>
                <primarypricing>72.50</primarypricing>
                <secondarypricing>14.50</secondarypricing>
                <service>NEXT DAY</service>
            </DeliveryOption>
        </otheroptions>
        <distance></distance>
        <istopobox>False</istopobox>
        <selectedtype>cost</selectedtype>
        <updates>
            <ObjectUiFormContents>
                <_nonratingzonesdone>-196865584</_nonratingzonesdone>
            </ObjectUiFormContents>
        </updates>
    </deliveryoptionresults>
</GetDeliveryOptionsResult>

Regards

Gurn69 · 2021-04-09

Hi I am importing this file into Business Central v17. However the file sent back from the webservice seems to put all the information that I require in the "deliveryoptionresults" tag and i am trying to structure this onto a more logical format but unsuccessful. The original file is being sent back with < and > but i have replaced these with <,> respectively. Unclear as to what you mean by \[code\] \[/code\]. Could you explain a bit more please.

Gurn69 · 2021-04-09

Just looking at the api documentation the response file layout shown in the documentation is

<GetDeliveryOptionsResult>
<![CDATA[
<deliveryoptionresults>
<recommended>
<DeliveryOption>
<accno>123456</accno>
<carriername>TEST CARRIER1</carriername>
<carrzone>ZONE 1</carrzone>
<condate>22/07/2020</condate>
<datelong>20200722</datelong>
<fromzone>ZONE 1</fromzone>
<optionid>NDowOjA6LTE6MzIsMzI=</optionid>
<primarypricing>13.18</primarypricing>
<secondarypricing>2.64</secondarypricing>
<service>NEXT DAY</service>
</DeliveryOption>
</recommended>
<otheroptions>
<DeliveryOption>
<accno>789456</accno>
<carriername>TEST CARRIER2</carriername>
<carrzone>ZONE 1</carrzone>
<condate>22/07/2020</condate>
<datelong>20200722</datelong>
<fromzone>ZONE 1</fromzone>
<optionid>NTowOjA6LTE6MzIsMzI=</optionid>
<primarypricing>13.80</primarypricing>
<secondarypricing>2.76</secondarypricing>
<service>NEXT DAY</service>
</DeliveryOption>
<DeliveryOption>
<accno>565656</accno>
<carriername>TEST CARRIER3</carriername>
<carrzone>ZONE 1</carrzone>
<condate>22/07/2020</condate>
<datelong>20200722</datelong>
<fromzone>ZONE 1</fromzone>
<optionid>NjowOjA6LTE6MzIsMzI=</optionid>
<primarypricing>14.50</primarypricing>
<secondarypricing>2.90</secondarypricing>
<service>NEXT DAY</service>
</DeliveryOption>
</otheroptions>
<distance></distance>
<istopobox>False</istopobox>
<selectedtype>cost</selectedtype>
</deliveryoptionresults>]]>
</GetDeliveryOptionsResult>

The data that needs to be extracted is all in the CDATA section. How would i extract this ?

Regards
Gurnek

ftornero · 2021-04-09

Hello @Gurn69,

You can use this function, like an example, to read the XML:

    procedure ReadXML()
    var
        Instr: InStream;
        fileName: Text;
        xmlDoc: XmlDocument;
        xmlDoc2: XmlDocument;
        xmlNodList: XmlNodeList;
        xmlNod: XmlNode;
        xmlNod2: XmlNode;
        fileMgt: Codeunit "File Management";
        tempBlob: Codeunit "Temp Blob";
        cData: Text;

    begin
        fileName := fileMgt.BLOBImportWithFilter(tempBlob, 'Choose XML', fileName, 'XML Files (*.XML)|*.XML', 'xml');
        if fileName <> '' then begin
            tempBlob.CreateInStream(Instr);
            if XmlDocument.ReadFrom(InStr, xmlDoc) then begin
                if xmlDoc.SelectSingleNode('//*[local-name()="GetDeliveryOptionsResult"]', xmlNod) then begin
                    cData := xmlNod.AsXmlElement().InnerText;
                    if XmlDocument.ReadFrom(cData, xmlDoc2) then begin
                        if xmlDoc2.SelectNodes('//*[local-name()="DeliveryOption"]', xmlNodList) then begin
                            foreach xmlNod in xmlNodList do begin
                                if xmlNod.SelectSingleNode('*[local-name()="carriername"]', xmlNod2) then
                                    Message(xmlNod2.AsXmlElement().InnerText);
                            end;
                        end;
                    end;
                end;
            end;
        end;
    end;

And regarding the \[code\] \[/code\]. is to give format to your code,xml,etc in this forum.

Regads.

Gurn69 · 2021-04-09

Hi @ftornero
Firstly may i thank you for helping me with this problem really appreciate it. I will try the above solution my only issue is that i am not actually physically selecting a file to load the actual file is being returned from a webservice call and i want to import the data into a BC table (Saas) table. From the above solution t seems to me that we are physically selecting a file to import, would i be correct in saying that ? Also regarding selecting the data i know selectsinglenode only selects the first occurence however if for example i wanted to retrieve all occurences of carriername would the solution retrieve these if not how would i retrieve all occurrences of an element. Sorry to be such a pain but new to all this XML so just trying to understand for future.

Regards
Gurnek

ftornero · 2021-04-09

Hello @Gurn69,

Yes, in the example the xml file is selected, but the important thing is that you end up with a InStream tha you can convert in a XMLDocument to process, you can use a Text variaible in place of the InStream.

So, from the webservice you must get an InStream or a Text and the rest is the same.

And to retrieve all ocurrences of an element, in the example code is this line:

if xmlDoc2.SelectNodes('//*[local-name()="DeliveryOption"]', xmlNodList) then begin

Regards.

Gurn69 · 2021-04-09

Hi @ftornero

I tried the solution and it failed with the following error

Microsoft.Dynamics.Nav.Runtime.NavXmlNode variable not initialised.

This happened when trying to process the following line

if xmlNod2.SelectNodes('//*[local-name()="DeliveryOption"]', xmlNodList) then begin

I tried to debug the problem and the only thing i can think of is that the data is in the following format after processing the line

if XmlDocument.ReadFrom(cData, XMLDoc2) then begin

<deliveryoptionresults>
<recommended><DeliveryOption><accno>123456</accno><carriername>TEST CARRIER1</carriername><carrzone>ZONE 1</carrzone><condate>9/04/2021</condate><datelong>20210409</datelong><fromzone>ZONE 1</fromzone><optionid>NDozOjEwOi0xOg==</optionid><primarypricing>65.90</primarypricing><secondarypricing>13.18</secondarypricing><service>NEXT DAY</service></DeliveryOption>
</recommended>
<otheroptions>
<DeliveryOption><accno>789456</accno><carriername>TEST CARRIER2</carriername><carrzone>ZONE 1</carrzone><condate>9/04/2021</condate><datelong>20210409</datelong><fromzone>ZONE 1</fromzone><optionid>NTozOjEzOi0xOg==</optionid><primarypricing>69.00</primarypricing><secondarypricing>13.80</secondarypricing><service>NEXT DAY</service></DeliveryOption>
<DeliveryOption><accno>565656</accno><carriername>TEST CARRIER3</carriername><carrzone>ZONE 1</carrzone><condate>9/04/2021</condate><datelong>20210409</datelong><fromzone>ZONE 1</fromzone><optionid>NjozOjc6LTE6</optionid><primarypricing>72.50</primaypricing>65.90</primarypricing><secondarypricing>13.18</secondarypricing><service>NEXT DAY</service></DeliveryOption><otheroptions><deliveryoptionresults>

Any views on why the variable may not be initialised?

Regards
Gurnek

ftornero · 2021-04-09

Hello @Gurn69

Your line

if xmlNod2.SelectNodes('//*[local-name()="DeliveryOption"]', xmlNodList) then begin

must be

if xmlDoc2.SelectNodes('//*[local-name()="DeliveryOption"]', xmlNodList) then begin

Regards.

Gurn69 · 2021-04-09

Hi @ftornero i have to say a very heartful thank you to you for helping me resolve my problem. Really appreciate the time you have taken to assist me.

You have saved me going mad.

Regards
Gurnek

Retrieve data from XML file

Best Answer

Answers

Categories