Discussion Board for collaboration on QlikView Scripting.
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.
http://maps.googleapis.com/maps/api/geocode/xml?address=InputLocation&sensor=false", False] (XmlSimple, Table is [GeocodeResponse]);
Error: "Access is denied."
How to pass USerID and Password?
this might be helpful:
QlikView - Using the REST Connector
Thank you for the reply.
Now I am able to connect respective URL with UserID and Password.
Now I am trying to import data from XML to Qlik.
Attached a sample XML file.
The file comtain multiple tags with nested tags.
i guess QV is not recognizing your file as xml:
Through above link I am getting error for Root Tag.
But in my file I have Root tag
Root --> Articles --> Article --> so on
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
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.
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
I downloaded XML files from a Site using VBA
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"
'DOCURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Type = 1
oStream.SaveToFile ThisWorkbook.Sheets("Details").Range("B2").Value & "\" & Articleno & "_" & VariantNo & "_" & BundleNo & "_" & Language & ".xml", 1 ' 1 = no overwrite, 2 = overwrite
ThisWorkbook.Sheets("ListOfDoc").Cells(i, 2).Value = 1 'Setting a flag
Let vRoot = '*.xml';
Let i = 1;
for each vFile in filelist('*.xml')
TRACE i ;
Then import XML files into Qlik:
FileBaseName() as ArticleNo_A,
[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]);
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.?