xmltable/xmlparse of standalone.xml

[virtualdatabase]

Everyone.. I'm having difficulty parsing the standalone.xml file

I need to pull portions of info from the standalone.xml file.  For now, I'm simply wanting to pull the information from the datasources section.

Any input would be most appreciated.

select x.* from (SELECT to_chars(a.file, 'UTF-8') AS File

FROM (EXEC SystemFileReader.getFiles('./standalone/configuration/standalone.xml')) AS a,

XMLTABLE('/datasources' PASSING XMLPARSE(DOCUMENT a.File) columns JNDI_Name string PATH '@name')) as x

[shawkins]

> I'm having difficulty parsing the standalone.xml file

What difficulty are you having?

From what you have above, you'll at least need a full path to the datasource elements ('/server/profile/subsystem/datasources/datasource'), or you can just use '//datasource' and use the PATH 'jndi-name'

[virtualdatabase]

select x.* from (SELECT to_chars(a.file, 'UTF-8') AS File

FROM (EXEC SystemFileReader.getFiles('./standalone/configuration/standalone.xml')) AS a,

XMLTABLE('//datasource' PASSING XMLPARSE(DOCUMENT a.File) columns JNDI_Name string PATH 'jndi-name')) as x

Results in:

Error: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "PATH 'jndi-name')[*])[*] as x" at line 3, column 101.

Was expecting: "as" | id

SQLState:  50000

ErrorCode: 0

Which is the same error as before notwithstanding the change to PATH etc.

[shawkins]

That is saying there is no alias on the XMLTABLE table function.  You probably want something that looks like:

select x.*, to_chars(a.file, 'UTF-8') AS File

FROM (EXEC SystemFileReader.getFiles('./standalone/configuration/standalone.xml')) AS a,

XMLTABLE('//datasource' PASSING XMLPARSE(DOCUMENT a.File) columns JNDI_Name string PATH 'jndi-name') as x

[virtualdatabase]

Well, that did take care of the error, however, no data is returned..

[shawkins]

Make sure you are actually getting the standalone.xml, and change the PATH to '@jndi-name' as it's an attribute.

[virtualdatabase]

I've done both...

For example:

SELECT

to_chars(a.file, 'UTF-8') AS File

FROM

(EXEC SystemFileReader.getFiles('./standalone/configuration/standalone.xml')) AS a

Does return the contents of the file.

However, still no data returns with:

select x.*, to_chars(a.file, 'UTF-8') AS File

FROM (EXEC SystemFileReader.getFiles('./standalone/configuration/standalone.xml')) AS a,

XMLTABLE('/server/profile/subsystem/datasources/datasource' PASSING XMLPARSE(DOCUMENT a.File) columns JNDI_Name string PATH '@jndi-name') as x

[shawkins]

One more nuance.  The xmlns attribute is causing the names to be namespaced.  The simplest thing to do is use a wildcard:

select x.*, to_chars(a.file, 'UTF-8') AS File

FROM (EXEC SystemFileReader.getFiles('./standalone/configuration/standalone.xml')) AS a,

XMLTABLE('/*:server/*:profile/*:subsystem/*:datasources/*:datasource' PASSING XMLPARSE(DOCUMENT a.File) columns JNDI_Name string PATH '@jndi-name') as x