The CData SSIS Components 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).

関連項目

• 自動スキーマ検出：テーブルスキーマに報告されたカラムを設定します。
• フリーフォームクエリ：ドット表記を使用して、ネストされたデータを選択します。
• 垂直フラット化：ネストされたデータを別々のテーブルとしてクエリします。
• XML 関数：クライアント側の集計と変換を実行するために返されたデータを操作します。