このプロシージャは、項目の配列をテーブルに変換し、ストアドプロシージャの複雑な入力を簡単に解析する方法を提供します。
Parameters
Parameter | Description |
---|---|
| Array of items to be converted; mandatory |
Attributes
Attribute | Type | Description |
---|---|---|
| integer | Object sequence number within the array |
| object | Object value |
Examples
1. シンプルコール
SELECT
*
FROM
(
CALL UTILS.arrayToTable(
items => (
'a'
,
'b'
,
'c'
)
)
)x;;
この呼び出しは3つのアイテムと対応するインデックスを返します:
2. 入れ子配列のJOIN
SELECT
x.id
AS
xid, y.id
AS
yid, y.item
FROM
(
CALL UTILS.arrayToTable(
items => ((
'a'
,
'aaa'
,
'aaaaa'
),(
'bb'
,
'bbb'
))
)
)x
CROSS
JOIN
TABLE
(
CALL UTILS.arrayToTable(
items => x.item
)
)y;;
この呼び出しは以下の結果を返します:
3. コネクタで複雑なXML構造を生成
BEGIN
DECLARE
string[] fieldNames = (
'Id'
,
'Value'
,
'ExtraProperty'
);
DECLARE
string[][] objects = ((
'1'
,
'One'
),(
'2'
,
'Two'
,
'ThisHasExtraProperty'
),(
'3'
,
'Three'
));
SELECT
XmlElement(
xObjects,
XmlAgg(xObject)
)
FROM
(
SELECT
XmlElement(
XObject,
XmlAgg(
XmlElement(
EvalName(
Cast
(properties.item
as
string)),
XmlParse(Content
Cast
(items.item
as
string))
)
ORDER
BY
items.id
)
)
AS
xObject
FROM
TABLE
(CALL UTILS.arrayToTable(items => objects)) records
CROSS
JOIN
TABLE
(CALL UTILS.arrayToTable(items => records.item)) items
JOIN
TABLE
(CALL UTILS.arrayToTable(items => fieldNames)) properties
ON
properties.id = items.id
GROUP
BY
records.id
) x;
END
;;
この呼び出しは以下の結果を返します:
<
xObjects
>
<
XObject
>
<
Id
>1</
Id
>
<
Value
>One</
Value
>
</
XObject
>
<
XObject
>
<
Id
>2</
Id
>
<
Value
>Two</
Value
>
<
ExtraProperty
>ThisHasExtraProperty</
ExtraProperty
>
</
XObject
>
<
XObject
>
<
Id
>3</
Id
>
<
Value
>Three</
Value
>
</
XObject
>
</
xObjects
>