Parse database field consisting of XML - Qlik Community

Report Inappropriate Content · ‎2012-02-20

Hi,

I have a field in one of my database tables which consists of a piece of XML. The table itself is a list of currencies, and the XML field is labelled 'exchange_rate' which consists of exchange rates from the currency of the current row into all other countries. Here is an example of the XML contained within just one of these fields, the following is an example of the 'exchage_rate' field for the currency with name 'EUR':

<to-currency csymbol="EUR" cname="Euro" base-exchange-rate="1.3162405881282215">

<cname>Australia Dollars</cname>

</currency>

<cname>Canada Dollars</cname>

</currency>

<cname>China Yuan Renminbi</cname>

</currency>

<cname>United Kingdom Pounds</cname>

</currency>

<cname>Hong Kong Dollars</cname>

</currency>

<cname>Indonesia Rupiahs</cname>

</currency>

<cname>Japan Yen</cname>

</currency>

<cname>United States Dollars</cname>

</currency>

</to-currency>

Is there a way to parse this data in the Qlikview load script so that it is easily accessible in a table in my Qlikview document?

Thanks,

Peter

deepakk · ‎2012-02-20

HI ,

Try the attach application.

Go to tablefile (in script). Select xml from the file type. A wizard will appear. On the left select Xml and click on Finish.

I hope this what you are looking for.

Deepak

Report Inappropriate Content · ‎2012-02-20

Thanks for your reply,

I'm not sure this solution will work, as the XML I attached is just one entry in an existing table in the database, it is not contained in an external XML file (such as 01.xml in your example).

Is there a way I can parse XML which is contained within a field in the database?

Thanks again,

Peter

deepakk · ‎2012-02-21

hi Peter,

Try storing the Data into xml.

Eg:

Test:

Load * from table.

Store Test into Test.xml;

I am assuming you have only one row. If you have multiple you need to take only the required row.

ONce you have the XML use like the above method.

Deepak

Report Inappropriate Content · ‎2012-02-23

Hi Deepak,

I still have not managed to get this working.

Let me paste in some examples....

The following is what appears in my load script:

currency_xml:

LOAD `exchange_rate`;

SQL SELECT *

FROM `prod`.`currency` WHERE `currency_id`='1';

STORE currency_xml into Test.xml;

This loads the 'exchange_rate' field for the first entry in the 'currency' table. This field contains the following data:

<to-currency csymbol="USD" cname="United States Dollars" base-exchange-rate="1.0">

<cname>Australia Dollars</cname>

</currency>

<cname>Canada Dollars</cname>

</currency>

<cname>China Yuan Renminbi</cname>

</currency>

</currency>

<cname>United Kingdom Pounds</cname>

</currency>

<cname>Hong Kong Dollars</cname>

</currency>

<cname>Indonesia Rupiahs</cname>

</currency>

<cname>Japan Yen</cname>

</currency>

</to-currency>

However, when I look at the Test.xml file which the load script produces, I see the following:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<CreatorDoc>C:\QlikView\Production\Sources\QVDs\Create_prod_QVDs.qvw</CreatorDoc>

<TableName>currency_xml</TableName>

<FieldName>exchange_rate</FieldName>

<Type>UNKNOWN</Type>

</NumberFormat>

</QvdFieldHeader>

</Fields>

<Statement>SQL SELECT *

FROM `prod`.`currency`</Statement>

</LineageInfo>

<Statement>SQL SELECT *

FROM `prod`.`currency` WHERE `currency_id`='1'</Statement>

</LineageInfo>

<Statement>currency:

LOAD `currency_id`,

`short_name`,

`full_name`,

`exchange_rate`,

created,

`last_update`,

`created_by`,

`last_updated_by`,

status;SQL SELECT *

FROM `prod`.`currency`</Statement>

</LineageInfo>

<Statement>currency_xml:

LOAD `exchange_rate`;SQL SELECT *

FROM `prod`.`mb_currency` WHERE `currency_id`='1'</Statement>

</LineageInfo>

</Lineage>

</QvdTableHeader>

<to-currency csymbol="USD" cname="United States Dollars" base-exchange-rate="1.0">

<cname>Australia Dollars</cname>

</currency>

<cname>Canada Dollars</cname>

</currency>

<cname>China Yuan Renminbi</cname>

</currency>

</currency>

<cname>United Kingdom Pounds</cname>

</currency>

<cname>Hong Kong Dollars</cname>

</currency>

<cname>Indonesia Rupiahs</cname>

</currency>

<cname>Japan Yen</cname>

</currency>

</to-currency>

Consequently, when I go to my Qlikview document and try and load the xml file via the 'Table files...' button, it thinks the XML ends after the </QvdTableHeader> tag and does not allow me to load the data that I am actually interested in.

Is there a way to save only the XML contained within the database filed into the Test.xml file without all of the associated metadata?

Thanks again,

Peter

deepakk · ‎2012-03-01

Hi Peter,

I used macro to convert the data into xml. This xml can then be read by qlikview.

Please check the attach file.

Deepak

Report Inappropriate Content · ‎2013-02-22

Hi Penfold,

I am also looking for some similar kind of solution...Can you please let me know if you did solve it out?

Thanks,

Sujith.

deepakk · ‎2013-02-24

Hi Sujith,

Try the attach application.

Regards

Deepak

Report Inappropriate Content · ‎2013-02-25

Hi Deepak,

Thanks for your help,this is what exactly what i was looking for,but my question here is how and where did you get the data into tables Data-1 and Data-2? How did you load them from the sql table?Can you please explain me in detail.

Thanks,

Sujith.