# How to Work with REST APIs with CData Solutions

**URL:** <https://community.cdata.com/t/how-to-work-with-rest-apis-with-cdata-solutions/135>\
**Category:** How-to with CData\
**Tags:** article, driver, api, rest\
**Created:** [March 31, 2023, 4:50pm UTC](https://community.cdata.com/t/how-to-work-with-rest-apis-with-cdata-solutions/135 "2023-03-31T16:50:53Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![jerodj-cdata](https://sea3.discourse-cdn.com/cdata/user_avatar/community.cdata.com/jerodj-cdata/32/7_2.png) [@jerodj-cdata](https://community.cdata.com/u/jerodj-cdata)\
**Post date:** [March 31, 2023, 4:50pm UTC](https://community.cdata.com/t/how-to-work-with-rest-apis-with-cdata-solutions/135/1 "2023-03-31T16:50:53Z")

</div>

The CData Drivers for REST provide standards-based access to REST resources by modeling local and remote data as relational tables, views, and stored procedures. The high-performance processing operations hide the complexity of making HTTP requests and processing the response, allowing you to interact with REST resources using the familiarity and utility of SQL-92 queries.

In this article, we will look at some sample REST data and discuss how to configure the CData Drivers for REST to work with that data.

## Connecting to a REST Resource

* * *

For this section, we are connecting to a REST resource with a single endpoint. The data returned is the City of Los Angeles 2010 Census data, grouped by ZIP code.

### Data Extract

Below is an extract of the data found in the sample REST resource.

```auto
<response> <row> <row _id="228" _uuid="A47815EC-0DA0-499D-9CE6-616A23C4F155" _position="228" _address="http://data.lacity.org/resource/nxs9-385f/228"> <zip_code>91371</zip_code> <total_population>1</total_population> <median_age>73.5</median_age> <total_males>0</total_males> <total_females>1</total_females> <total_households>1</total_households> <average_household_size>1.00</average_household_size> </row> <row _id="1" _uuid="1AE8EAF5-D6E6-4AF9-88FD-3CF179F2A8FC" _position="1" _address="http://data.lacity.org/resource/nxs9-385f/1"> <zip_code>90001</zip_code> <total_population>57110</total_population> <median_age>26.6</median_age> <total_males>28468</total_males> <total_females>28642</total_females> <total_households>12971</total_households> <average_household_size>4.40</average_household_size> </row> ... </row></response>

```

### Configuring the Connection

Now that we know what the data for the REST resource looks like, we are ready to configure the connection. The HTTP endpoint for the 2010 Census data for Los Angeles is available (as XML) at the URL [https://data.lacity.org/api/views/nxs9-385f/rows.xml](https://data.lacity.org/api/views/nxs9-385f/rows.xml). Use this value for the **URI** property.

Because the REST resource returns XML data, set the **Format** property to “XML”. Set the **XPath** property to “/response/row/row” so that each repeated row child element appears as a single row in the data table representation of the REST resource. With these properties set, you are ready to begin working with the data as a data table instead of a raw REST resource. The CData Drivers for REST parse the XML data and expose all of the available data in each row element, including the child elements and the XML attributes of each row.

If you wish to configure the driver to automatically generate schema file(s) set **GenerateSchemaFiles** to “OnUse” or “OnStart” and set **Location** to an accessible directory on disk. The completed connection string follows:

URI=[https://data.lacity.org/api/views/nxs9-385f/rows.xml](https://data.lacity.org/api/views/nxs9-385f/rows.xml); Format=XML; XPath=/response/row/row; GenerateSchemaFiles=OnStart; Location=C:/Users/Administrator/MySchemaDirectory;

Using this connection string, the driver exposes a single table named XMLData that has as its columns the attributes and child elements in each row element of the data. Immediately below is a table containing a subset of returned data (some of which has been truncated for readability). In the section following, we provide a explanation of the generated schema file for the census data and an example of a custom schema file.

#### SELECT \* FROM XMLData

| \_address | \_id | \_position | \_uuid | average\_household\_size | median\_age | total\_females | total\_households | total\_males | total\_population | zip\_code |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| https://…/1 | 1 | 1 | 1AE8EAF5-… | 4.4 | 26.6 | 28642 | 12971 | 28468 | 57110 | 90001 |
| https://…/2 | 2 | 2 | D2556F56-… | 4.36 | 25.5 | 26347 | 11731 | 24876 | 51223 | 90002 |
| https://…/3 | 3 | 3 | 5586CDA7-… | 4.22 | 26.3 | 33635 | 15642 | 32631 | 66266 | 90003 |
| https://…/4 | 4 | 4 | D14749EB-… | 2.73 | 34.8 | 30878 | 22547 | 31302 | 62180 | 90004 |
| https://…/5 | 5 | 5 | 3F9BD39B-… | 2.5 | 33.9 | 18382 | 15044 | 19299 | 37681 | 90005 |

## &nbsp;

## Modeling REST Resources as Databases

* * *

If the REST resource you are connecting to has a single endpoint, then using the connection string properties is the best way to create a database model. However, most REST resources expose data at several endpoints and may include endpoints for performing actions, such as sending a message, uploading a document, and more. In these cases, the best approach is to create schema files to model the REST endpoints as tables, views, and stored procedures.

#### LACensusData2010.rsd

Below is a schema file based on the REST resource for the 2010 Los Angeles census data, written using RSBScript. A detailed explanation of the schema file follows. For more information on using RSBScript, refer to the RSBScript Reference section in the [online help documentation](https://www.cdata.com/drivers/rest/docs).

```auto
<rsb:script xmlns:rsb="http://www.rssbus.com/ns/rsbscript/2" xmlns:xs="http://www.w3.org/2001/XMLSchema"> <rsb:info title="LACensusData2010" desc="2010 Census data for the city of Los Angeles, grouped by ZIP code." xmlns:other="http://www.rssbus.com/ns/rsbscript/2/other"> <attr name="ID" xs:type="integer" readonly="false" other:xPath="@_id" /> <attr name="AverageHouseholdSize" xs:type="double" readonly="false" other:xPath="average_household_size" /> <attr name="MedianAge" xs:type="double" readonly="false" other:xPath="median_age" /> <attr name="TotalFemales" xs:type="integer" readonly="false" other:xPath="total_females" /> <attr name="TotalHouseholds" xs:type="integer" readonly="false" other:xPath="total_households" /> <attr name="TotalMales" xs:type="integer" readonly="false" other:xPath="total_males" /> <attr name="TotalPopulation" xs:type="integer" readonly="false" other:xPath="total_population" /> <attr name="ZIPCode" xs:type="integer" readonly="false" other:xPath="zip_code" /> </rsb:info> <rsb:set attr="URI" value="https://data.lacity.org/api/views/nxs9-385f/rows.xml" /> <rsb:set attr="RepeatElement" value="/response/row/row" /> <!-- The GET method corresponds to SELECT. --> <rsb:script method="GET"> <rsb:call op="xmlproviderGet"> <rsb:push/> </rsb:call> </rsb:script> <!-- The POST method corresponds to INSERT. --> <rsb:script method="POST"> <rsb:set attr="method" value="POST"/> <rsb:call op="xmlproviderGet"> <rsb:throw code="500" desc="Inserts are not currently supported."/> <rsb:push/> </rsb:call> </rsb:script> <!-- The MERGE method corresponds to UPDATE. --> <rsb:script method="MERGE"> <rsb:set attr="method" value="PUT"/> <rsb:call op="xmlproviderGet"> <rsb:throw code="500" desc="Updates are not currently supported."/> <rsb:push/> </rsb:call> </rsb:script> <!-- The DELETE method corresponds to DELETE. --> <rsb:script method="DELETE"> <rsb:set attr="method" value="DELETE"/> <rsb:call op="xmlproviderGet"> <rsb:throw code="500" desc="Deletes are not currently supported."/> <rsb:push/> </rsb:call> </rsb:script></rsb:script>

```

There are several distinct parts of the schema definition, from the table and column definitions to specific configurations based on the REST resource being exposed to the definitions for handling different CRUD (create, read, update, delete) operations. These parts are described below:

### Table Name & Description

```auto
<rsb:info title="LACensusData2010" desc="2010 Census data for the city of Los Angeles, grouped by ZIP code." xmlns:other="http://www.rssbus.com/ns/rsbscript/2/other">

```

The rsb:info element contains the table definition and has two important attributes: title is the name of the table or view and desc is the description of the table or view. These values determine what information is exposed in common BI, reporting, and ETL tools.

### Column Definitions

The attr child elements of the rsb:info element represent the column definitions for the table or view. Below is a detailed explanation of the column definitions.

```auto
... <attr name="ID" xs:type="integer" readonly="false" other:xPath="@_id" />...

```

Each element has several attributes that provide information about the column:

- name: provides the name of the column, used in the projection of a SQL query and exposed in common BI, reporting, and ETL tools when working with the REST resource
- xs:type: provides the SQL data type for the column
- readonly: indicates whether the column is read-only or not, “false” by default
- other:xPath: configures how to parse the value for the column using XPath notation (details below)

### REST Resource URI

```auto
<rsb:set attr="URI" value="https://data.lacity.org/api/views/nxs9-385f/rows.xml" />

```

The rsb:set keyword of RSBScript sets properties for use in the drivers. To indicate the file or endpoint for the REST resource for the table, set the attr and value XML attributes to “URI” and the URI for the resource, respectively. This value overrides any value set in the connection string, allowing you to configure each schema to point to a different REST resource.

### Repeat Element

```auto
<rsb:set attr="RepeatElement" value="/response/row/row" />

```

To define XPath for this schema, set the attr attribute to “RepeatElement” and set the value attribute to the XML element to be used to split the REST resource into multiple rows. Setting the RepeatElement attribute in the schema definition overrides the value set in the **XPath** connection property.

#### XPath Details

Several properties and attributes used to configure the REST drivers use XPath notation. For the XPath connection property and the RepeatElement attribute of custom schemas, the XPath value defines the XML element on which to split the data into rows. The other:xPath attribute in a column definition defines exactly which XML attribute or child element to expose as the column value and can be absolute or relative (to **XPath** or RepeatElement).

For example, using the XML data exposed above, define the other:xPath attribute for the Id column using the absolute path /response/row/row@\_id or the relative path @\_id. Note that the @ character indicates the value is parsed from an XML attribute, not an XML element.

If you wish to expose the value of an immediate child element of the repeated XML element, omit the other:xPath attribute. By default, the drivers attempt to parse an element with the same name as the name attribute. For example, the column definition for the ZIPCode column could be as simple as the following:

```auto
<attr name="ZIPCode" xs:type="integer" />

```

### Defining CRUD Operations

The schema above defines the behavior for an HTTP GET operation (SELECT), but throws errors for HTTP POST (SQL INSERT), MERGE (SQL UPDATE), and DELETE (SQL DELETE) operations, asserting that those operations are currently unsupported. If you are working with a read-only REST resource, or wish for your integration to be read-only, delete the rsb:script keywords for INSERT, UPDATE, and DELETE. This causes BI, reporting, and ETL tools to treat the exposed resource as a SQL view instead of a table.

Use RSBScript to customize the behavior for each operation, from adding URL parameters (as supported by the REST resource) based on columns and values found in the SQL query (typically used for SELECT and DELETE requests) to building PUT and POST data in order to support INSERT and UPDATE requests.

#### Sample Operation Implementations

The implementations below are examples of how to implement full CRUD support for a REST resource. (Note that the example REST resource for this article does not support write operations.) More details on implementing specific requests are found in the [online Help documentation](https://www.cdata.com/drivers/rest/docs) in the Modeling REST Data section.

- SELECT \* FROM LACensusData2010 WHERE ([ZIPCode] = 90005)

- INSERT INTO LACensusData2010 (MedianAge, TotalFemales, …) VALUES (25.5, 3587, …)

- UPDATE LACensusData2010 SET [MedianAge] = 25.5 WHERE [Id] = 57

- DELETE FROM LACensusData2010 WHERE [Id]=‘57’

## Get Started

* * *

The CData Drivers for REST allow you to easily work with REST resources as if they were a database, trading the complexities of REST requests for the familiarity of SQL in common technologies like JDBC, ODBC, [ADO.NET](http://ADO.NET), and more. Download a [free, 30 day trial](https://www.cdata.com/drivers/rest/download) of any of our drivers and start working with REST resources in BI, reporting, ETL, and custom applications today. If you have any questions, reach out to our world-class [Support Team](https://www.cdata.com/support/submit.aspx).