Forum Stats

  • 3,839,809 Users
  • 2,262,538 Discussions
  • 7,901,061 Comments

Discussions

SQL on XMLTABLE doesn't returns any output

CarstenDD
CarstenDD Member Posts: 125 Bronze Badge

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="[email protected]" start="2020-12-01T18:22:05.977Z">

 <group name="open sftp://XX99XX1943XXX:***@xxx.yyy.com -hostkey=&quot;zzzzz-sha2-nistp256 256 nQ+4D11vmAxnBrqCuODEQ5fqdPq5NeF2xt9qEJKvxtA=&quot; -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=&quot;binary&quot; -- &quot;/response/out/*.xml&quot; &quot;L:\CW\IMPORT_LISTEN\Test_Sc\&quot;" 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

Tagged:

Best Answers

  • cormaco
    cormaco Member Posts: 1,959 Silver Crown
    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
    
    
    
    
    CarstenDD
  • cormaco
    cormaco Member Posts: 1,959 Silver Crown
    Answer ✓

    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.

    CarstenDD

Answers

  • cormaco
    cormaco Member Posts: 1,959 Silver Crown
    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
    
    
    
    
    CarstenDD
  • CarstenDD
    CarstenDD Member Posts: 125 Bronze Badge

    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
    cormaco Member Posts: 1,959 Silver Crown
    Answer ✓

    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.

    CarstenDD