1 Reply Latest reply: Oct 16, 2012 12:44 PM by Graeme Smith RSS

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

    Graeme Smith

      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.

        • Re: Access Hierarchical Data from Web Service without Multiple HTTP Calls in Load Script
          Graeme Smith

          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