AnsweredAssumed Answered

Populate records from XML on the fly?!

Question asked by jurgmay on Mar 10, 2016
Latest reply on Mar 10, 2016 by taylorsharpe

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!

Outcomes