Excel Add-In for XML

Build 23.0.8839

DataModel

Specifies the data model to use when parsing XML documents and generating the database metadata.

Possible Values

Document, FlattenedDocuments, Relational

Data Type

string

Default Value

"Document"

Remarks

The add-in splits XML documents into rows based on elements that repeat at the same level.

Flattening Nested XML

By default, the add-in projects columns over the properties of objects and returns arrays as XML aggregates.

  • Object: Any parent element that does not repeat at the same height.
  • Array: Any element that repeats at the same height.

In the following example, jobs is a primitive array:

<jobs>sales</jobs>
<jobs>marketing</jobs>

In the following example, maintenance is an object array, since each maintenance node has child elements.

<maintenance>
  <date>07-17-2017</date>
  <desc>oil change</desc>
</maintenance>
<maintenance>
  <date>01-03-2018</date>
  <desc>new tires</desc>
</maintenance> 

Selecting a Data Modeling Strategy

The following DataModel configurations are available. See Parsing Hierarchical Data for examples of querying the data in the different configurations.

  • Document

    Returns a single table representing a row for each top-level object. In this data model, any nested object arrays will not be flattened and will be returned as aggregates. Unless an XPath value is explicitly specified, the add-in will identify and use the top-most object array found as the XPath.

  • FlattenedDocuments

    Returns a single table representing a JOIN of the available documents in the file. In this data model, nested XPath values will act in the same manner as a SQL JOIN. Additionally, nested sibling XPath values (child paths at the same height), will be treated as a SQL CROSS JOIN. Unless explicitly specified, the add-in will identify the XPath values available by parsing the file and identifying the available documents, including nested documents.

  • Relational

    Returns multiple tables, one for each XPath value specified. In this data model, any nested documents (object arrays) will be returned as relational tables that contain a primary key and a foreign key that links to the parent table. Unless explicitly specified, the add-in will identify the XPath values available by parsing the file and identifying the available documents (including nested documents).

See Also

  • XPath: Explicitly set the paths to the documents you want to include.
  • FlattenArrays and FlattenObjects: Use these properties to horizontally flatten the data. This enables customization of the columns that will be identified for each of these data models.
  • Parsing Hierarchical Data: Shows how to query data using each of the DataModel configurations.
  • Modeling XML Data: Provides a map to the different data modeling strategies.
  • Connecting to XML Data Sources: Follow this configuration guide for an overview of the properties you need to connect.

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 23.0.8839