Flattened Documents Model

For users who simply need access to the entirety of their data, flattening the data into a single table is the best option. The add-in will use streaming and only parses the data once per query in this mode.

Joining Object Arrays into a Single Table

With DataModel set to "FlattenedDocuments", the add-in returns a separate table for each object array, but implicitly JOINed to the parent table. Any nested sibling XPath values (child paths at the same height) will be treated as a SQL CROSS JOIN.

Example

Below is a sample query and the results, based on the sample document in Raw Data and parsing based on the XPaths /root/people, /root/people/vehicles, and /root/people/vehicles/maintenance. This implicitly JOINs the people element with the vehicles element and implicitly JOINs the vehicles element with the maintenance element.

Connection String

Use the following connection string to query the Raw Data in this example.

URI=C:\people.txt;Format=XML;DataModel=FlattenedDocuments;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;'

Query

The following query drills into the nested elements in each people element. Since the XPath property included the vehicles node, you can query an element of a vehicle explicitly.

SELECT
  [personal.age] AS age,
  [personal.gender] AS gender,
  [personal.name.first] AS name_first,
  [personal.name.last] AS name_last,
  [source],
  [type],
  [model],
  [insurance.company] AS ins_company,
  [insurance.policy_num] AS ins_policy_num,
  [date] AS maint_date,
  [desc] AS maint_desc
FROM
  [people]

Results

With horizontal and vertical flattening based on the described paths, each vehicle element is implicitly JOINed to its parent people element and each maintenance element is implicitly JOINed to its parent vehicle element.


age	gender	first_name	last_name	source	type	model	ins_company	ins_policy_num	maint_date	maint_desc
20	M	John	Doe	internet	car	Honda Civic	ABC Insurance	12345	2017-07-17	oil change
20	M	John	Doe	internet	car	Honda Civic	ABC Insurance	12345	2018-01-03	new tires
20	M	John	Doe	internet	truck	Dodge Ram	ABC Insurance	12345	2017-08-27	new tires
20	M	John	Doe	internet	truck	Dodge Ram	ABC Insurance	12345	2018-01-08	oil change
24	F	Jane	Roberts	phone	car	Toyota Camry	Car Insurance	98765	2017-05-11	tires rotated
24	F	Jane	Roberts	phone	car	Toyota Camry	Car Insurance	98765	2017-11-03	oil change
24	F	Jane	Roberts	phone	car	Honda Accord	Car Insurance	98765	2017-10-07	new air filter
24	F	Jane	Roberts	phone	car	Honda Accord	Car Insurance	98765	2018-01-13	new brakes