How to import data from an XML file to BC tables?

isabtogumon · 2021-01-28

Hello everyone,

I already have the data from the XML file in InStream, I need to access each node to obtain the data and insert it into a BC table. XML has several levels, in addition to being very extensive. How can I do it?

I have seen a method (y2u.be/x8AOo9j-ifE), however I have not managed to access the nodes. This is an example of the XML structure

<?xml version="1.0" encoding="UTF-8"?>
<soap:Envelope>
   <soap:Body>
      <GetMovementResponse>
         <GetMovementResult>
            <Code>1</Code>
            <Message/>
            <Data></Data>
            <List>
               <Movement>  <--- From here
                  <Code>XXX</Code>
                  <MovementType>XXX</MovementType>
                  <DateMovement>2020-11-27T15:04:04.09</DateMovement>
                  <OriginCompany>
                     <ID>0</ID>
                  </OriginCompany>
                  <CenterCostSource>
                     <ID>0</ID>
                     <ExternalCode>1</ExternalCode>
                  </CenterCostSource>
                  .
                  .
                  .
                </Movement>
             +<Movement>
             +<Movement>
.
.
.

ftornero · 2021-02-04

Hello @isabtogumon

With this XML you need to use a name space manager to deal with the lien in green in the image.

if XmlDocument.ReadFrom(InStr, xmlDoc) then begin
	xmlnsMgr.AddNamespace('ns', 'http://www.company.com.br/Company');
	if xmlDoc.SelectNodes('//ns:Movement', xmlnsMgr, xmlNodList) then begin
		foreach xmlNod in xmlNodList do begin
			xmlNod.SelectSingleNode('ns:OriginCompany/ns:ID', xmlnsMgr, xmlNod2);
			Message(xmlNod2.AsXmlElement().InnerText);
		end;
	end;
end;

And this is the new var:

xmlnsMgr: XmlNamespaceManager;

Regards

ftornero · 2021-01-28

Hello @isabtogumon,

How you already have the xml in a Instream, you can do something like this to get, by example, the value of OriginCompany/ID of every Movement

if XmlDocument.ReadFrom(InStr, xmlDoc) then begin
	if xmlDoc.SelectNodes('//Movement', xmlNodList) then begin
		foreach xmlNod in xmlNodList do begin
			xmlNod.SelectSingleNode('OriginCompany/ID', xmlNod2);
			Message(xmlNod2.AsXmlElement().InnerText);
		end;
	end;
end;

Where the variables are:

InStr: InStream;
xmlDoc: XmlDocument;
xmlNodList: XmlNodeList;
xmlNod: XmlNode;
xmlNod2: XmlNode;

Regards

isabtogumon · 2021-02-02

Hello @ftornero, thanks for your answer

I tried your method but unfortunately it doesn't access the line:

foreach xmlNod in xmlNodList do begin

I skipped that line and it shows me the following error:

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

I do not know if it is related to the fact that I did not enter the mentioned line

ftornero · 2021-02-03

Hello @isabtogumon,

Could you elaborate what means "it doesn't access the line"

Regards.

isabtogumon · 2021-02-04

Hello @ftornero

What I mean, at runtime it doesn't execute the "foreach" block, so it skips these lines:

xmlNod.SelectSingleNode('OriginCompany/ID', xmlNod2);
Message(xmlNod2.AsXmlElement().InnerText);

JJMc · 2021-02-04

The easiest way you can do it, is parse the XML to the XMLBuffer and treat it like a simple table in BC.

ftornero · 2021-02-04

Hello @isabtogumon,

Then the problem is in the previous line, that is not selecting any node.

	if xmlDoc.SelectNodes('//Movement', xmlNodList) then begin

Could you post a reduced version of the XML file that you are reading ?

Regards

isabtogumon · 2021-02-04

Hello @JJMc

I had contemplated it and tried this procedure. However, it does not fill the table correctly

procedure ImportXML()
    var
        XMLInStream: InStream;
        UploadResult: Boolean;
        DialogCaption: Text;
        XMLFileName: Text;
        XMLBuffer: Record "XML Buffer";
        TMovimemtosWS: Record TMovimemtosWS;
    begin
        UploadResult := UploadIntoStream(DialogCaption, '', '', XMLFileName, XMLInStream);
        XMLBuffer.DeleteAll;
        XMLBuffer.LoadFromStream(XMLInStream);
        if XMLBuffer.FindSet() then
            repeat
                TMovimemtosWS.Validate(TipoMovimentacao, XMLBuffer.Value);
                TMovimemtosWS.Validate(DataMovimentacao, XMLBuffer.Value);
                TMovimemtosWS.Validate(TipoDocumento, XMLBuffer.Value);
                if not TMovimemtosWS.Insert() then
                    TMovimemtosWS.Modify();
            until XMLBuffer.Next() = 0;
    end;

I only consider three fields but this process does not fill them correctly

isabtogumon · 2021-02-04

Hello @ftornero

This is just an extract, the XML contains many more nodes called "Movimentacao"

ftornero · 2021-02-04

Hello @isabtogumon

With this XML you need to use a name space manager to deal with the lien in green in the image.

if XmlDocument.ReadFrom(InStr, xmlDoc) then begin
	xmlnsMgr.AddNamespace('ns', 'http://www.company.com.br/Company');
	if xmlDoc.SelectNodes('//ns:Movement', xmlnsMgr, xmlNodList) then begin
		foreach xmlNod in xmlNodList do begin
			xmlNod.SelectSingleNode('ns:OriginCompany/ns:ID', xmlnsMgr, xmlNod2);
			Message(xmlNod2.AsXmlElement().InnerText);
		end;
	end;
end;

And this is the new var:

xmlnsMgr: XmlNamespaceManager;

Regards

isabtogumon · 2021-02-12

Hello @ftornero

I applied the example and that solves the problem. Thank you.

nehalipatel · 2023-08-09

@ftornero can you please explain how you have insert data in BC table.....I am trying with example but it not working

ftornero · 2023-08-14

Hello @nehalipatel,

Could you post how are you trying to insert the data in the BC table ?

Regards.

Howdy, Stranger!

Quick Links

Categories

How to import data from an XML file to BC tables?

Best Answer

Answers