Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
;
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.
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 >
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
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>
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
;