arrayToTable
You are looking at an older version of the documentation. The latest version is found here.
This procedure converts an array of items into a table and provides an easy way to parse complex input of stored procedures.
Parameters
Parameter | Description |
---|---|
items | Array of items to be converted; mandatory |
Attributes
Attribute | Type | Description |
---|---|---|
| integer | Object sequence number within the array |
| object | Object value |
Examples
1. Simple call
SELECT * FROM (
CALL UTILS.arrayToTable(
items => ('a','b','c')
)
)x;;
This call returns three items with the corresponding indexes:
2. Joining of nested arrays
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;;
This call returns the following result:
3. Generating complex XML structures in the connectors
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;;
This call returns the following result:
<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>