Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for Data Integration and Data Analytics gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Load xml file question

hi guys,

how can I load the following xml into qv and get report date, report status, report_code, code, currency_desc, qualifier, volume as columns?

<report date="8-14-2015">

<report status="Live">

    <identification report_code="123" report_name="Sales Per Region" report_date="8-14-2015" report_time="200" report_version="100"/>

    <data>

      <row_ AAA code="11">

        <currency_desc>USD</currency_desc>

        <qualifier>BA</qualifier>

        <volume>50</volume>

      </row_AAA>

      <row_AAA code="MM">

        <currency_desc>EUR</currency_desc>

        <qualifier>BB</qualifier>

        <volume>200</volume>

      </row_AAA>

Thanks,

Alec

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Load xml file question

This is an improper XML file as there is no closing tag for the first two tags or for the data tag, and there is an invalid "row_ AAA" tag - or is that just how you cut and pasted?

Assuming that a valid form of the file is:

<report date="8-14-2015">

<report status="Live">

  <identification report_code="123" report_name="Sales Per Region" report_date="8-14-2015" report_time="200" report_version="100"/>

  <data>

    <row_AAA code="11">

      <currency_desc>USD</currency_desc>

      <qualifier>BA</qualifier>

      <volume>50</volume>

    </row_AAA>

    <row_AAA code="MM">

      <currency_desc>EUR</currency_desc>

      <qualifier>BB</qualifier>

      <volume>200</volume>

    </row_AAA>

  </data>

</report>

</report>

The load script would be:

report:

LOAD

    [report/identification/report_date] as report_date,

    [report/status] as report_status,

    [report/identification/report_code] as report_code,

    %Key_report_16926E6E9269865A   

FROM (XmlSimple, Table is [report]);

Join (report)

LOAD code,

    currency_desc,

    qualifier,

    volume,

    %Key_report_16926E6E9269865A   

FROM (XmlSimple, Table is [report/report/data/row_AAA]);

DROP Field %Key_report_16926E6E9269865A; // not needed any more

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

2 Replies
Highlighted
MVP
MVP

Re: Load xml file question

This is an improper XML file as there is no closing tag for the first two tags or for the data tag, and there is an invalid "row_ AAA" tag - or is that just how you cut and pasted?

Assuming that a valid form of the file is:

<report date="8-14-2015">

<report status="Live">

  <identification report_code="123" report_name="Sales Per Region" report_date="8-14-2015" report_time="200" report_version="100"/>

  <data>

    <row_AAA code="11">

      <currency_desc>USD</currency_desc>

      <qualifier>BA</qualifier>

      <volume>50</volume>

    </row_AAA>

    <row_AAA code="MM">

      <currency_desc>EUR</currency_desc>

      <qualifier>BB</qualifier>

      <volume>200</volume>

    </row_AAA>

  </data>

</report>

</report>

The load script would be:

report:

LOAD

    [report/identification/report_date] as report_date,

    [report/status] as report_status,

    [report/identification/report_code] as report_code,

    %Key_report_16926E6E9269865A   

FROM (XmlSimple, Table is [report]);

Join (report)

LOAD code,

    currency_desc,

    qualifier,

    volume,

    %Key_report_16926E6E9269865A   

FROM (XmlSimple, Table is [report/report/data/row_AAA]);

DROP Field %Key_report_16926E6E9269865A; // not needed any more

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

Highlighted
Specialist II
Specialist II

Re: Load xml file question

thank you Jonathan!