Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How to load 2 xml from 1 txt-file

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">

    <ns2Smiley Tongueroduct>

  Data1

    </ns2Smiley Tongueroduct>

</OrderCheckRequest>

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

<OrderCheckResponse xmlns:ns2="http://www.example.org">

    <ns2:returnCode>1</ns2:returnCode>

    <ns2Smiley Tongueroduct>

     Data2

    </ns2Smiley Tongueroduct>

   

</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

1 Solution

Accepted Solutions

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

Hi,

maybe something like this:

QlikCommunity_Thread_190507_Pic1.JPG

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

4 Replies

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

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

Not applicable

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

Marcus_Sommer Sadly no :-(

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

Hi,

maybe something like this:

QlikCommunity_Thread_190507_Pic1.JPG

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

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

Community Browser