Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Access Hierarchical Data from Web Service without Multiple HTTP Calls in Load Script

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:

SampleXML.png

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:

HttpConnections.png

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

LostChild.png

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.

1 Solution

Accepted Solutions
Not applicable
Author

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:

  1. Load the XML into a fixed width field
  2. Concatenate the field values into a variable
  3. Load the XML data as per the default script structure as generated by QV, but replace the web service calls with inline loads from your variable.

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:

FoundChildTables.png

And returns the correct data:

FoundChild.png

It's not ideal, but it does avoid:

  • the issue where only the first child node value is being returned
  • macros
  • writing/reading an XML file
  • risk of returning inconsistent data by making multiple HTTP calls


Hope this helps someone.  If anyone has any better ideas, I'm all ears!

Cheers,

Graeme


View solution in original post

1 Reply
Not applicable
Author

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:

  1. Load the XML into a fixed width field
  2. Concatenate the field values into a variable
  3. Load the XML data as per the default script structure as generated by QV, but replace the web service calls with inline loads from your variable.

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:

FoundChildTables.png

And returns the correct data:

FoundChild.png

It's not ideal, but it does avoid:

  • the issue where only the first child node value is being returned
  • macros
  • writing/reading an XML file
  • risk of returning inconsistent data by making multiple HTTP calls


Hope this helps someone.  If anyone has any better ideas, I'm all ears!

Cheers,

Graeme