Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Read CData

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

<Invio Data="2014-11-07T17:21:57"><![CDATA[<?xml version="1.0" encoding="ISO-8859-1"?>

<SQL>

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

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

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

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

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

</SQL>

]]></Invio>

Thanks

1 Solution

Accepted Solutions

Re: Re: Read CData

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

;


talk is cheap, supply exceeds demand
6 Replies
agomes1971
Valued Contributor

Re: Read CData

Hi,

see this: Loading XML file

Hope it helps

André Gomes

Re: Read CData

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Read CData

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

<TAR code="XXXX" date="2014-11-11T16:25:15">

  <Aziende>

  <Azienda nome="XXXXXXXXXXX" SoftwareHouse="XXXXX" Codice="" lott="XXXXXXXXXX">

  <Geo Reg="xxxxx" />

  <SQL scar="X" date="2014-11-11T10:04:23" >

  <Invio ID="X" Data="2014-11-11T10:04:28" Esito="0"><![CDATA[<?xml version="1.0" encoding="ISO-8859-1"?>

<SQL>

<RECORD><QtaVenduta>18</QtaVenduta><ImpVenduta>49,33</ImpVenduta></RECORD>

</SQL>

]]></Invio>

  </SQL>

  </Azienda>

  </Aziende>

</TAR >

Re: Read CData

Hi,

one solution could be:

QlikCommunity_Thread_141596_Pic1.JPG.jpg

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

Not applicable

Re: Read CData

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?

<Aziende>

  <Azienda nome="XXXXXXXXXXX" SoftwareHouse="XXXXX" Codice="" lott="XXXXXXXXXX">

  <Geo Reg="xxxxx" />

  <SQL scar="X" date="2014-11-11T10:04:23" >

  <Invio ID="X" Data="2014-11-11T10:04:28" Esito="0"><![CDATA[<?xml version="1.0" encoding="ISO-8859-1"?>

<SQL>

<RECORD><QtaVenduta>18</QtaVenduta><ImpVenduta>49,33</ImpVenduta></RECORD>

</SQL>

]]></Invio>

  </SQL>

  </Azienda>

  <Azienda nome="XXXXXXXXXXX" SoftwareHouse="XXXXX" Codice="" lott="XXXXXXXXXX">

  <Geo Reg="xxxxx" />

  <SQL scar="X" date="2014-11-11T10:04:23" >

  <Invio ID="X" Data="2014-11-11T10:04:28" Esito="0"><![CDATA[<?xml version="1.0" encoding="ISO-8859-1"?>

<SQL>

<RECORD><QtaVenduta>18</QtaVenduta><ImpVenduta>49,33</ImpVenduta></RECORD>

</SQL>

]]></Invio>

  </SQL>

  </Azienda>

  <Azienda nome="XXXXXXXXXXX" SoftwareHouse="XXXXX" Codice="" lott="XXXXXXXXXX">

  <Geo Reg="xxxxx" />

  <SQL scar="X" date="2014-11-11T10:04:23" >

  <Invio ID="X" Data="2014-11-11T10:04:28" Esito="0"><![CDATA[<?xml version="1.0" encoding="ISO-8859-1"?>

<SQL>

<RECORD><QtaVenduta>18</QtaVenduta><ImpVenduta>49,33</ImpVenduta></RECORD>

</SQL>

]]></Invio>

  </SQL>

  </Azienda>

</Aziende>

Re: Re: Read CData

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

;


talk is cheap, supply exceeds demand
Community Browser