6 Replies Latest reply: Nov 12, 2014 4:11 AM by Gysbert Wassenaar RSS

    Read CData

    Marco Morgia

      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

        • Re: Read CData
          André Gomes

          Hi,

           

          see this: Loading XML file

           

          Hope it helps

           

          André Gomes

          • Re: Read CData
            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.

              • Re: Read CData
                Marco Morgia

                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
                Marco Wedel

                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

                  • Re: Read CData
                    Marco Morgia

                    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
                        Gysbert Wassenaar

                        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
                        ;