Read XML Product file & store it in to staging table using X++

In this post, I would like to share X++ Code that will help to read XML product file & stored its information in to staging table in AX 2012 using X++

Suppose we have following XML file.


<?xml version="1.0" encoding="utf-8"?>
<!--Products.xml file-->
<Products>
  <Product>
    <OrderItem>010109</OrderItem>
    <UOM>EA</UOM>
    <UnitPrice>0.01</UnitPrice>
    <ManufacturerName>ACCO</ManufacturerName>
    <PictureFileName>S0165591_STD.JPG</PictureFileName>
    <UNSPSC>44122003</UNSPSC>
    <ShortDescribe>BINDER 38610 (YR2B250) YELLOW</ShortDescribe>
    <LongDescribe>Pressboard Binder, 1" Capacity, 11"x8-1/2", Yellow</LongDescribe>
    <Category>Non-Categorized</Category>
    <SubCategory>Non-Categorized</SubCategory>
    <LongWebDescription>Coated pressboard offers excellent moisture resistance with a grained, long-lasting finish. High quality three-ring metal. Opening and closing triggers.</LongWebDescription>
  </Product>
  <Product>
    <OrderItem>ACC38610</OrderItem>
    <UOM>EA</UOM>
    <UnitPrice>0.01</UnitPrice>
    <ManufacturerName>ACCO</ManufacturerName>
    <PictureFileName>S0165591_STD.JPG</PictureFileName>
    <UNSPSC>44122003</UNSPSC>
    <ShortDescribe>BINDER 38610 (YR2B250) YELLOW</ShortDescribe>
    <LongDescribe>Pressboard Binder, 1" Capacity, 11"x8-1/2", Yellow</LongDescribe>
    <Category>Non-Categorized</Category>
    <SubCategory>Non-Categorized</SubCategory>
    <LongWebDescription>Coated pressboard offers excellent moisture resistance with a grained, long-lasting finish. High quality three-ring metal. Opening and closing triggers.</LongWebDescription>
  </Product>
</Products>


And we have following table in AX.

Table Name: scnVendCatInboundCatalog

Field Name	Data Type	Comments
VendItem	String50 EDT	Order Item
UOM	String25 EDT	UOM
UnitPrice	String25 EDT	Unit Price
OEMName	String50 EDT	Manufacturer Name
PicURI	String50 EDT	Picture File Name. Note: Assumption that this will point to a URL picture resource from Vendor.
UNSPSC	String50 EDT	UNSPSC
ShortDesc	String50 EDT	Short Describe
LongDesc	String250 EDT	Long Describe
VendCategory	String50 EDT	Category
VendSubCategory	String50 EDT	Sub Category
LongWebDesc	String250 EDT	Long Web Description

now we need to write 2 methods to import XML file data into above table. First method we will write as a table method to check whether imported data is already exists in table or not.


public boolean CheckVendItem(VendItem _vendItem)
{
    scnVendCatInboundCatalog VendCatInboundCatalog;
    ;

    select firstOnly VendCatInboundCatalog where VendCatInboundCatalog.VendItem == _vendItem;

    if ( VendCatInboundCatalog.VendItem == _vendItem)
    return true;
    else
    return false;
}

Second method will responsible to read XML file & insert new record or update existing record in table.


static void XMLReadProduct(Args _args)
{
    #define.paramsNode("Products")
FileIoPermission permission;
XMLDocument doc;
XMLNode rootNode, ProductNode,ProductFieldList;
XMLNodeList ProductList ;
scnVendCatInboundCatalog VendCatInboundCatalog;
XMLParseError xmlError;
int i, countupdate, countinsert;
;
permission= new FileIoPermission("C:\\ABCDEF.xml",'r');
permission.assert();
// Get the XML document
doc = new XMLDocument();
doc.load("C:\\ABCDEF.xml");
xmlError = doc.parseError();
if (xmlError && xmlError.errorCode() != 0)
throw error(strFmt("Error: %1",xmlError.reason()));

rootNode = doc.getNamedElement(#paramsNode);

ProductList = rootNode.childNodes();
for (i = 0; i < ProductList.length(); i++)
{
    ProductFieldList = ProductList.item(i);


    if(VendCatInboundCatalog.CheckVendItem(ProductFieldList.selectSingleNode("OrderItem").text()))
    {
        ttsBegin;
        select forUpdate VendCatInboundCatalog where VendCatInboundCatalog.VendItem == ProductFieldList.selectSingleNode("OrderItem").text();

        VendCatInboundCatalog.VendItem = ProductFieldList.selectSingleNode("OrderItem").text();
        VendCatInboundCatalog.UOM = ProductFieldList.selectSingleNode("UOM").text();
        VendCatInboundCatalog.UnitPrice = ProductFieldList.selectSingleNode("UnitPrice").text();
        VendCatInboundCatalog.OEMName = ProductFieldList.selectSingleNode("ManufacturerName").text();
        VendCatInboundCatalog.PicURI = ProductFieldList.selectSingleNode("PictureFileName").text();
        VendCatInboundCatalog.UNSPSC = ProductFieldList.selectSingleNode("UNSPSC").text();
        VendCatInboundCatalog.ShortDesc = ProductFieldList.selectSingleNode("ShortDescribe").text();
        VendCatInboundCatalog.LongDesc = ProductFieldList.selectSingleNode("LongDescribe").text();
        VendCatInboundCatalog.VendCategory = ProductFieldList.selectSingleNode("Category").text();
        VendCatInboundCatalog.VendSubCategory = ProductFieldList.selectSingleNode("SubCategory").text();
        VendCatInboundCatalog.LongWebDesc = ProductFieldList.selectSingleNode("LongWebDescription").text();

        VendCatInboundCatalog.update();
        ttsCommit;

        countupdate++;
        // info("update successfully");
    }
    else
    {
        VendCatInboundCatalog.VendItem = ProductFieldList.selectSingleNode("OrderItem").text();
        VendCatInboundCatalog.UOM = ProductFieldList.selectSingleNode("UOM").text();
        VendCatInboundCatalog.UnitPrice = ProductFieldList.selectSingleNode("UnitPrice").text();
        VendCatInboundCatalog.OEMName = ProductFieldList.selectSingleNode("ManufacturerName").text();
        VendCatInboundCatalog.PicURI = ProductFieldList.selectSingleNode("PictureFileName").text();
        VendCatInboundCatalog.UNSPSC = ProductFieldList.selectSingleNode("UNSPSC").text();
        VendCatInboundCatalog.ShortDesc = ProductFieldList.selectSingleNode("ShortDescribe").text();
        VendCatInboundCatalog.LongDesc = ProductFieldList.selectSingleNode("LongDescribe").text();
        VendCatInboundCatalog.VendCategory = ProductFieldList.selectSingleNode("Category").text();
        VendCatInboundCatalog.VendSubCategory = ProductFieldList.selectSingleNode("SubCategory").text();
        VendCatInboundCatalog.LongWebDesc = ProductFieldList.selectSingleNode("LongWebDescription").text();

        VendCatInboundCatalog.insert();
        countinsert++;
        // info("insert successfully");
    }

}
    info(strFmt("%1 Update & %2 Insert Done Successfully",countupdate,countinsert));
}

Happy DAX!!!!!