Returning multiple records from XML document

[virtualdatabase]

Using the VDB.xml file I want to return various items.

I can return items at the vdb level fine:

select f.file ,a.*

from (select TO_CHARS(contents, 'US-ASCII') file

from sysadmin.VDBResources

WHERE resourcePath='/META-INF/vdb.xml') as f,

XMLTABLE('/vdb' PASSING XMLPARSE(DOCUMENT f.file) COLUMNS VDBname string PATH '@name', version string PATH '@version', description string PATH 'description/text()') as a

but when I want the models within it I get an error:

select f.file ,a.*

from (select TO_CHARS(contents, 'US-ASCII') file

from sysadmin.VDBResources

WHERE resourcePath='/META-INF/vdb.xml') as f,

XMLTABLE('/vdb' PASSING XMLPARSE(DOCUMENT f.file) COLUMNS ModelName string PATH 'model/@name') a

This erros with: 

Error: TEIID30171 Unexpected multi-valued result was returned for XMLTable column "ModelName".  Path expressions for non-XML type columns should return at most a single result.

SQLState:  50000

ErrorCode: 30171

Note that the PATH I used above I got while using the Designer Import XML... I just changed it from using getTextFiles to the VDBResources query.

I know this is more of an XPATH issue but please...What am I doing wrong?

[shawkins]

> I know this is more of an XPATH issue but please...What am I doing wrong?

You can either return an array of model names, or change your context item to be the model:

XMLTABLE('/vdb/model' PASSING XMLPARSE(DOCUMENT f.file) COLUMNS ModelName string PATH '@name') a

[virtualdatabase]

Thanks... that did it... I am was able to finish that task and get other properties as well!

[virtualdatabase]

Getting past that... now I'm trying to get the data-role info including permission, associated resource-name (model) and the various properties (allow-create, allow-read, etc)

Since permission is nested under data-role, how can that be done?

[shawkins]

It all just depends on what you want to iterate over.  If you want to iterate on permissions, then use that as your context item.  Then if you need that correlated to role information you can have a column that references parent information RoleName string PATH '../@Name'