Loading XML

Wed, 25 Nov 2020 16:16:04 GMT

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:

</Values>

</Product>

</Product>

</Product>

</Values>

</Product>

</Product>

</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".

Re: Loading XML

Anonymous — Sat, 28 May 2016 03:20:42 GMT

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

Re: Loading XML

Mon, 30 May 2016 07:51:44 GMT

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

topic Loading XML in QlikView

Loading XML

Re: Loading XML

Re: Loading XML