Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to return hierarchical data from a web service in the load script of a Qlikview application with a single HTTP call. I have created a simple service to demonstrate.
This is the output from the web service XML:
Using the web files wizard in the script editor, the following script code is generated:
// Start of [HierarchicalData.asmx/hierarchicaldata] LOAD statements
'Children/HierarchicalDataSample':
LOAD Name,
Time,
%Key_HierarchicalDataSample_D1F3DE47113B8958 // Key to parent table: ArrayOfHierarchicalDataSample/HierarchicalDataSample
FROM [http://localhost:48453/Service.asmx/HierarchicalData] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample/Children/HierarchicalDataSample]);
'ArrayOfHierarchicalDataSample/HierarchicalDataSample':
LOAD Name,
Time,
%Key_ArrayOfHierarchicalDataSample_1509068996212F5B, // Key to parent table: ArrayOfHierarchicalDataSample
%Key_HierarchicalDataSample_D1F3DE47113B8958 // Key for this table: ArrayOfHierarchicalDataSample/HierarchicalDataSample
FROM [http://localhost:48453/Service.asmx/HierarchicalData] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample]);
ArrayOfHierarchicalDataSample:
LOAD xmlns:xsi,
xmlns:xsd,
xmlns,
%Key_ArrayOfHierarchicalDataSample_1509068996212F5B // Key for this table: ArrayOfHierarchicalDataSample
FROM [http://localhost:48453/Service.asmx/HierarchicalData] (XmlSimple, Table is [ArrayOfHierarchicalDataSample]);
// End of [HierarchicalData.asmx/hierarchicaldata] LOAD statements
This results in 3 separate HTTP connections being made to the web service:
This might be ok for non volatile data, but is not acceptable for us as it could result in inconsistencies in the data being returned (e.g. if the data changes between the time that the first and last of the web service calls are made).
The other approach I tried was manually modifying the extract script to access the child data within the hierarchy:
MultipleLevels:
LOAD Name as ParentName,
Time as ParentTime,
[Children/HierarchicalDataSample/Name] as [ChildName]
FROM [http://localhost:48453/Service.asmx/HierarchicalData] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample]);
This results in a single call being made to the web service, but QV is only returning the first child element (Mary in this example).
Hopefully I'm just doing something stupid. If anyone has any ideas on how I can access hierarchical data from a web service in a single call, I'd appreciate any pointers. I'm ideally looking to achieve this with the native functionality in the load script (as opposed to using macros).
Thanks in advance.
I have come up with a work around to extract hierarchical web service data in a single HTTP call. It's not ideal, but it works, avoids the potential for data inconsistencies, doesn't require writing data to an XML file, works around the bug with only the first child being returned, and doesn't use macros.
This is the basic approach:
Sample script below:
RawXmlTable:
LOAD @1:n AS RawXml ,RowNo() AS RowNumber FROM [http://localhost:48453/Service.asmx/HierarchicalData] (fix, codepage is 1252);
XmlStringTable:
LOAD CONCAT(RawXml,'',RowNumber) AS XmlStringField Resident RawXmlTable;
LET XmlString = Peek('XmlStringField');
TRACE $(XmlString);
DROP TABLE RawXmlTable;
DROP TABLE XmlStringTable;
// Start of [HierarchicalData.asmx/hierarchicaldata] LOAD statements
'Children/HierarchicalDataSample':
LOAD Name as ChildName,
Time as ChildTime,
%Key_HierarchicalDataSample_D1F3DE47113B8958 // Key to parent table: ArrayOfHierarchicalDataSample/HierarchicalDataSample
INLINE [$(XmlString)] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample/Children/HierarchicalDataSample]);
'ArrayOfHierarchicalDataSample/HierarchicalDataSample':
LOAD Name,
Time,
%Key_ArrayOfHierarchicalDataSample_1509068996212F5B, // Key to parent table: ArrayOfHierarchicalDataSample
%Key_HierarchicalDataSample_D1F3DE47113B8958 // Key for this table: ArrayOfHierarchicalDataSample/HierarchicalDataSample
INLINE [$(XmlString)] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample]);
ArrayOfHierarchicalDataSample:
LOAD xmlns:xsi,
xmlns:xsd,
xmlns,
%Key_ArrayOfHierarchicalDataSample_1509068996212F5B // Key for this table: ArrayOfHierarchicalDataSample
INLINE [$(XmlString)] (XmlSimple, Table is [ArrayOfHierarchicalDataSample]);
// End of [HierarchicalData.asmx/hierarchicaldata] LOAD statements
This results in the following table structure:
And returns the correct data:
It's not ideal, but it does avoid:
Hope this helps someone. If anyone has any better ideas, I'm all ears!
Cheers,
Graeme
I have come up with a work around to extract hierarchical web service data in a single HTTP call. It's not ideal, but it works, avoids the potential for data inconsistencies, doesn't require writing data to an XML file, works around the bug with only the first child being returned, and doesn't use macros.
This is the basic approach:
Sample script below:
RawXmlTable:
LOAD @1:n AS RawXml ,RowNo() AS RowNumber FROM [http://localhost:48453/Service.asmx/HierarchicalData] (fix, codepage is 1252);
XmlStringTable:
LOAD CONCAT(RawXml,'',RowNumber) AS XmlStringField Resident RawXmlTable;
LET XmlString = Peek('XmlStringField');
TRACE $(XmlString);
DROP TABLE RawXmlTable;
DROP TABLE XmlStringTable;
// Start of [HierarchicalData.asmx/hierarchicaldata] LOAD statements
'Children/HierarchicalDataSample':
LOAD Name as ChildName,
Time as ChildTime,
%Key_HierarchicalDataSample_D1F3DE47113B8958 // Key to parent table: ArrayOfHierarchicalDataSample/HierarchicalDataSample
INLINE [$(XmlString)] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample/Children/HierarchicalDataSample]);
'ArrayOfHierarchicalDataSample/HierarchicalDataSample':
LOAD Name,
Time,
%Key_ArrayOfHierarchicalDataSample_1509068996212F5B, // Key to parent table: ArrayOfHierarchicalDataSample
%Key_HierarchicalDataSample_D1F3DE47113B8958 // Key for this table: ArrayOfHierarchicalDataSample/HierarchicalDataSample
INLINE [$(XmlString)] (XmlSimple, Table is [ArrayOfHierarchicalDataSample/HierarchicalDataSample]);
ArrayOfHierarchicalDataSample:
LOAD xmlns:xsi,
xmlns:xsd,
xmlns,
%Key_ArrayOfHierarchicalDataSample_1509068996212F5B // Key for this table: ArrayOfHierarchicalDataSample
INLINE [$(XmlString)] (XmlSimple, Table is [ArrayOfHierarchicalDataSample]);
// End of [HierarchicalData.asmx/hierarchicaldata] LOAD statements
This results in the following table structure:
And returns the correct data:
It's not ideal, but it does avoid:
Hope this helps someone. If anyone has any better ideas, I'm all ears!
Cheers,
Graeme