This discussion is archived
6 Replies Latest reply: Feb 20, 2013 6:22 AM by BluShadow RSS

HELP TO RETERIVE DATA FROM XML

675884 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points