11 Replies Latest reply on Mar 10, 2016 7:31 PM by taylorsharpe

    Populate records from XML on the fly?!

    jurgmay

      I have a database that uses a third party web service to pass back a list of residential properties associated to a given postcode. The address data comes back from the web service as XML.

       

      What I need to do is somehow present the user with that list of addresses so that they can choose one. I do this using a portal at the moment.

       

      Here's an example of the XML that I get back from the web service:

       

      <?xml version="1.0" encoding="utf-8"?>
      <soap:Envelope xmlns:soap="http://www.w3.org/2003/05/soap-envelope" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <soap:Body>
          <GetFullAddressResponse xmlns="http://www.data-8.co.uk/integr8">
            <GetFullAddressResult>
              <Results>
                <FormattedAddress>
                  <Address>
                    <Lines>
                      <string>1 HIGH STREET</string>
                      <string>TOWN</string>
                      <string/>
                      <string/>
                      <string>CITY</string>
                      <string/>
                      <string>POSTCODE</string>
                    </Lines>
                  </Address>
                  <RawAddress>
                    <Organisation/>
                    <Department/>
                    <AddressKey>1234567</AddressKey>
                    <OrganisationKey>0</OrganisationKey>
                    <PostcodeType>S</PostcodeType>
                    <BuildingNumber>1</BuildingNumber>
                    <SubBuildingName/>
                    <BuildingName/>
                    <DependentThoroughfareName/>
                    <DependentThoroughfareDesc/>
                    <ThoroughfareName>NAME</ThoroughfareName>
                    <ThoroughfareDesc>DESC</ThoroughfareDesc>
                    <DoubleDependentLocality/>
                    <DependentLocality>LOCALITY</DependentLocality>
                    <Locality>LOCALITY</Locality>
                    <Postcode>POSTCODE</Postcode>
                    <Dps>11B</Dps>
                    <PoBox/>
                    <PostalCounty/>
                    <TraditionalCounty>COUNTY</TraditionalCounty>
                    <AdministrativeCounty>ADMIN COUNTY</AdministrativeCounty>
                  </RawAddress>
                </FormattedAddress>
                <FormattedAddress>
                  <Address>
                    <Lines>
                      <string>2 HIGH STREET</string>
                      <string>TOWN</string>
                      <string/>
                      <string/>
                      <string>CITY</string>
                      <string/>
                      <string>POSTCODE</string>
                    </Lines>
                  </Address>
                  <RawAddress>
                    <Organisation/>
                    <Department/>
                    <AddressKey>1234568</AddressKey>
                    <OrganisationKey>0</OrganisationKey>
                    <PostcodeType>S</PostcodeType>
                    <BuildingNumber>1</BuildingNumber>
                    <SubBuildingName/>
                    <BuildingName/>
                    <DependentThoroughfareName/>
                    <DependentThoroughfareDesc/>
                    <ThoroughfareName>NAME</ThoroughfareName>
                    <ThoroughfareDesc>DESC</ThoroughfareDesc>
                    <DoubleDependentLocality/>
                    <DependentLocality>LOCALITY</DependentLocality>
                    <Locality>LOCALITY</Locality>
                    <Postcode>POSTCODE</Postcode>
                    <Dps>11B</Dps>
                    <PoBox/>
                    <PostalCounty/>
                    <TraditionalCounty>COUNTY</TraditionalCounty>
                    <AdministrativeCounty>ADMIN COUNTY</AdministrativeCounty>
                  </RawAddress>
                </FormattedAddress>
              </Results>
            </GetFullAddressResult>
          </GetFullAddressResponse>
        </soap:Body>
      </soap:Envelope>
      

       

      I'm only interested in the 7 'string' values inside each '/Address/Lines/' node.

       

      At the moment I loop through the XML using the MBS plugin to pull out the values and create records in an 'Addresses' table like this:

       

      // THESE VARIABLE HAVE VALUES ALREADY
      //
      // $xml is the raw XML
      // $countResultLines is the number of unique addresses - this is passed back from the web service
      
      
      Set Variable [ $counter ; Value: 1 ] 
      
      
      Loop
        New Record/Request
      
      
        Set Field [ Addresses::add1 ; MBS ( "XML.Query"; $xml ; "string(/GetFullAddressResult/Results/FormattedAddress[" & $counter & "]/Address/Lines/string[1]/text() )" ) ] 
        Set Field [ Addresses::add2 ; MBS ( "XML.Query"; $xml ; "string(/GetFullAddressResult/Results/FormattedAddress[" & $counter & "]/Address/Lines/string[2]/text() )" ) ] 
        Set Field [ Addresses::add3 ; MBS ( "XML.Query"; $xml ; "string(/GetFullAddressResult/Results/FormattedAddress[" & $counter & "]/Address/Lines/string[3]/text() )" ) ] 
        Set Field [ Addresses::add4 ; MBS ( "XML.Query"; $xml ; "string(/GetFullAddressResult/Results/FormattedAddress[" & $counter & "]/Address/Lines/string[4]/text() )" ) ] 
        Set Field [ Addresses::add5 ; MBS ( "XML.Query"; $xml ; "string(/GetFullAddressResult/Results/FormattedAddress[" & $counter & "]/Address/Lines/string[5]/text() )" ) ] 
        Set Field [ Addresses::add6 ; MBS ( "XML.Query"; $xml ; "string(/GetFullAddressResult/Results/FormattedAddress[" & $counter & "]/Address/Lines/string[6]/text() )" ) ] 
        Set Field [ Addresses::postcode ; MBS ( "XML.Query"; $xml ; "string(/GetFullAddressResult/Results/FormattedAddress[" & $counter & "]/Address/Lines/string[7]/text() )" ) ] 
      
      
        Set Variable [ $counter ; Value: $counter + 1 ] 
      
      
        Exit Loop If [ $counter > $countResultLines ] 
      End Loop
      
      
      
      
      
      
      
      
      
      
      

       

      The trouble is that with just 40 addresses this can take 6 seconds which is far too long when using the database. I've got the web service communication part down to 1 second so it's taking 5 seconds to parse the XML and create the records in the Addresses table.

       

      Any advice on alternate approaches to solve this problem would be welcome.

       

      Thanks!