topic Re: How to load 2 xml from 1 txt-file in QlikView

How to load 2 xml from 1 txt-file

Thu, 05 Nov 2015 15:17:48 GMT

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

Re: How to load 2 xml from 1 txt-file

marcus_sommer — Fri, 06 Nov 2015 13:08:43 GMT

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

Re: How to load 2 xml from 1 txt-file

Sun, 08 Nov 2015 13:57:33 GMT

Marcus_Sommer Sadly no 😞

Re: How to load 2 xml from 1 txt-file

MarcoWedel — Mon, 09 Nov 2015 00:15:53 GMT

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

Re: How to load 2 xml from 1 txt-file

marcus_sommer — Mon, 09 Nov 2015 08:16:08 GMT

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