リレーショナルモデル
The CData ODBC Driver for XML can be configured to create a relational model of the data in the XML file or source, treating each XPath as an individual table containing a primary key and a foreign key that links to the parent document.This is particularly useful if you need to work with your XML data in existing BI, reporting, and ETL tools that expect a relational data model.
Joining Nested Arrays as Tables
With DataModel set to "Relational", any JOINs are controlled by the query.Any time you perform a JOIN query, the XML file or source will be queried once for each table included in the query.
例
Below is a sample query against the sample document in Raw データ, using a relational model based on the XML paths "/root/people", "/root/people/vehicles", and "/root/people/vehicles/maintenance".
接続文字列
DataModel 接続プロパティを"Relational" に設定しXPath 接続プロパティを"/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;" に設定して、次のクエリを実行してサンプル結果セットを表示します。
URI=C:\people.txt;DataModel=Relational;XPath='/root/people;/root/people/vehicles;/root/people/vehicles/maintenance;'
クエリ
The following query explicitly JOINs the people, vehicles, and maintenance tables.
SELECT
[people].[personal.age] AS age,
[people].[personal.gender] AS gender,
[people].[personal.name.first] AS first_name,
[people].[personal.name.last] AS last_name,
[people].[source],
[vehicles].[type],
[vehicles].[model],
[vehicles].[insurance.company] AS ins_company,
[vehicles].[insurance.policy_num] AS ins_policy_num,
[maintenance].[date] AS maint_date,
[maintenance].[desc] AS maint_desc
FROM
[people]
JOIN
[vehicles]
ON
[people].[_id] = [vehicles].[people_id]
JOIN
[maintenance]
ON
[vehicles].[_id] = [maintenance].[vehicles_id]
結果
In the example query, each maintenance element is JOINed to its parent vehicle element, which is JOINed to its parent people element to produce a table with 8 rows (2 maintenance entries for each of 2 vehicles each for 2 people).
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 |
関連項目
- 自動スキーマ検出:テーブルスキーマに報告されたカラムを設定します。
- フリーフォームクエリ:ドット表記を使用して、ネストされたデータを選択します。
- 垂直フラット化:ネストされたデータを別々のテーブルとしてクエリします。
- XML 関数:クライアント側の集計と変換を実行するために返されたデータを操作します。