How to load 2 xml from 1 txt-file

[#1]

Hi,

I ve ~10k of txt-files, all containing 1 or 2 xml-file-data:

text.txt:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<OrderCheckRequest xmlns:ns2="http://www.example.org">
    <ns2:product>
  Data1
    </ns2:product>
</OrderCheckRequest>




<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<OrderCheckResponse xmlns:ns2="http://www.example.org">
    <ns2:returnCode>1</ns2:returnCode>
    <ns2:product>
     Data2
    </ns2:product>

</OrderCheckResponse>

It's no problem to load the first part (OrderCheckRequest) out of all files, but I ve no idea how to load the second (OrderCheckResponse) within Qlikview.

Has anyone an idea?

Thanks

[marcus_sommer]

It's only the table-wizard which showed only the first xml-table but if you write the second xml-load manually (copy+paste from first one and then change field- and table-names) you could load the second table without problems.

- Marcus

[#3]

Marcus_Sommer Sadly no 😞

[MarcoWedel · Accepted solution]

Hi,

maybe something like this:

tabTempXML:
LOAD [@1:n] as text,
    RecNo() as SeqNo,
    RangeSum(Peek(XMLNo),-([@1:n] like '<?xml*')) as XMLNo
FROM [https://community.qlik.com/servlet/JiveServlet/download/907739-195811/2xmlIn1.txt] (fix, codepage is 1252);

tabTempXML2:
LOAD Concat(text, chr(13) & chr(10),SeqNo) as text,
    XMLNo
Resident tabTempXML
Group By XMLNo;

DROP Table tabTempXML;

tabTempXML3:
LOAD text as text2 Resident tabTempXML2 Where RecNo()=1;

tabXML:
LOAD data,
    returnCode,
    product
From_Field (tabTempXML3, text2) (XmlSimple, Table is [OrderCheckRequest]);

DROP Table tabTempXML3;

tabTempXML3:
LOAD text as text2 Resident tabTempXML2 Where RecNo()=2;

tabXML:
LOAD data,
    returnCode,
    product
From_Field (tabTempXML3, text2) (XmlSimple, Table is [OrderCheckResponse]);

DROP Table tabTempXML2, tabTempXML3;

Using some code from Rob Wunderlich:Re: Reading multiple XML nodes

hope this helps

regards

Marco

[marcus_sommer]

Regarding to your first xml-txt-file this worked:

// Start of [2xmlIn1.txt] LOAD statements

OrderCheckRequest:

LOAD [xmlns:ns2],

    product

FROM (XmlSimple, Table is [OrderCheckRequest]);

// End of [2xmlIn1.txt] LOAD statements

// Start of [2xmlIn1.txt] LOAD statements

OrderCheckResponse:

LOAD [ns2:returnCode], product

FROM (XmlSimple, Table is [OrderCheckResponse]);

// End of [2xmlIn1.txt] LOAD statements

Your second xml-txt-file is different to them and I think your attempt failed because you referred to a field data which doesn't exists in this form. If your 10k on files are quite consistent you could use the above approach and needs to be only carefully by the few changes an field- and table-names. If your files aren't consistent you will need advanced approaches like suggested from MarcoWedel with some more logic included.

- Marcus