Top-Level Document Model
Using a top-level document view of the data provides ready access to top-level elements. The add-in returns nested elements in aggregate, as single columns.
One aspect to consider is performance. You forego the time and resources to process and parse nested elements -- the add-in parses the returned data once, using streaming to read the data. Another consideration is your need to access any data stored in nested parent elements, and the ability of your tool or application to process JSON or XML.
Modeling a Top-Level Document View
With DataModel set to "Document" (the default), the add-in scans only a single element, the top-level element by default. The top-level elements are available as columns due to the default object flattening. Nested elements are returned as aggregates.
You can set XPath to specify an element other than the top-level one.
Example
Below is a sample query and the results, based on the sample document in Raw Data. The query results in a single "people" table based on the XPath "/root/people".
Connection String
Set the DataModel connection property to "Document" to perform the following query and see the example result set. The add-in will scan only the XPath value below:
URI=C:\people.txt;Format=XML;DataModel=Document;XPath='/root/people';
Query
The following query pulls the top-level elements and the subelements of the vehicles element into the results.
SELECT
[personal.age] AS age,
[personal.gender] AS gender,
[personal.name.first] AS name_first,
[personal.name.last] AS name_last,
[source],
[vehicles]
FROM
[people]
Results
With a document view of the data, the personal element is flattened into 4 columns and the source and vehicles elements are returned as individual columns, resulting in a table with 6 columns.
age | gender | name_first | name_last | source | vehicles | |
20 | M | John | Doe | internet | <vehicles><type>car</type><model>Honda Civic</model><insurance><company>ABC Insurance</company><policy_num>12345</policy_num></insurance><features>sunroof</features><features>rims</features><maintenance><date>07-17-2017</date><desc>oil change</desc></maintenance><maintenance><date>01-03-2018</date><desc>new tires</desc></maintenance></vehicles><vehicles><type>truck</type><model>Dodge Ram</model><insurance><company>ABC Insurance</company><policy_num>12345</policy_num></insurance><features>lift kit</features><features>tow package</features><maintenance><date>08-27-2017</date><desc>new tires</desc></maintenance><maintenance><date>01-08-2018</date><desc>oil change</desc></maintenance></vehicles> | |
24 | F | Jane | Roberts | phone | <vehicles><type>car</type><model>Toyota Camry</model><insurance><company>Car Insurance</company><policy_num>98765</policy_num></insurance><features>upgraded stereo</features><maintenance><date>05-11-2017</date><desc>tires rotated</desc></maintenance><maintenance><date>11-03-2017</date><desc>oil change</desc></maintenance></vehicles><vehicles><type>car</type><model>Honda Accord</model><insurance><company>Car Insurance</company><policy_num>98765</policy_num></insurance><features>custom paint</features><features>custom wheels</features><maintenance><date>10-07-2017</date><desc>new air filter</desc></maintenance><maintenance><date>01-13-2018</date><desc>new brakes</desc></maintenance></vehicles> |
See Also
- Automatic Schema Discovery: Configure column discovery with horizontal flattening.
- Free-Form Queries: Use dot notation to select nested data.
- Vertical Flattening: Query nested data as separate tables.
- XML Functions and JSON Functions: Manipulate the data returned to perform client-side aggregation and transformations.