Vertical Flattening
Vertical flattening queries enable you to retrieve a nested element as if it were a separate table.
Vertical Flattening Query Syntax
In the FROM clause, you can use dot notation to drill down to a nested element.
SELECT * FROM [people.vehicles]
Example
Consider a single array element from the Raw Data document -- a person object from an array of people:
<?xml version="1.0" encoding="UTF-8" ?>
<root>
<people>
<personal>
<age>20</age>
<gender>M</gender>
<name>
<first>John</first>
<last>Doe</last>
</name>
</personal>
<vehicles>
<type>car</type>
<model>Honda Civic</model>
<insurance>
<company>ABC Insurance</company>
<policy_num>12345</policy_num>
</insurance>
<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>
<maintenance>
<date>08-27-2017</date>
<desc>new tires</desc>
</maintenance>
<maintenance>
<date>01-08-2018</date>
<desc>oil change</desc>
</maintenance>
</vehicles>
<source>internet</source>
</people>
</root>
Connection String
With the following connection string, the add-in will not parse nested data -- the data is processed when you execute the query. Due to the default FlattenObjects functionality, the properties of the top-level element are flattened. Nested data is returned as an XML aggregate.
URI=C:\people.txt;DataModel=Document;XPath='/root/people;'
Query
Vertical flattening will allow you to retrieve the vehicles element as a separate table:
SELECT * FROM [people.vehicles]This query returns the following data set:
features | insurance.company | insurance.policy_num | maintenance | model | type |
<features>sunroof</features><features>rims</features> | ABC Insurance | 12345 | <maintenance><date>07-17-2017</date><desc>oil change</desc></maintenance><maintenance><date>01-03-2018</date><desc>new tires</desc></maintenance> | Honda Civic | car |
<features>lift kit</features><features>tow package</features> | ABC Insurance | 12345 | <maintenance><date>08-27-2017</date><desc>new tires</desc></maintenance><maintenance><date>01-08-2018</date><desc>oil change</desc></maintenance> | Dodge Ram | truck |
<features>upgraded stereo</features> | Car Insurance | 98765 | <maintenance><date>05-11-2017</date><desc>tires rotated</desc></maintenance><maintenance><date>11-03-2017</date><desc>oil change</desc></maintenance> | Toyota Camry | car |
<features>custom paint</features><features>custom wheels</features> | Car Insurance | 98765 | <maintenance><date>10-07-2017</date><desc>new air filter</desc></maintenance><maintenance><date>01-13-2018</date><desc>new brakes</desc></maintenance> | Honda Accord | car |