Loading XML

[#1]

I've this problem while reading XML files. I found a script on this blog but it seems to be incorrect, because the join connects all the parrent ID's to all childs.

This is the code:

//------------------------------------------------------------------------------------------------------------------------

// XML Load (Specific part of load script used for loading XML data

//------------------------------------------------------------------------------------------------------------------------

SUB XMLLoad2

  TRACE # Building XML tables ...;

  SET vS_SUB_XMLLoad = 1;

  SET vS_XML_Input =  "..\8_Import\Qlik data1.xml" ;

Directory;

// Start of [ar_EG##exported_0.xml] LOAD statements

'ProductInformation':

LOAD  ExportTime

    , ExportContext

    , ContextID

    , WorkspaceID as [%SPI_WorkspaceID.KEY]

    , UseContextLocale

FROM [$(vS_XML_Input)] (XmlSimple, Table is [ProductInformation]);

'PrdsP':

LOAD  ID as [%PrdsP.KEY]

  , UserTypeID as [PrdsP.UserTypeID]

    , Selected as [PrdsP.Selected]

    , Name as [PrdsP.Name]

FROM [$(vS_XML_Input)] (XmlSimple, Table is [ProductInformation/Products/Product]);

left join ('PrdsP')

LOAD WorkspaceID as %SPI_WorkspaceID.KEY

FROM [$(vS_XML_Input)] (XmlSimple, Table is [ProductInformation]);

'PrdsPP':

LOAD  ID as [%PrdsPP.KEY]

  , UserTypeID as [PrdsPP.UserTypeID]

    , Selected as [PrdsPP.Selected]

    , Name as [PrdsPP.Name]

FROM [$(vS_XML_Input)] (XmlSimple, Table is [ProductInformation/Products/Product/Product]);

left join ('PrdsPP')

LOAD ID as [%PrdsP.KEY]

FROM [$(vS_XML_Input)] (XmlSimple, Table is [ProductInformation/Products/Product]);

// End of [ar_EG##exported_0.xml] LOAD statements

END SUB

The input is like this:

<ProductInformation ExportTime="2016-04-25 11:19:28" ExportContext="es_AR" ContextID="ar_EG" WorkspaceID="Main" UseContextLocale="false">

  <Products>

    <Product ID="Product hierarchy root" UserTypeID="Product user-type root" Selected="false">

      <Name>Name1</Name>

      <Values>

        <Value AttributeID="ContextID">ROOT</Value>

      </Values>

      <Product ID="0001" UserTypeID="DomainCluster" Selected="false">

          <Name>Some text 1</Name>

      </Product>

      <Product ID="0002" UserTypeID="DomainCluster" Selected="false">

        <Name>Some text 2</Name>

      </Product>

      <SequenceProduct ID="Sequence1"/>

      <SequenceProduct ID="Sequence2"/>

      <SequenceProduct ID="Sequence3"/>

      <SequenceProduct ID="Sequence4"/>

    </Product>

    <Product ID="Product hierarchy root2" UserTypeID="Product user-type root" Selected="false">

      <Name>Name1</Name>

      <Values>

        <Value AttributeID="ContextID">ROOT</Value>

      </Values>

      <Product ID="0003" UserTypeID="DomainCluster" Selected="false">

        <Name>Some text 1</Name>

      </Product>

      <Product ID="0004" UserTypeID="DomainCluster" Selected="false">

        <Name>Some text 2</Name>

      </Product>

      <SequenceProduct ID="Sequence1"/>

      <SequenceProduct ID="Sequence2"/>

      <SequenceProduct ID="Sequence3"/>

      <SequenceProduct ID="Sequence4"/>

    </Product>

  </Products>

</ProductInformation>

Please let me know how I only get Product ID="Product hierarchy root" connected to Product ID = "0001" and "0002" and Product hierarchy root2" only connceted to Product ID="0003" and Product ID="0004".

[Anonymous]

Hi Floris,

You could try the following:

Product:

LOAD

  ID as [Product ID],

    UserTypeID,

    Selected,

    Name,

    %Key_Product_EC6DD09A05B9DEB5   

FROM xml_load_test.xml (XmlSimple, Table is [ProductInformation/Products/Product/Product]);

Left Join(Product)

LOAD

  ID as [Product Hierarchy ID],

    [Values/Value] as Value,

    [Values/Value/AttributeID] as AttributeID,

    %Key_ProductInformation_A0F1FCAB4B1D6F09,   

    %Key_Product_EC6DD09A05B9DEB5   

FROM xml_load_test.xml (XmlSimple, Table is [ProductInformation/Products/Product]);

Best Regards,

-- Karla

[#3]

Karla,

Thank you for this solution, this helps me a lot.

I see that within the load statements a %Key_.. is used with at the end some kind of Hexadecimal string. During my research I found out that this key definitions will automatically generate a value which can then be used as connection between the Parent and Child within an XML load.

I like to define/generate this string at the end of the %Key_.. fields myself.

At the moment I let the Wizard define these keys for me.

Is there any way I can define/generate this string myself?

With regards,

Floris