6 Replies Latest reply on Mar 7, 2019 8:21 PM by SonalP

Unable to fetch data from nested null namespace using XMLTABLE

SonalP Mar 6, 2019 4:17 PM

Hi Guys, Hope you all are doing good.

I'm facing issue while fetching the data from nested nullable namespace using XMLTABLE.

From the following XML string, the moment I remove ' xmlns="" ' from 'documents' tags, i am start getting the data.

Could you please help/guide me to resolve this issue?

select xt.*

from

(

select '<?xml version=''1.0'' encoding=''UTF-8''?>

<DocumentList

xmlns="http://www.xyz.com/testworkflowmodel/8.1/dms">

<documents

xmlns="">

<id>{urn:repoId:System}{uid}1e7b6e94-49c8-957128311b42</id>

<path>/temp/procinstances/2018/01/01/01/pi-7418529/example2.TIFF</path>

<name>example2.TIFF</name>

<DocumentTypes>Email</DocumentTypes>

</properties>

<contentType>image/tiff</contentType>

</documents>

<documents

xmlns="">

<id>{urn:repoId:System}{uid}1e7b6e94-49c8-957128311b42</id>

<path>/procinstances/2018/02/20/13/pi-7418529/procattachs/sample.tif</path>

<name>sample.tif</name>

<DocumentTypes>Previous Rules</DocumentTypes>

</properties>

<contentType>image/tiff</contentType>

</documents>

</DocumentList>'

as STRINGVALUE

from dual

) cd

, XMLTABLE(

xmlnamespaces(

'http://www.xyz.com/testworkflowmodel/8.1/dms' as "tns1"

, '' as "tns2"

, default 'http://www.xyz.com/testworkflowmodel/8.1/dms'

)

, '/DocumentList/documents'

PASSING XMLTYPE.createXML(cd.STRINGVALUE)

COLUMNS

id VARCHAR2(128 CHAR) path 'id',

path VARCHAR2(128 CHAR) path 'path',

name VARCHAR2(128 CHAR) path 'name',

contenttype VARCHAR2(128 CHAR) path 'contentType',

size1 NUMBER path 'size',

documenttype VARCHAR2(128 CHAR) path 'properties/DocumentType'

) xt

;

1. Re: Unable to fetch data from nested null namespace using XMLTABLE

cormaco Mar 6, 2019 5:01 PM (in response to SonalP)

Please change your username to something readable:

How can I change my Display Name?

Here is a working solution.

I changed the default namespace declaration to "" added tns1: to DocumentList and removed the tns2 declaration and corrected the typo in path 'properties/DocumentType'

select xt.*

from

  (

    select '<?xml version=''1.0'' encoding=''UTF-8''?>

<DocumentList

    xmlns="http://www.xyz.com/testworkflowmodel/8.1/dms">

    <documents

        xmlns="">

        <id>{urn:repoId:System}{uid}1e7b6e94-49c8-957128311b42</id>

        <path>/temp/procinstances/2018/01/01/01/pi-7418529/example2.TIFF</path>

        <name>example2.TIFF</name>

        <properties>

            <Member>A123456B</Member>

            <Scheme>1234567890</Scheme>

            <DocumentTypes>Email</DocumentTypes>

        </properties>

        <description></description>

        <size>33127</size>

        <contentType>image/tiff</contentType>

        <dateCreated>2018-01-01T01:41:57</dateCreated>

        <dateLastModified>2018-01-01T01:41:57</dateLastModified>

    </documents>

    <documents

        xmlns="">

        <id>{urn:repoId:System}{uid}1e7b6e94-49c8-957128311b42</id>

        <path>/procinstances/2018/02/20/13/pi-7418529/procattachs/sample.tif</path>

        <name>sample.tif</name>

        <properties>

            <Member>A123456B</Member>

            <Scheme>1234567890</Scheme>

            <DocumentTypes>Previous Rules</DocumentTypes>

        </properties>

        <description></description>

        <size>10409</size>

        <contentType>image/tiff</contentType>

        <dateCreated>2018-02-20T13:41:57</dateCreated>

        <dateLastModified>2018-02-20T13:41:57</dateLastModified>

    </documents>

</DocumentList>'

    as STRINGVALUE

    from dual

  ) cd

  , XMLTABLE(

    xmlnamespaces(

      'http://www.xyz.com/testworkflowmodel/8.1/dms' as "tns1"

      , default ''

    )

    , '/tns1:DocumentList/documents'

      PASSING XMLTYPE.createXML(cd.STRINGVALUE)

      COLUMNS

        id            VARCHAR2(128 CHAR) path 'id',

        path          VARCHAR2(128 CHAR) path 'path',

        name          VARCHAR2(128 CHAR) path 'name',

        contenttype   VARCHAR2(128 CHAR) path 'contentType',

        size1         NUMBER             path 'size',

        documenttype  VARCHAR2(128 CHAR) path 'properties/DocumentTypes'

  ) xt

;

1 person found this helpful

2. Re: Unable to fetch data from nested null namespace using XMLTABLE

SonalP Mar 7, 2019 10:02 AM (in response to cormaco)

have changed it upto small enxtent:

, '/tns1:DocumentList/documents' --> , '/tns1:DocumentList/*:documents'
Like Show 0 Likes(0)

Actions
3. Re: Unable to fetch data from nested null namespace using XMLTABLE

