Retrieve data from XML file

Gurn69Gurn69 Member Posts: 10
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

Best Answer

  • ftorneroftornero Member Posts: 522
    Answer ✓
    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.

Answers

  • ftorneroftornero Member Posts: 522
    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
  • Gurn69Gurn69 Member Posts: 10
    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.
  • Gurn69Gurn69 Member Posts: 10
    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
  • ftorneroftornero Member Posts: 522
    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.
  • Gurn69Gurn69 Member Posts: 10
    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
  • ftorneroftornero Member Posts: 522
    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.
  • Gurn69Gurn69 Member Posts: 10
    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
  • ftorneroftornero Member Posts: 522
    Answer ✓
    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.
  • Gurn69Gurn69 Member Posts: 10
    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. B)

    Regards
    Gurnek
Sign In or Register to comment.