Load xml file question

[alec1982]

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

[jonathandienst · Accepted solution]

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

[alec1982]

thank you Jonathan!