SonalP Mar 7, 2019 10:03 AM (in response to cormaco)

Thank you
Like Show 0 Likes(0)

Actions
4. Re: Unable to fetch data from nested null namespace using XMLTABLE

SonalP Mar 7, 2019 10:24 AM (in response to cormaco)

also, could you please help me like, i want to fetch data between XML tag the multiple row mapped to another table like as mentioned below:
-------------------------Table definition-------------------------------

     clob_table(pi number, clob_data clob);
------------------------------SQL----------------------------
select cd.pi, xt.*
from (
     select pi, clob_data as STRINGVALUE
     from clob_table
) cd
, XMLTABLE(
    xmlnamespaces(
      'http://www.carnot.ag/workflowmodel/3.1/dms' as "tns1"
      , default ''
    )
    , '/tns1:DocumentList/*:documents'
      PASSING XMLTYPE.createXML(t.STRINGVALUE)
      COLUMNS
        id            VARCHAR2(128 CHAR) path 'id',
        path          VARCHAR2(128 CHAR) path 'path',
        name          VARCHAR2(128 CHAR) path 'name',
        contenttype   VARCHAR2(128 CHAR) path 'contentType',
        size1         NUMBER             path 'size',
        documenttype VARCHAR2(128 CHAR) path 'properties/DocumentType'
) xt
;
------------------------------ERROR----------------------------
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
19279. 00000 - "XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence"
*Cause:    The XQuery sequence passed in had more than one item.
*Action:   Correct the XQuery expression to return a single item sequence.
---------------------------------------------------------------------------------------

Can we achieve it w/o using cursor / temp_table ?
Like Show 0 Likes(0)

Actions

5. Re: Unable to fetch data from nested null namespace using XMLTABLE

Jason_(A_Non) Mar 7, 2019 2:57 PM (in response to SonalP)

Well to basically repeat the answer that @cormaco gave you and to provide a framework for you to test with, this work with the original sample you provided.

WITH clob_table AS
(SELECT 3.14 AS pi,
        q'[<?xml version="1.0" encoding="UTF-8"?>
<DocumentList xmlns="http://www.xyz.com/testworkflowmodel/8.1/dms">
   <documents xmlns="">
      <id>{urn:repoId:System}{uid}1e7b6e94-49c8-957128311b42</id>
      <path>/temp/procinstances/2018/01/01/01/pi-7418529/example2.TIFF</path>
      <name>example2.TIFF</name>
      <properties>
         <Member>A123456B</Member>
         <Scheme>1234567890</Scheme>
         <DocumentTypes>Email</DocumentTypes>
      </properties>
      <description/>
      <size>33127</size>
      <contentType>image/tiff</contentType>
      <dateCreated>2018-01-01T01:41:57</dateCreated>
      <dateLastModified>2018-01-01T01:41:57</dateLastModified>
   </documents>
   <documents xmlns="">
      <id>{urn:repoId:System}{uid}1e7b6e94-49c8-957128311b42</id>
      <path>/procinstances/2018/02/20/13/pi-7418529/procattachs/sample.tif</path>
      <name>sample.tif</name>
      <properties>
         <Member>A123456B</Member>
         <Scheme>1234567890</Scheme>
         <DocumentTypes>Previous Rules</DocumentTypes>
      </properties>
      <description/>
      <size>10409</size>
      <contentType>image/tiff</contentType>
      <dateCreated>2018-02-20T13:41:57</dateCreated>
      <dateLastModified>2018-02-20T13:41:57</dateLastModified>
   </documents>
</DocumentList>]' clob_data
   FROM dual)
-- Above simulates your table
SELECT cd.pi, xt.*
  FROM clob_table cd
, XMLTABLE(
    xmlnamespaces(
      'http://www.xyz.com/testworkflowmodel/8.1/dms' as "tns1"
      , default ''
    )
    , '/tns1:DocumentList/documents'
      PASSING XMLTYPE(cd.clob_data)
      COLUMNS
        id            VARCHAR2(128 CHAR) path 'id',
        path          VARCHAR2(128 CHAR) path 'path',
        name          VARCHAR2(128 CHAR) path 'name',
        contenttype   VARCHAR2(128 CHAR) path 'contentType',
        size1         NUMBER             path 'size',
        documenttype  VARCHAR2(128 CHAR) path 'properties/DocumentTypes'
  ) xt
;

In your prior comment, you don't show us the XML being referenced, you modified the Xpath, and you changed the tns1 namespace so how are we supposed to know what the cause of your error really is without seeing what your input XML is? Plug your XML into this testing SQL structure I provided and show us the error.

6. Re: Unable to fetch data from nested null namespace using XMLTABLE

SonalP Mar 7, 2019 8:21 PM (in response to Jason_(A_Non))

Hi Jason, hope you're doing good.

Thanks for the response and the time you've invested. I got the resolution on this. Now, i'm achieving the it by looping over the cursors.

However, i've stared receiving a new error, related to the size of the column we used within xmltype.

I'm not able to get the way to handle this scenario especially for the long (70,000+) char string.

Request you to please my following discussion thread as well

Unable to fetch data from nested null namespace using XMLTABLE

Also, i apologize for the mistakes in mock data.
i'm really very sorry about my in-eligibility in giving you the real data real data, since i'm bound with some policies.
Like Show 0 Likes(0)

Actions

Go to original post