Retrieve data from XML file

Gurn69

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

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

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

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

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

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

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

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

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

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

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