Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL on XMLTABLE doesn't returns any output

CarstenDDDec 3 2020

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
This post has been answered by cormaco on Dec 3 2020
Jump to Answer

Comments

cormaco
Answer

Here is a corrected version of your query,
I removed the leading slash in the starttime path and added the namespace declaration:

SELECT x.*
FROM owxml t,
   XMLTABLE (
        xmlnamespaces(default 'http://winscp.net/schema/session/1.0'),
        '/session'
        PASSING t.content
        COLUMNS
            starttime VARCHAR2(255) PATH 'group[@name="pwd"]/@start'
        ) x

STARTTIME                     
------------------------------
2020-12-01T18:22:06.383Z
Marked as Answer by CarstenDD · Dec 3 2020
1 person found this helpful
CarstenDD

Hello cormaco,
thanks a lot for the quick response and the solution. Can you tell me, why the xmlnamespaces() is solving the problem?
Regards
Carsten

cormaco

You always have to declare all the namespaces that are referenced in the path expressions in your xmltable statement.
Xquery only matches tags if the name and the namespace are the same.
In you code there was no namespace declaration, so starttime had the empty default namespace xmlns="" while in your xml it was xmlns="http://winscp.net/schema/session/1.0" so the tags did not match.

1 person found this helpful
1 - 3

Post Details

Added on Dec 3 2020
#xml
3 comments
930 views