4 Replies Latest reply: Nov 9, 2015 3:16 AM by Marcus Sommer RSS

    How to load 2 xml from 1 txt-file

      Hi,

       

      I ve ~10k of txt-files, all containing 1 or 2 xml-file-data:

       

      text.txt:

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <OrderCheckRequest xmlns:ns2="http://www.example.org">
          <ns2:product>
        Data1
          </ns2:product>
      </OrderCheckRequest>
      
      
      
      
      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <OrderCheckResponse xmlns:ns2="http://www.example.org">
          <ns2:returnCode>1</ns2:returnCode>
          <ns2:product>
           Data2
          </ns2:product>
          
      </OrderCheckResponse>
      

       

      It's no problem to load the first part (OrderCheckRequest) out of all files, but I ve no idea how to load the second (OrderCheckResponse) within Qlikview.

       

      Has anyone an idea?

       

      Thanks

        • Re: How to load 2 xml from 1 txt-file
          Marcus Sommer

          It's only the table-wizard which showed only the first xml-table but if you write the second xml-load manually (copy+paste from first one and then change field- and table-names) you could load the second table without problems.

           

          - Marcus

              • Re: How to load 2 xml from 1 txt-file
                Marcus Sommer

                Regarding to your first xml-txt-file this worked:

                 

                // Start of [2xmlIn1.txt] LOAD statements

                OrderCheckRequest:

                LOAD [xmlns:ns2],

                    product

                FROM [D:\2xmlIn1.txt] (XmlSimple, Table is [OrderCheckRequest]);

                // End of [2xmlIn1.txt] LOAD statements

                 

                // Start of [2xmlIn1.txt] LOAD statements

                OrderCheckResponse:

                LOAD [ns2:returnCode], product

                FROM [D:\2xmlIn1.txt] (XmlSimple, Table is [OrderCheckResponse]);

                // End of [2xmlIn1.txt] LOAD statements

                 

                Your second xml-txt-file is different to them and I think your attempt failed because you referred to a field data which doesn't exists in this form. If your 10k on files are quite consistent you could use the above approach and needs to be only carefully by the few changes an field- and table-names. If your files aren't consistent you will need advanced approaches like suggested from marcowedel with some more logic included.

                 

                - Marcus

            • Re: How to load 2 xml from 1 txt-file
              Marco Wedel

              Hi,

               

              maybe something like this:

               

              QlikCommunity_Thread_190507_Pic1.JPG

               

              tabTempXML:
              LOAD [@1:n] as text,
                  RecNo() as SeqNo,
                  RangeSum(Peek(XMLNo),-([@1:n] like '<?xml*')) as XMLNo
              FROM [https://community.qlik.com/servlet/JiveServlet/download/907739-195811/2xmlIn1.txt] (fix, codepage is 1252);
              
              tabTempXML2:
              LOAD Concat(text, chr(13) & chr(10),SeqNo) as text,
                  XMLNo
              Resident tabTempXML
              Group By XMLNo;
              
              DROP Table tabTempXML;
              
              tabTempXML3:
              LOAD text as text2 Resident tabTempXML2 Where RecNo()=1;
              
              tabXML:
              LOAD data,
                  returnCode,
                  product
              From_Field (tabTempXML3, text2) (XmlSimple, Table is [OrderCheckRequest]);
              
              DROP Table tabTempXML3;
              
              tabTempXML3:
              LOAD text as text2 Resident tabTempXML2 Where RecNo()=2;
              
              tabXML:
              LOAD data,
                  returnCode,
                  product
              From_Field (tabTempXML3, text2) (XmlSimple, Table is [OrderCheckResponse]);
              
              DROP Table tabTempXML2, tabTempXML3;
              

               

              Using some code from Rob Wunderlich:Re: Reading multiple XML nodes

               

              hope this helps

               

              regards

               

              Marco