topic Re: Read CData in QlikView

Read CData

Tue, 11 Nov 2014 10:09:12 GMT

Good Evening at all.

i've search in other post, but i have not found a solution to my problem. I have to read an xml file with a cdata tag

<SQL>

<RECORD><code>12345678</code><desc>product1</desc></RECORD>

</SQL>

]]></Invio>

Thanks

Re: Read CData

agomes1971 — Tue, 11 Nov 2014 10:32:16 GMT

Hi,

see this: Loading XML file

Hope it helps

André Gomes

Re: Read CData

Gysbert_Wassenaar — Tue, 11 Nov 2014 10:33:36 GMT

Try using FROM_FIELD:

Invio:
LOAD Invio%Table
FROM [comm141596.xml] (XmlSimple, Table is [Invio]);

RECORD:
LOAD code,
    desc,
    RecNo() as recordno
FROM_FIELD (Invio, Invio%Table)  (XmlSimple, Table is [SQL/RECORD]);

See attached qvw.

Re: Read CData

Tue, 11 Nov 2014 15:52:20 GMT

Thanks a lot. I think it's a good solution for me, but i can't use it, because i've post an incorrect xml. I've make some try but i have problem.

The correct XML it's

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>

<SQL>

</SQL>

]]></Invio>

</SQL>

</Azienda>

</Aziende>

</TAR >

Re: Read CData

MarcoWedel — Tue, 11 Nov 2014 23:06:43 GMT

Hi,

one solution could be:

LOAD *,
    TextBetween(Invio,'<QtaVenduta>','</QtaVenduta>') as QtaVenduta,
    TextBetween(Invio,'<ImpVenduta>','</ImpVenduta>') as ImpVenduta
Where Invio like '<RECORD>*';
LOAD code,
    Timestamp(Timestamp#(PurgeChar(date,'T'),'YYYY-MM-DDhh:mm:ss')) as date,
    [Aziende/Azienda/nome] as nome,
    [Aziende/Azienda/SoftwareHouse] as SoftwareHouse,
    [Aziende/Azienda/lott] as lott,
    [Aziende/Azienda/SQL/scar] as scar,
    Timestamp(Timestamp#(PurgeChar([Aziende/Azienda/SQL/date],'T'),'YYYY-MM-DDhh:mm:ss'))as [SQL/date],
    SubField([Aziende/Azienda/SQL/Invio],chr(10)) as Invio,
    [Aziende/Azienda/SQL/Invio/ID] as ID,
    Timestamp(Timestamp#(PurgeChar([Aziende/Azienda/SQL/Invio/Data],'T'),'YYYY-MM-DDhh:mm:ss')) as Data,
    [Aziende/Azienda/SQL/Invio/Esito] as Esito,
    [Aziende/Azienda/Geo/Reg] as Reg
FROM [QlikCommunity_Thread_141596.xml] (XmlSimple, Table is [TAR]);

hope this helps

regards

Marco

Re: Read CData

Wed, 12 Nov 2014 07:18:14 GMT

Thanks, I've tried and it work.

But i see if I have multiple <Azienda> tag, the script load online the <QtaVenduta> and <ImpVenduta> of the first <Azienda> tag. How i read the XML until the end?

<SQL>

</SQL>

]]></Invio>

</SQL>

</Azienda>

<SQL>

</SQL>

]]></Invio>

</SQL>

</Azienda>

<SQL>

</SQL>

]]></Invio>

</SQL>

</Azienda>

</Aziende>

Re: Re: Read CData

Gysbert_Wassenaar — Wed, 12 Nov 2014 09:11:09 GMT

Try this:

TAR:
LOAD code,
    date,
    %Key_TAR_C53FF8CC6C4E49C9  
FROM [comm141596_TAR.xml] (XmlSimple, Table is [TAR]);

Azienda:
LOAD nome,
    SoftwareHouse,
    lott,
    [SQL/scar] as scar,
    [SQL/date],
    [SQL/Invio] as Invio,
    [SQL/Invio/ID] as ID,
    [SQL/Invio/Data] as Data,
    [SQL/Invio/Esito] as Esito,
    [Geo/Reg] as Reg,
    %Key_TAR_C53FF8CC6C4E49C9   
FROM [comm141596_TAR.xml] (XmlSimple, Table is [TAR/Aziende/Azienda]);

Records:
LOAD nome, 
  TextBetween(Record,'<QtaVenduta>','</QtaVenduta>') as QtaVenduta,  
    TextBetween(Record,'<ImpVenduta>','</ImpVenduta>') as ImpVenduta
Where SubStringCount(Record,'<QtaVenduta>')
; 
LOAD nome, subfield(Invio,'<RECORD>') as Record
Resident Azienda
;