Mapping XML responses

For example, the following XML document contains nested elements in the address element and repeating nested elements in the pets and vehicles elements.

<residents>
    <resident>
        <resident_id>ajx363</resident_id>
        <name>Sydney Smith</name>
        <address>
            <street>101 Main Street</street>
            <city>Raleigh</city>
            <state>NC</state>
        </address>
        <county>Wake</county>
        <pets>
            <pet>
                <species>dog</species>
                <breed>beagle</breed>
                <weight unit="lbs">35</weight>
            </pet>
        </pets>
        <vehicles>
            <vehicle>car</vehicle>
            <vehicle>boat</vehicle>
            <vehicle>bicycle</vehicle>
        </vehicles>
    </resident>
    <resident>
        <resident_id>tzn525</resident_id>
        <name>Cora Welch</name>
        <address>
            <street>191 First Street</street>
            <city>Chapel Hill</city>
            <state>NC</state>
        </address>
        <county>Orange</county>
        <pets>
            <pet>
                <species>pig</species>
                <breed>yorkshire</breed>
                <weight unit="lbs">55</weight>
            </pet>
        </pets>
        <vehicles>
            <vehicle>scooter</vehicle>
            <vehicle>truck</vehicle>
            <vehicle>bicycle</vehicle>
        </vehicles>
    </resident>
</residents>

When generating the relational view, the driver decompounds native elements into separate, but related tables. The mapping of the sample XML document produced one parent table and two child tables. In the parent table, simple elements, such as name and county, are flattened into corresponding relational columns. Nested elements are also flattened into relational columns; however, column names are formed by concatenating the name of the parent and nested elements, which are joined by an underscore character. For example, the ADDRESS_STREET column contains the values of the street element that is nested in the address element.

Primary keys for parent tables are determined heuristically from the top-level fields in the document. For example, resident_id. However, if none of the fields are determined to be viable candidates, the driver generates a primary key column, ROWID. Be aware that, when a ROWID column is generated, the driver also flattens and maps objects into a single table. See "Determining the primary key" for more information. Note that you can designate a new primary key in a parent table using the Configuration Manager. For details, see "Customizing your Schema."

The parent table for our example is named RESIDENTS and takes the following form:

The data for the repeating elements nested in the pets element normalizes to PETS child tables. When discovered, the repeating elements within a common parent element are mapped to corresponding relational columns. For example, the species and breed element values from the pets element in the XML sample, are mapped as columns to the following PETS table. A foreign key relationship to the parent table is provided by including the primary key of the parent in the child, in this case, RESIDENT_ID. The primary key of the child table is a composite key formed by the primary key of the parent table combined with the positional information contained in the POSITION column. If the array is nested multiple layers deep, additional positional columns for parent elements are mapped to insure that a unique key is used.

In addition, attributes contained in elements are mapped to the table as a discrete columns with the attribute value being mapped as the column value. For example, the weight unit maps to WEIGHT_UNIT with a value of lbs.

The child table for the pets array would take the following form:

The repeating elements nested in the vehicles element normalize to the VEHICLES child table. The values of the nested elements are mapped into a single relational column that corresponds to the name of the array. For example, the values for the elements nested in the vehicles element in the XML sample, such as car and boat, map to the VEHICLES column in the VEHICLES table. To maintain a unique foreign key, the driver generates a POSITION common to differentiate from the duplicate primary keys derived from the parent table.