Hi,
I'm working on Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production.
I have a quite simple table (owxml) with an ID and a XMLTYPE-Column (named Content) and the following XML stored in the table:
<?xml version="1.0" encoding="UTF-8"?>
<session xmlns="http://winscp.net/schema/session/1.0" name="XX99XX1943XXX@xxx.yyy.com" start="2020-12-01T18:22:05.977Z">
<group name="open sftp://XX99XX1943XXX:***@xxx.yyy.com -hostkey="zzzzz-sha2-nistp256 256 nQ+4D11vmAxnBrqCuODEQ5fqdPq5NeF2xt9qEJKvxtA=" -timeout=15" start="2020-12-01T18:22:06.290Z">
</group>
<group name="pwd" start="2020-12-01T18:22:06.383Z">
<cwd>
<cwd value="/" />
<result success="true" />
</cwd>
</group>
<group name="get -nopermissions -preservetime -transfer="binary" -- "/response/out/*.xml" "L:\CW\IMPORT_LISTEN\Test_Sc\"" start="2020-12-01T18:22:06.446Z">
<download>
<filename value="/response/out/Production_OrderResponse_7038387953_20201125_170928839.xml" />
<destination value="\\comsrv03\ol4i\CW\IMPORT_LISTEN\Test_Sc\Production_OrderResponse_7038387953_20201125_170928839.xml" />
<result success="true" />
</download>
<download>
<filename value="/response/out/Production_Invoice_0957669780_20201120_144134433.xml" />
<destination value="\\comsrv03\ol4i\CW\IMPORT_LISTEN\Test_Sc\Production_Invoice_0957669780_20201120_144134433.xml" />
<result success="true" />
</download>
</group>
</session>
I can't even get this simple statement running to deliver a result.
SELECT x.*
FROM owxml t,
XMLTABLE ('
/session
'
PASSING t.content
COLUMNS
starttime VARCHAR2(255) PATH '/group[@name="pwd"]/@start'
) x
WHERE t.sysowxml = 15
I get no error, just one row with an empty "starttime"-column. When I access the table with SQLDetective I get the full XML as shown above.
I'm using this table for several different XMLs stored in the content-column where I have no problems to get the data out of it. I have no clue what leads to this behavior with this XML-structure.
Every suggestion will be appreciated.
Regards Carsten