Read CData

[#1]

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

[agomes1971]

Hi,

see this: Loading XML file

Hope it helps

André Gomes

[Gysbert_Wassenaar]

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.

[#4]

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 >

[MarcoWedel]

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

[#6]

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>

[Gysbert_Wassenaar · Accepted solution]

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
;