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 位用户发现它有用
        • 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.