6 Replies Latest reply: Feb 20, 2013 8:22 AM by BluShadow RSS

    HELP TO RETERIVE DATA FROM XML

    675884
      Hi All,
      below is the XML string that is present in my column, I want to retrieve the value of "documentId" field from that. I'm easily able to identify the timestamp value from the tag "PublishedTimestamp" but unable to get to the tag "documentId" to retrieve its value.

      Your help is highly appreciable.


      <ABCNotificationEnvelope xsi:noNamespaceSchemaLocation="ABCNotification.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="urn://wsc.ABC.com/dm/2010/02/02" xmlns:ns6="http://docs.oasis-open.org/wsn/t-1" xmlns:ns5="http://docs.oasis-open.org/wsrf/r-2" xmlns:ns4="http://docs.oasis-open.org/wsrf/bf-2" xmlns:ns3="http://docs.oasis-open.org/wsn/b-2" xmlns:ns2="http://www.w3.org/2005/08/addressing" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
      <Header>
      <Version>V1_2010_02_16</Version>
      <Source>AAA</Source>
      <CreatedByUser>AAA</CreatedByUser>
      <PublishingSystem>AAA</PublishingSystem>
      <PublishedTimestamp>2013-02-09T17:29:47-05:00</PublishedTimestamp>
      <EventType>Expire</EventType>
      <Topic>
      <Name>name/Create</Name>
      </Topic>
      </Header>
      <Body>
      <batchCreateDocumentsNotification xmlns="urn://mqc.abc.com/batch/2010/08/13">
      <batchId>11111111111</batchId>
      <repositoryId>SSSSSSSS</repositoryId>
      <documents>
      <document>
      <documentClass>ABC</documentClass>
      <documentId>1234567</documentId>
      </document>
      </documents>
      </batchCreateDocumentsNotification>
      </Body>
      </ABCNotificationEnvelope>
        • 1. Re: HELP TO RETERIVE DATA FROM XML
          odie_63
          Please see : {message:id=9360002}
          and post all the information needed.

          What have you tried so far?
          • 2. Re: HELP TO RETERIVE DATA FROM XML
            Solomon Yakobson
            with t as (
                       select xmltype('<ABCNotificationEnvelope xsi:noNamespaceSchemaLocation="ABCNotification.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="urn://wsc.ABC.com/dm/2010/02/02" xmlns:ns6="http://docs.oasis-open.org/wsn/t-1" xmlns:ns5="http://docs.oasis-open.org/wsrf/r-2" xmlns:ns4="http://docs.oasis-open.org/wsrf/bf-2" xmlns:ns3="http://docs.oasis-open.org/wsn/b-2" xmlns:ns2="http://www.w3.org/2005/08/addressing" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:env="http://schemas.xmlsoap.org/soap/envelope/">
            <Header>
            <Version>V1_2010_02_16</Version>
            <Source>AAA</Source>
            <CreatedByUser>AAA</CreatedByUser>
            <PublishingSystem>AAA</PublishingSystem>
            <PublishedTimestamp>2013-02-09T17:29:47-05:00</PublishedTimestamp>
            <EventType>Expire</EventType>
            <Topic>
            <Name>name/Create</Name>
            </Topic>
            </Header>
            <Body>
            <batchCreateDocumentsNotification xmlns="urn://mqc.abc.com/batch/2010/08/13">
            <batchId>11111111111</batchId>
            <repositoryId>SSSSSSSS</repositoryId>
            <documents>
            <document>
            <documentClass>ABC</documentClass>
            <documentId>1234567</documentId>
            </document>
            </documents>
            </batchCreateDocumentsNotification>
            </Body>
            </ABCNotificationEnvelope>') xml_doc from dual
                      )
            select  x.*
              from  t,
                    xmltable(
                             xmlnamespaces('urn://mqc.abc.com/batch/2010/08/13' As "n"),
                             '//Body/n:batchCreateDocumentsNotification/n:documents/n:document'
                             passing xml_doc
                             columns
                               documentId number path 'n:documentId'
                            ) x
            /
            
            DOCUMENTID
            ----------
               1234567
            
            SQL>
            SY.
            • 3. Re: HELP TO RETERIVE DATA FROM XML
              BluShadow
              Like this?
              SQL> ed
              Wrote file afiedt.buf
              
                1  with t as (select xmltype('<ABCNotificationEnvelope xsi:noNamespaceSchemaLocation="ABCNotification.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ns="urn://wsc.ABC.com/dm/2010/02/02" xmlns:ns6="http://docs.oasis-open.org/wsn/t-1" xml
              ns:ns5="http://docs.oasis-open.org/wsrf/r-2" xmlns:ns4="http://docs.oasis-open.org/wsrf/bf-2" xmlns:ns3="http://docs.oasis-open.org/wsn/b-2" xmlns:ns2="http://www.w3.org/2005/08/addressing" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:en
              v="http://schemas.xmlsoap.org/soap/envelope/">
                2    <Header>
                3      <Version>V1_2010_02_16</Version>
                4      <Source>AAA</Source>
                5      <CreatedByUser>AAA</CreatedByUser>
                6      <PublishingSystem>AAA</PublishingSystem>
                7      <PublishedTimestamp>2013-02-09T17:29:47-05:00</PublishedTimestamp>
                8      <EventType>Expire</EventType>
                9      <Topic>
               10        <Name>name/Create</Name>
               11      </Topic>
               12    </Header>
               13    <Body>
               14      <batchCreateDocumentsNotification xmlns="urn://mqc.abc.com/batch/2010/08/13">
               15        <batchId>11111111111</batchId>
               16        <repositoryId>SSSSSSSS</repositoryId>
               17        <documents>
               18          <document>
               19            <documentClass>ABC</documentClass>
               20            <documentId>1234567</documentId>
               21          </document>
               22        </documents>
               23      </batchCreateDocumentsNotification>
               24    </Body>
               25  </ABCNotificationEnvelope>') as xml from dual)
               26  --
               27  -- end of test data
               28  --
               29  select x.*
               30  from   t
               31        ,xmltable(xmlnamespaces('urn://mqc.abc.com/batch/2010/08/13' as "x"),
               32                  '/ABCNotificationEnvelope/Body/x:batchCreateDocumentsNotification'
               33                  passing t.xml
               34                  columns document_id number path '//x:documentId'
               35*                ) x
              SQL> /
              
              DOCUMENT_ID
              -----------
                  1234567
              • 4. Re: HELP TO RETERIVE DATA FROM XML
                BluShadow
                Solomon,
                You must have posted that in the time I've been answering other posts... dang, I must remember to refresh the posts before I answer them. ;)
                • 5. Re: HELP TO RETERIVE DATA FROM XML
                  odie_63
                  You two are spoiling all the fun ;) ... and showing bad practices too ("lazy" descendant axis).

                  I know we're not at school but sometimes it's interesting to just push people in the right direction so that they find the solution by themselves.
                  • 6. Re: HELP TO RETERIVE DATA FROM XML
                    BluShadow
                    odie_63 wrote:
                    You two are spoiling all the fun ;) ... and showing bad practices too ("lazy" descendant axis).

                    I know we're not at school but sometimes it's interesting to just push people in the right direction so that they find the solution by themselves.
                    Well, considering the OP had said they were able to get the timestamp value (which is in the default namespace), it seemed to me they were just in a position where they weren't sure how to get at the inner namespaces. Ok, it would have been good if they'd posted their code, but in this case, it was simple enough to demonstrate, and if they don't understand they can ask. ;)

                    As for the lazy part... I don't usually do that in my own queries. :D