Example for using an XMLport to update a table

josephdewey · 2012-11-06

I've searched and searched for an example for this, but all I've found are links to things that only answer a small portion of my question.

I have an XML document (see below), and I want to use an XMLport to do an import/update. If the CUST_ID is not in the Customer table, it should import it. If the CUST_ID is in the customer table, it should update the customer information.

Here's my XML document:

<root>
  <row>
    <CUST_ID>3060966</CUST_ID>
    <CUST_NAME>Frank Wallace</CUST_NAME>
    <CUST_ADDR>123 Main</CUST_ADDR>
    <CUST_PHONE>985-555-1212</CUST_PHONE>
  </row>
  <row>
    <CUST_ID>3060967</CUST_ID>
    <CUST_NAME>Betty Weaving</CUST_NAME>
    <CUST_ADDR>123 Washington</CUST_ADDR>
    <CUST_PHONE>985-555-1213</CUST_PHONE>
  </row>
</root>

And, I know from my research that I'll probably need to make a temporary table, and then use that temporary table to process the records. But, I could really use an example of how I would do this. Thanks in advance. I'm new to XMLports and temporary tables, even though I've done basic examples of them.

kine · 2012-11-06

You do not need new temp table, you can connect the xmlport to the customer table directly, you can just set the SourceTableTemporary property to yes. It means, it insert all data into this temp table, and after import is done, you need to run your process, which will go through this temp table, and do what you need in real table.

josephdewey · 2012-11-06

Thanks Kine for your advice. Does someone have an example of doing this to both import and update records?

josephdewey · 2012-11-07

I figured out an example of how to do this. Here's my code. It creates a temporary table as part of the XMLport, and then it goes through all of the records of the temporary table, and either updates them if they're already in the customer table, or it creates a new record, if they're not in the customer table.

For the XMLport:

OBJECT XMLport 50004 Test Customer XML
{
  OBJECT-PROPERTIES
  {
    Date=11/07/12;
    Time=12:58:28 PM;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnPostXMLport=BEGIN
                    IF TempCust.FINDSET THEN
                      REPEAT
                        IF CustTable.GET(TempCust."No.") THEN BEGIN
                          CustTable.TRANSFERFIELDS(TempCust);
                          CustTable.MODIFY;
                        END
                        ELSE BEGIN
                          CustTable.COPY(TempCust);
                          CustTable.INSERT;
                        END;
                      UNTIL TempCust.NEXT = 0;
                  END;

  }
  ELEMENTS
  {
    { [{9D645870-9123-4C37-B27F-84A0AFFCAA00}];  ;root                ;Element ;Text     }

    { [{C64EA200-FBAA-4BCF-B489-2721F3E59BC6}];1 ;row                 ;Element ;Table   ;
                                                  VariableName=TempCust;
                                                  SourceTable=Table18;
                                                  Temporary=Yes }

    { [{E90D194D-FE25-44B1-9E30-5BCE0D66B616}];2 ;CUST_ID             ;Element ;Field   ;
                                                  DataType=Code;
                                                  SourceField=TempCust::No. }

    { [{412E8942-F652-4BC8-81E0-01EC0C971C8F}];2 ;CUST_NAME           ;Element ;Field   ;
                                                  DataType=Text;
                                                  SourceField=TempCust::Name }

    { [{5D43A42E-F5F9-4830-B31D-7713FC46AA4B}];2 ;CUST_ADDR           ;Element ;Field   ;
                                                  DataType=Text;
                                                  SourceField=TempCust::Address }

    { [{45760F19-38EE-4D47-ACCA-C672E7DA0D96}];2 ;CUST_PHONE          ;Element ;Field   ;
                                                  DataType=Text;
                                                  SourceField=TempCust::Phone No. }

  }
  EVENTS
  {
  }
  REQUESTPAGE
  {
    PROPERTIES
    {
    }
    CONTROLS
    {
    }
  }
  CODE
  {
    VAR
      CustTable@1000000000 : Record 18;

    BEGIN
    END.
  }
}

and the Codeunit:

OBJECT Codeunit 50054 Import Test Customer XML
{
  OBJECT-PROPERTIES
  {
    Date=11/07/12;
    Time=12:37:19 PM;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            //Run the Test Customer import
            FileName.OPEN('C:\Users\username\Desktop\test.xml');
            FileName.CREATEINSTREAM(FileStream);
            XMLPORT.IMPORT(50004, FileStream);
            FileName.CLOSE;


            MESSAGE('Completed!!!');
          END;

  }
  CODE
  {
    VAR
      FileStream@1000000001 : InStream;
      FileName@1000000000 : File;

    BEGIN
    END.
  }
}

Example for using an XMLport to update a table

Comments

Categories