Excel Add-In for Sage 200

Build 24.0.9062

テーブル値関数

テーブル値関数は、テーブル（行セット）を返す関数です。

Note： テーブル値関数のサポートは本製品 の実験的な機能です。この機能は、SQL-92準拠という本製品 のコアスコープを超えています。そのため、これらの関数のパフォーマンスは最適ではない場合があります。

テーブル値関数句

CROSS APPLY

CROSS APPLY 演算子は、直前のテーブル式によって生成されたテーブルまたは結果セットの各行に対してサブクエリを実行するために使用されます。 

<table_expression_1> CROSS APPLY <table_expression_2>

第2のテーブル式は、第1のテーブル式の結果を参照して、テーブル値関数を介して派生カラムまたは変更されたレコードセットを作成することができます。

各結果レコードは、関数によって分割された値を含むカラムを除いて、すべて同じカラム値を持つ、分割されたレコードのインスタンスです。

WITH

WITH 句は、分割される構造内の構成要素（キー、要素名、属性名など）と照合したり、関数から生成されるカラムのメタデータを指定したりするために、特定のテーブル値関数と一緒に使用されます。 
SELECT A.ColumnName, X.DerivedColumnName FROM TableName A CROSS APPLY <table-valued function> WITH (DerivedColumnName varchar(255)) AS X

テーブル値関数

STRING_SPLIT(input_text,delimiter)

直前のテーブル式のレコードセットの各レコードを受け取り、区切り文字を含むカラム（input_text）を区切り文字（delimiter）で区切られた部分文字列に分割し、部分文字列ごとに1レコードを返します。

  • input_text：パースしたい値を持つカラム。
  • delimiter：input_text で指定されたカラムの値を分割するために使用される文字。

次のような"SplitColumn" というカラムがあるとします。 

One-Two-Three
複数のレコードに渡るこの値を分割するには： 
SELECT A.ID, X.Value FROM [TableWithDelimitedStringField] A CROSS APPLY STRING_SPLIT(A.SplitColumn,'-') WITH (Value VARCHAR(255)) AS X

-- Results:
-----------
|ID|Value|
|1|One|
|1|Two|
|1|Three|

JSONTABLE(json_content,[jsonpath])

直前のテーブル式のレコードセットの各レコードについて、WITH 句で指定されたキーと一致するJSON 配列（json_content）のキーのインスタンスごとに、"jsonpath" 入力で指定されたスコープで1レコードを返します。

  • json_content：JSON の「テーブル」（オブジェクト配列）。コンテンツはネストできますが、ルートレベルで、他のJSON の構造体ではなく単一のJSON 配列でなければなりません。
    • WITH 句で指定されたキーの各インスタンスの値は、ルートレベルのJSON 配列の直接の子である部分構造でのみ取得可能です。
  • jsonpath：オプションのJSONPath クエリで、json_content 配列の中からコンテンツを取り出したいスコープを定義します。WITH 句で識別されるJSON キーは、このパラメータで定義されるスコープに存在する必要があります。デフォルトはJSON ルート（$）です。

ID カラムと以下の内容を持つ「JSONColumn」という名前のJSON コンテンツを持つカラムを含む、単一のレコードを持つサンプルテーブルを例にします： 

[
	{
		"name": "Samuel",
		"email": "[email protected]",
		"extrainfo": {
			"city": "Seattle"
		}
	},
	{
		"name": "Katherine",
		"email": "[email protected]",
	},
	{
		"name": "George",
		"email": "[email protected]",
	},
	{
		"name": "Carlos",
		"email": "[email protected]",
	}
]

特定のキーのすべての値を抽出するには、JSONTABLE 関数でスコープを指定し、WITH 句で目的のキーを記述します。 

SELECT A.ID, X.name FROM [TableWithJSONField] A CROSS APPLY JSONTABLE(A.JSONColumn) WITH (name VARCHAR(255)) AS X

-- Results: 
|ID|name|
---------
|1 |Samuel|
|1 |Katherine|
|1 |George|
|1 |Carlos|

XMLTABLE(xml_content,[xpath,child_type])

