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 connector 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]

<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>

<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>

<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>

<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>