Importing Data with Web Services

The core of Slate data integration leverages the Query Builder and Upload Dataset tools to exchange information between systems.

For data integration via web services, any query can be routinely called as a web service or scheduled to push data into another system, while Upload Dataset can accept a push or pull batched data from another service.

The Query Builder tool is the primary method for exporting data. Query results may be delivered in several ways, including web services.

Pulling from a Remote Endpoint into Slate's Upload Dataset

The following are the steps to generate an endpoint that can be called via web services.  This import service runs once every 24 hoursThe two keys of this process are the "Import Remote Server" and "XML" settings on the source format. 

  1. Select Database on the top navigation bar and select Source Formats.
  2. Select New Source Format.
  3. Enter the Source Format Name and navigate to the Import Automation tab.

    • Import Remote Server is the URL of the web service to be called.  Request the complete remote server URL from the vendor providing your data.  The authentication credentials to access the remote server must be included.
    • The syntax for the URL should be:"username:password" followed by an "@" prior to the actual URL, i.e.: "sftp://username:password@studentdata.vendor.com"

      For reference, the Duolingo Integration documentation illustrates a specific use case of how this is configured with authentication.

    • Import Path/Mask identifies the file path Slate will use to locate the file once authenticated, i.e: vendor/listbuy_to_slate*.csv.  It is not necessary to include the /incoming root folder here as Slate automatically defaults to it. 
  4. Go to the Format Definition tab to complete the Source Format XML configuration.

    The "XML" Format Definition field for XML documents uses XPath syntax to tell Slate which elements represent a new record, and how relative to this element it should traverse the source document to find the relevant data points. This provides Slate with specific instructions for parsing the incoming data. Below is an example; also see the sample file attached at the end of this article:

    <layout type="xml" node="/persons/person">

      <f id="First Name" s="first" />

      <f id="Last Name" s="last" />

      <f id="Email" s="email" />

      <f id="Birthdate" s="birthdate" />

      <f id="School 1 Code" s="schools/school[1]/ceeb" />

      <f id="School 2 Code" s="schools/school[2]/ceeb" />

    </layout>

      Tip

    Need help creating a custom source format? Check out our article Source Format Library and Creating a New Custom Source Format.

    a. The <layout> parent node contains the paths to source fields contained within the file and has two attributes:

    • type which tells Slate which file type to expect - in this case, XML
    • node which tells Slate the path to each "row" of the file; in this case, there is a person node for each row contained within an overall persons node

    b. There is one <f> node for each source field that you will map:

    • The id attribute is the source field name you will map in the Upload Dataset tool. These should be friendly names which make sense to you and your staff so that the source fields can be easily and appropriately mapped to destinations.
    • s contains the path to the XML node containing the data.

    The first <f> node has s="first" which indicates that the First Name is stored in node at /persons/person/first.

    The second <f> node has s="last" indicating that the Last Name is stored at /persons/person/last.

    The third and fourth <f> nodes behavior similarly to First Name and Last Name, defining the appropriate path to single data elements.

    c. The fifth and sixth <f> nodes, however, demonstrate how to handle multirelational data - in this case, Schools:

    • The School 1 Code has a path (i.e., s attribute) of schools/school[1]/ceeb. Unlike the previous paths, this path contains square brackets with a number, [1], which indicates that we should pull the ceeb node from the first school node that we encounter under the schools node.
    • We get to the second school code by incrementing the number contained in the square brackets. This last <f> node demonstrates this functionality in referencing the second school and its ceeb node.
Pushing Data into Slate's Service Endpoint to Import Records Upload Dataset

Slate can provision an endpoint within your database to which you may push data. This data will appear as uploads in the Upload Dataset tool. Accepted data formats include: XML, JSON, ZIP, and DIP formats.

To set up this endpoint:

    1. Select Database on the top navigation bar and select User Permissions.
    2. Create a new User Account with a User Type of Service Account. The username and password may be of your choosing.
    3. Create a new Source Format.  Then update the Source Format "XML" configuration. The "XML" Format Definition field for XML documents uses XPath syntax to tell Slate which elements represent a new record, and how relative to this element it should traverse the source document to find the relevant data points. This provides Slate with specific instructions for parsing the incoming data. Below is an example; also see the sample file attached at the end of this article:

      <layout type="xml" node="/persons/person">

        <f id="First Name" s="first" />

        <f id="Last Name" s="last" />

        <f id="Email" s="email" />

        <f id="Birthdate" s="birthdate" />

        <f id="School 1 Code" s="schools/school[1]/ceeb" />

        <f id="School 2 Code" s="schools/school[2]/ceeb" />

      </layout>

        Tip

      Need help creating a custom source format? Check out our article Source Format Library and Creating a New Custom Source Format.

      a. The <layout> parent node contains the paths to source fields contained within the file and has two attributes:

      • type which tells Slate which file type to expect - in this case, XML
      • node which tells Slate the path to each "row" of the file; in this case, there is a person node for each row contained within an overall persons node

      b. There is one <f> node for each source field that you will map:

      • The id attribute is the source field name you will map in the Upload Dataset tool. These should be friendly names which make sense to you and your staff so that the source fields can be easily and appropriately mapped to destinations.
      • s contains the path to the XML node containing the data.

      The first <f> node has s="first" which indicates that the First Name is stored in node at /persons/person/first.

      The second <f> node has s="last" indicating that the Last Name is stored at /persons/person/last.

      The third and fourth <f> nodes behavior similarly to First Name and Last Name, defining the appropriate path to single data elements.

      c. The fifth and sixth <f> nodes, however, demonstrate how to handle multirelational data - in this case, Schools:

      • The School 1 Code has a path (i.e., s attribute) of schools/school[1]/ceeb. Unlike the previous paths, this path contains square brackets with a number, [1], which indicates that we should pull the ceeb node from the first school node that we encounter under the schools node.
      • We get to the second school code by incrementing the number contained in the square brackets. This last <f> node demonstrates this functionality in referencing the second school and its ceeb node.
    4. Get the service Endpoint: Once you have created your source format including the appropriate XML settings, save and re-open the Source Format main setting page to view the endpoint via the Web Services link.
    5. POST with a request body containing the XML, JSON, or file data that is being uploaded.
      The username and password need to be passed in this POST via an authorization header for basic authentication.

The following page link provides an example of how this might be done in C# - that said, the platform is agnostic, which means you can do a POST from any platform:  https://msdn.microsoft.com/en-us/library/system.net.networkcredential(v=vs.110).aspx

The data is consumed through the Upload Dataset mechanism. Results are displayed within the Upload Dataset tool for verification.

Note that most uploads are processed within 15 minutes, so you may not see the imported data instantaneously.

Was this article helpful?
1 out of 13 found this helpful