直前のテーブル式の結果セットの各レコードについて、WITH 句で指定されたタグ名や属性名に一致するXML 構造体（xml_content）の要素や属性ごとに、"xpath" 入力で指定されたスコープで1レコードを返します。

  • xml_content：XML 構造を含むカラム。
  • xpath：本製品 がWITH 句で指定されたタグ / 属性名に一致するコンテンツを抽出するXML 構造内のスコープを指定するオプションのXPath。
    • サブ要素のコンテンツを抽出する場合、本製品 は、ルートレベル（深さ0）、ルートの直属の子（深さ1）、およびルート直属の子の子（深さ2）のタグからすべてのコンテンツを取得できます。
    • 要素の属性のコンテンツを抽出する場合、本製品 は、ルートレベル（深さ0）とルートレベル要素の直属の子（深さ1）の指定された属性を含むタグからすべてのコンテンツを取得できます。
  • child_type：親要素（xpath 入力で指定される）のうち、WITH 句で指定されるカラムがコンテンツを識別するために照合される部分を指定するオプションのパラメータ。
    • 次の値を指定できます。
      • 0： WITH 句のカラムは、親要素の属性名およびサブ要素のタグ名との一致がチェックされます。
      • 1： WITH 句のカラムは、親要素の属性名と一致するかチェックされます。
      • 2： WITH 句のカラムは、親要素のサブ要素のタグ名と一致するかチェックされます。
    • 指定がない場合、デフォルトは0です。

サブ要素の値の抽出

ID カラムと、以下の内容を持つ「XMLContent」という名前のXML コンテンツを持つカラムを含む、単一のレコードを持つサンプルテーブルを例にします： 
<shoppingList>
    <item>
        <name>Apples</name>
        <quantity>3</quantity>
        <unit>Kg</unit>
    </item>
    <item>
        <name>Bread</name>
        <quantity>2</quantity>
        <unit>Loaf</unit>
		<extrainfo>
			<Type>Whole-Grain</Type>
		</extrainfo>
    </item>
    <item>
        <name>Milk</name>
        <quantity>1</quantity>
        <unit>Carton</unit>
    </item>
    <item>
        <name>Eggs</name>
        <quantity>12</quantity>
        <unit></unit>
    </item>
</shoppingList>

サブ要素コンテンツを抽出するには、XMLTABLE 関数でスコープを指定し、WITH 句で必要な要素名を指定します。XMLTABLE 関数のchild_type 入力が2に設定されている場合、これは機能しないことに注意してください。 

SELECT A.ID, X.name FROM [TableWithXMLField] A CROSS APPLY XMLTABLE(A.XMLContent,'//*/item') WITH (name VARCHAR(255)) AS X

-- Results: 
|ID|name|
---------
|1|Apples|
|1|Bread|
|1|Milk|
|1|Eggs|

要素タグ属性を使用した値の抽出

ID カラムと、以下の内容を持つ「XMLContent」という名前のXML コンテンツを持つカラムを含む、単一のレコードを持つサンプルテーブルを例にします： 

<restaurant>
  <dish type="appetizer">
    <name lang="en">Caprese Salad</name>
    <chef>Chef Giovanni</chef>
    <price currency="USD">9.99</price>
  </dish>
  <dish type="main-course">
    <name lang="fr">Boeuf Bourguignon</name>
    <chef>Chef Marie</chef>
    <price currency="EUR">19.99</price>
  </dish>
  <dish type="dessert">
    <name lang="es">Tres Leches Cake</name>
    <chef>Chef Alejandro</chef>
    <price currency="MXN">89.99</price>
  </dish>
</restaurant>

属性コンテンツを抽出するには、XMLTABLE 関数でスコープを指定し、WITH 句で必要な属性名を指定します。XMLTABLE 関数のchild_type 入力が2に設定されている場合、これは機能しないことに注意してください。 

SELECT A.ID, X.type FROM [TableWithXMLField] A CROSS APPLY XMLTABLE(A.XMLContent,'//*/dish') WITH (type VARCHAR(255)) AS X

-- Results: 
|ID|type|
---------
|1|appetizer|
|1|main-course|
|1|dessert|

CSVTABLE(csv_content,[delimiter])

直前のテーブル式の結果セットの各レコードについて、CSV テーブルを含むカラム（csv_content）から読み取り、そのCSV テーブルの各レコードについて、WITH 句で指定されたCSV カラムの値を含むレコードを1つ返します。

  • csv_content：CSV テーブルを含むカラム。
  • delimiter：csv_content 入力に含まれるCSV コンテンツを分割する、オプションのカスタム区切り文字（カンマの代わり）。

ID カラムと、以下のコンテンツを持つ「CSVContent」という名前のCSV テーブルを持つカラムを含む、単一のレコードを持つサンプルテーブルを例にします： 

Name;Category;Price
Apple;Fruit;0.99
Spaghetti;Pasta;5.49
Chicken Breast;Meat;8.99
Broccoli;Vegetable;2.49

"Name" カラムのすべての値を選択し、カスタム区切り文字（;）を考慮するには： 

SELECT A.ID, X.Name FROM [TableWithCSVField] A CROSS APPLY CSVTABLE(A.CSVContent,';') WITH (Name VARCHAR(255)) AS X

-- Results:
|ID|Name|
-----------
|1|Apple|
|1|Spaghetti|
|1|Chicken Breast|
|1|Broccoli|

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