7 Replies Latest reply: Mar 29, 2017 7:33 AM by Deepika Shinde RSS

    How to store data from XML to Qlik

    Deepika Shinde

      Hi,

       

      I have XML on http (API) which have authentication.The XML  contain multiple -nested Tag.I am trying to import XML to Qlik.There are more then 60000 XMLs.

       

      I am trying to load few Attributes to a table ,but I am facing an issue as the API is password protected.How to pass UserID and Password to get the data.

       

      Example:

      GeocodeResponse:

      //   [Attributes_Name]

      BookID,BookName,Author 

       

        FROM [

      http://maps.googleapis.com/maps/api/geocode/xml?address=InputLocation&sensor=false", False] (XmlSimple, Table is [GeocodeResponse]);

        sleep 500;

       

      Error: "Access is denied."

      How to pass USerID and Password?

       

      Regards,

      Deepika

          • Re: How to store data from XML to Qlik
            Deepika Shinde

            Hi Frank,

             

            Thank you for the reply.

            Now I am able to connect respective URL with UserID and Password.

             

            http://UserID:Password@maps.googleapis.com/maps/api/geocode/xml?address=InputLocation&sensor=false

             

            Solved !

             

            Now I am trying to import data from XML to Qlik.

            Attached a sample XML file.

             

            The file comtain multiple tags with nested tags.

             

            Regards

            Deepika

              • Re: How to store data from XML to Qlik
                Frank Hartmann

                i guess QV is not recognizing your file as xml:

                www.xmlvalidation.com

                • Re: How to store data from XML to Qlik
                  Deepika Shinde

                  Hi,

                   

                  I am able to import XML file in Qlik using "Table Files" option. :-)

                   

                  But I am facing issue as few data are missing .

                   

                  Structure of XML

                  Article

                      --> Feature

                                --> Complex

                                                --> LeafElement

                                --> Simple

                   

                   

                  i,e   3 nested tages

                   

                  Using"Table Files" I am able to till Complex tag only

                  Say Complex Tag contain 9 LeafElement,but in Qlik it extract only one LeafElement and not all 9 LeafElement.

                   

                  When I import this XML in Excel ,I get all the Tages but not in Qlik.

                    • Re: How to store data from XML to Qlik
                      Frank Hartmann

                      if you erase the hyphens in the xml then QV will recognize the file as xml.

                      then you should be able to read the file into QV as xml without loosing structure/information.

                       

                       

                       

                      hope this helps

                        • Re: How to store data from XML to Qlik
                          Deepika Shinde

                          Thank Frank!

                           

                          I downloaded XML files from a Site using VBA

                          Sub DownloadFile()

                           

                           

                          Dim MainURL As String

                          Dim DOCURL As String

                          Dim RowCount As Long

                          Dim Articleno As String

                          Dim VariantNo As String

                          Dim BundleNo As String

                          Dim r As Integer

                           

                          MainURL = "MainULR"

                          Dim WinHttpReq As Object

                          RowCount = Sheets("ListOfDoc").UsedRange.Rows.Count

                           

                           

                          For i = 1 To RowCount

                              If ThisWorkbook.Sheets("ListOfDoc").Cells(i, 2).Value = "" Then 'checking a flag

                              DOCURL = MainURL & ThisWorkbook.Sheets("ListOfDoc").Cells(i, 1).Value

                            

                              Articleno = ThisWorkbook.Sheets("ListOfDoc").Cells(i, 1).Value

                              Articleno = Mid(Articleno, InStr(Articleno, "mmsArtNo=") + 9, InStr(Articleno, "&variantNo=") - (InStr(Articleno, "mmsArtNo=") + 9))

                              VariantNo = ThisWorkbook.Sheets("ListOfDoc").Cells(i, 1).Value

                              VariantNo = Mid(VariantNo, InStr(VariantNo, "&variantNo=") + 11, InStr(VariantNo, "&bundleNo") - (InStr(VariantNo, "&variantNo=") + 11))

                              Language = Right(ThisWorkbook.Sheets("ListOfDoc").Cells(i, 1).Value, 2)

                              BundleNo = ThisWorkbook.Sheets("ListOfDoc").Cells(i, 1).Value

                              BundleNo = Mid(BundleNo, InStr(BundleNo, "&bundleNo=") + 10, InStr(BundleNo, "&language=") - (InStr(BundleNo, "&bundleNo=") + 10))

                           

                           

                              Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")

                              WinHttpReq.Open "GET", DOCURL, False, "deepika.shinde", "Pass-word-24"

                              WinHttpReq.send

                            

                              'DOCURL = WinHttpReq.responseBody

                              If WinHttpReq.Status = 200 Then

                                  Set oStream = CreateObject("ADODB.Stream")

                                  oStream.Open

                                  oStream.Type = 1

                              

                                  oStream.Write WinHttpReq.responseBody

                                  oStream.SaveToFile ThisWorkbook.Sheets("Details").Range("B2").Value & "\" & Articleno & "_" & VariantNo & "_" & BundleNo & "_" & Language & ".xml", 1 ' 1 = no overwrite, 2 = overwrite

                                  oStream.Close

                              End If

                              ThisWorkbook.Sheets("ListOfDoc").Cells(i, 2).Value = 1 'Setting a flag

                             End If

                          Next i

                           

                           

                          End Sub

                           

                           

                          Let vRoot = '*.xml';

                          Let i = 1;

                           

                           

                          for each vFile in filelist('*.xml')

                            TRACE i ;

                           

                          Then import XML files into Qlik:

                          Code:

                           

                          Article:

                            LOAD

                            FileBaseName() as ArticleNo_A,

                            PLKZ,

                            ART_NO,

                            VAR_NO,

                            BUNDLE_NO,

                            SUBSYS_NO,

                            MISC_ID,

                            PDF_URL,

                            [Features/SimpleFeature/MISC_feature_ID] as [SimpleFeature/MISC_feature_ID],

                            [Features/SimpleFeature/MISC_feature_Label] as [SimpleFeature/MISC_feature_Label],

                            [Features/SimpleFeature/MISC_feature_SortId] as [SimpleFeature/MISC_feature_SortId],

                            [Features/SimpleFeature/MISC_feature_Uom] as [SimpleFeature/MISC_feature_Uom],

                            [Features/SimpleFeature/MISC_feature_Value] as [SimpleFeature/MISC_feature_Value],

                            [Features/SimpleFeature/MISC_feature_type] as [SimpleFeature/MISC_feature_type],

                            [Features/SimpleFeature/MISC_feature_norm_label] as [SimpleFeature/MISC_feature_norm_label],

                            [MISC_Properties/MISC_Language] as MISC_Language,

                            [MISC_Properties/MISC_ComText2] as MISC_ComText2,

                            [MISC_Properties/MISC_ComTextWebshop] as MISC_ComTextWebshop,

                            FileBaseName() & %Key_Article_CCAD92DEFFCA5924 as Key_Article   // Key for this table: Root/Articles/Article

                            FROM $(vFile) (XmlSimple, Table is [Root/Articles/Article]);

                          i = i + 1;

                          Next vFile

                           


                          But above process takes around 16 hours  for approx 79000 records (7 hours to download approx 79000 xml files and 9 hours to import 79000 xml files to Qlik).

                           

                          Is there any other way which will be more faster than this.?