arrayToTable

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
`id`	integer	Object sequence number within the array
`item`	object	Object value

Examples

1. Simple call

SQL

SELECT * FROM (
    CALL UTILS.arrayToTable(
        items => ('a','b','c')
    )
)x;;

This call returns three items with the corresponding indexes:

2. Joining of nested arrays

SQL

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

SQL

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:

XML

<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>