4 Replies Latest reply on Sep 21, 2017 8:47 AM by Steven Hawkins

    Returning multiple records from XML document

    virtualdatabase Apprentice

      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?