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

    Unable to fetch data from nested null namespace using XMLTABLE

    SonalP

      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>

              <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"

            , '' 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

          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

            have changed it upto small enxtent:

             

            , '/tns1:DocumentList/documents'  -->  , '/tns1:DocumentList/*:documents'

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

              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 ?

              • 5. Re: Unable to fetch data from nested null namespace using XMLTABLE
                Jason_(A_Non)

                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

                  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.