Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to store data from XML to Qlik

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

7 Replies
Frank_Hartmann
Honored Contributor

Re: How to store data from XML to Qlik

this might be helpful:

QlikView - Using the REST Connector

Not applicable

Re: How to store data from XML to Qlik

Hi Frank,

Thank you for the reply.

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

http://UserIDSmiley Tongueassword@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

Frank_Hartmann
Honored Contributor

Re: How to store data from XML to Qlik

i guess QV is not recognizing your file as xml:

www.xmlvalidation.com

Not applicable

Re: How to store data from XML to Qlik

Through above link I am getting error for Root Tag.

But in my file I have Root tag

Root --> Articles --> Article --> so on

Not applicable

Re: How to store data from XML to Qlik

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.

Frank_Hartmann
Honored Contributor

Re: How to store data from XML to Qlik

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

Not applicable

Re: How to store data from XML to Qlik

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.?

Community Browser