1 2 Previous Next 27 Replies Latest reply: Sep 5, 2014 4:00 AM by chris227 RSS

    Querying CLOB column (with XML content)

    Veera_V

      1. How to extract a particular xml tag value from a column with CLOB datatype.

       

      Example:

      <REQUEST_DETAIL>

        <GROUP_TYPE>PR</GROUP_TYPE>

        <GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>

         <BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN>

         <PROJ_MGR_ID></PROJ_MGR_ID>

        <PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>

        </REQUEST_DETAIL>

       

      select

      xmltype(e.x).extract('/REQUEST_DETAIL/GROUP_TYPE/text()').getStringVal()

        from t e

      /

       

      above example works.

       

      If I dont know the order of xml tag to be queried how can I get the value.

       

      (*****/****/***/***/REQUEST_DETAIL/GROUP_TYPE/***/**** )

       

      2.using DBMS_LOB.SUBSTR is taking more time to execute and it never return results and session goes wait status for accesing LOB_INDEX (concurrently the CLOB column  is used by application).

       

      Is there any other option to get the results quicker.

       

      SELECT DBMS_LOB.SUBSTR (xml_blob, 4, DBMS_LOB.INSTR (xml_blob, '<Name>', 1, 1 ) + 6 ) name,

              status,

              COUNT (*) cnt

               FROM LOG_TBL

           WHERE TRUNC(IO_DATE)=TRUNC(SYSDATE)

                   GROUP BY DBMS_LOB.SUBSTR (xml_blob, 4, DBMS_LOB.INSTR (xml_blob, '<Name>', 1, 1 ) + 6 ),

              status

       

       

      Regards,

      Veera

        • 1. Re: Querying CLOB column (with XML content)
          BluShadow

          Your example data is rather basic so there's not much to demonstrate.  However, you don't need to know the order of the XML data, as the tag names identify the data, regardless of the order they are provided in the XML.  It CAN make a difference if there are repeated elements and you want to pick them out in order, but your example data doesn't indicate such a need.

           

          SQL> ed
          Wrote file afiedt.buf

            1  with t as (select '<REQUEST_DETAIL>
            2    <GROUP_TYPE>PR</GROUP_TYPE>
            3    <GROUP_NAME>DATA ENGINEERING ASSET MANAGEMENT</GROUP_NAME>
            4    <BUS_UNIT_ACRN>SCS-FCAT</BUS_UNIT_ACRN>
            5    <PROJ_MGR_ID></PROJ_MGR_ID>
            6    <PROJ_MGR_NAME>Roland Roy</PROJ_MGR_NAME>
            7  </REQUEST_DETAIL>' as clob_data from dual)
            8  --
            9  -- end of test data
          10  --
          11  select x.*
          12  from   t
          13        ,xmltable('/'
          14                  passing xmltype(t.clob_data)
          15                  columns group_type    varchar2(2)  path './GROUP_TYPE'
          16                         ,group_name    varchar2(30) path './GROUP_NAME'
          17                         ,bus_unit_acrn varchar2(10) path './BUS_UNIT_ACRN'
          18                         ,proj_mgr_id   number       path './PROJ_MGR_ID'
          19                         ,proj_mgr_name varchar2(20) path './PROJ_MGR_NAME'
          20*                ) x
          SQL> /

           

          GR GROUP_NAME                     BUS_UNIT_A PROJ_MGR_ID PROJ_MGR_NAME
          -- ------------------------------ ---------- ----------- --------------------
          PR DATA ENGINEERING ASSET MANAGEM SCS-FCAT               Roland Roy

           

          Notes:

          a) you are better storing your XML in XMLTYPE on the database rather than CLOB

          b) you should avoid using extract and getStringVal (or getClobVal) functions as these are old and deprecated, better to use XMLTABLE or XQuery functionality provided since 10g and 11g respectively.

          c) don't use LOB/String functions to process XML data.

          • 2. Re: Querying CLOB column (with XML content)
            Veera_V

            Hi BluShadow,

             

            I am getting null value when I ran below:

             

            SELECT X.*

              from   t

                    ,xmltable('/'

                              PASSING XMLTYPE(t.XML_BLOB)

                              columns DomainName    varchar2(15)  path './DomainName'

                                                 ) X

                                           WHERE t.no=317229340    

             

            From XML_BLOB (column CLOB type):-

            ..

            ..

            <DomainName>P099</DomainName>

            ..

            ..

             

             

            Regards,

            Veera

            • 3. Re: Querying CLOB column (with XML content)
              Moazzam

              See below example

               

              SELECT dbms_xmlgen.convert(x.description.getClobVal(), 1) as description
              FROM test_xml t
                 , XMLTable(
                     '/*/record'
                     passing t.xml_data
                     columns
                       description  xmltype path 'description/text()'
                   ) x
              ;

               

              Oracle SQL - Extracting clob value from XML with repeating nodes

              • 4. Re: Querying CLOB column (with XML content)
                Veera_V

                Seems this <DomainName>P099</DomainName> comes repatedly for multiple elements . (i need unique of single domain name just one P099 and ignore domains "PUB" in the same clob data).

                • 5. Re: Re: Querying CLOB column (with XML content)
                  odie_63

                  Veera,

                   

                  What's your database version? (SELECT * FROM v$version)

                   

                  Why do you not know the paths to query?

                   

                  If you want to extract data from a specific node regardless of its place, use a descendant axis (//) :

                  SELECT X.*

                  from   t

                       , xmltable('.'

                           PASSING XMLTYPE(t.XML_BLOB)

                           columns DomainName    varchar2(15)  path '//DomainName'

                        ) X

                   

                  but be aware it's not a recommended way since Oracle has to walk down the entire tree to find out the target node, which could lead to serious performance issues.

                  If you know the complete path from root then by all means use it.

                  • 6. Re: Re: Querying CLOB column (with XML content)
                    odie_63

                    2729533 wrote:

                     

                    Seems this <DomainName>P099</DomainName> comes repatedly for multiple elements . (i need unique of single domain name just one P099 and ignore domains "PUB" in the same clob data).

                    Post a valid sample XML please, and explain clearly what result you want.

                     

                    Thanks.

                    • 7. Re: Querying CLOB column (with XML content)
                      Veera_V

                      Database version is 11.2.0.3

                       

                       

                      single xml will have multiple transactions (multiple elements and they will be loaded into different related tables ).

                      • 8. Re: Querying CLOB column (with XML content)
                        BluShadow

                        To repeat what odie asked...

                         

                        Post a valid sample XML please

                        • 9. Re: Querying CLOB column (with XML content)
                          Veera_V

                          From the below xml content I need only domainname value (not required to search entire xml - 1st occurance of domainname . here P099).

                           

                           

                          below query works for less no of inputs.

                          select DBMS_LOB.SUBSTR (XML_BLOB, 4, DBMS_LOB.INSTR (XML_BLOB, '<DomainName>', 1, 1 ) + 12 )

                          from t_tab

                          WHERE t_tab=317663815 

                           

                          (If I  for entire table , it goes to wait state for accesing LOB index and never return rows;this table is live and continuously using the XML content to processs).

                           

                           

                          <?xml version="1.0" encoding="UTF-8"?>

                          <Transmission>

                          <TransmissionHeader>

                          <UserName>P016.INT1</UserName>

                          <Password>INT1</Password>

                          <SenderTransmissionNo>P099_03092014021310_05081733000240</SenderTransmissionNo>

                          <AckSpec>

                          <ComMethodGid>

                          <Gid>

                          <Xid>EMAIL</Xid>

                          </Gid>

                          </ComMethodGid>

                          <AckOption>ERROR</AckOption>

                          </AckSpec>

                          </TransmissionHeader>

                          <TransmissionBody>

                          <GLogXMLElement>

                          <Location>

                          <TransactionCode>IU</TransactionCode>

                          <LocationGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>969000545</Xid>

                          </Gid>

                          </LocationGid>

                          <LocationName>KLA TENCOR</LocationName>

                          <Address>

                          <AddressLine1>32 CH DU VIEUX CHENE</AddressLine1>

                          <AddressLine2>38240 MEYLAN</AddressLine2>

                          <City>MEYLAN</City>

                          <PostalCode>38240</PostalCode>

                          <CountryCode3Gid>

                          <Gid>

                          <Xid>FR</Xid>

                          </Gid>

                          </CountryCode3Gid>

                          <TimeZoneGid>

                          <Gid>

                          <Xid>Local</Xid>

                          </Gid>

                          </TimeZoneGid>

                          </Address>

                          <Contact>

                          <ContactGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>969000545</Xid>

                          </Gid>

                          </ContactGid>

                          <FirstName>KLA TENCOR</FirstName>

                          </Contact>

                          <ParentLocationGid>

                          <LocationGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>P099</Xid>

                          </Gid>

                          </LocationGid>

                          </ParentLocationGid>

                          </Location>

                          </GLogXMLElement>

                          <GLogXMLElement>

                          <Location>

                          <TransactionCode>IU</TransactionCode>

                          <LocationGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>CONSIGNEE-9979028275</Xid>

                          </Gid>

                          </LocationGid>

                          <LocationName>KLA TENCOR</LocationName>

                          <Address>

                          <AddressLine1>159 OLD WOOD PLACE</AddressLine1>

                          <AddressLine2>ELIBURN - LIVINGSTON</AddressLine2>

                          <AddressLines>

                          <SequenceNumber>3</SequenceNumber>

                          <AddressLine>GREAT BRITAIN</AddressLine>

                          </AddressLines>

                          <City>ELIBURN - LIVINGSTON</City>

                          <PostalCode>1101</PostalCode>

                          <CountryCode3Gid>

                          <Gid>

                          <Xid>XL</Xid>

                          </Gid>

                          </CountryCode3Gid>

                          <TimeZoneGid>

                          <Gid>

                          <Xid>Local</Xid>

                          </Gid>

                          </TimeZoneGid>

                          </Address>

                          <Contact>

                          <ContactGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>CONSIGNEE-9979028275</Xid>

                          </Gid>

                          </ContactGid>

                          <FirstName>KLA TENCOR</FirstName>

                          </Contact>

                          <ParentLocationGid>

                          <LocationGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>P099</Xid>

                          </Gid>

                          </LocationGid>

                          </ParentLocationGid>

                          </Location>

                          </GLogXMLElement>

                          <GLogXMLElement>

                          <Location>

                          <TransactionCode>IU</TransactionCode>

                          <LocationGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>PAYEE-969000545</Xid>

                          </Gid>

                          </LocationGid>

                          <Address>

                          <CountryCode3Gid>

                          <Gid>

                          <Xid>ZZZ</Xid>

                          </Gid>

                          </CountryCode3Gid>

                          <TimeZoneGid>

                          <Gid>

                          <Xid>Local</Xid>

                          </Gid>

                          </TimeZoneGid>

                          </Address>

                          <Contact>

                          <ContactGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>PAYEE-969000545</Xid>

                          </Gid>

                          </ContactGid>

                          <FirstName/>

                          </Contact>

                          <ParentLocationGid>

                          <LocationGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>P099</Xid>

                          </Gid>

                          </LocationGid>

                          </ParentLocationGid>

                          </Location>

                          </GLogXMLElement>

                          <GLogXMLElement>

                          <ActualShipment>

                          <Shipment>

                          <ShipmentHeader>

                          <ShipmentGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>9979028275</Xid>

                          </Gid>

                          </ShipmentGid>

                          <TransactionCode>RC</TransactionCode>

                          <ShipmentRefnum>

                          <ShipmentRefnumQualifierGid>

                          <Gid>

                          <Xid>GLOG</Xid>

                          </Gid>

                          </ShipmentRefnumQualifierGid>

                          <ShipmentRefnumValue>P099.9979028275</ShipmentRefnumValue>

                          </ShipmentRefnum>

                          <ShipmentRefnum>

                          <ShipmentRefnumQualifierGid>

                          <Gid>

                          <Xid>PRIMARY TRACKING REF</Xid>

                          </Gid>

                          </ShipmentRefnumQualifierGid>

                          <ShipmentRefnumValue>9979028275</ShipmentRefnumValue>

                          </ShipmentRefnum>

                          <ShipmentRefnum>

                          <ShipmentRefnumQualifierGid>

                          <Gid>

                          <DomainName>PUBLIC</DomainName>

                          <Xid>REF</Xid>

                          </Gid>

                          </ShipmentRefnumQualifierGid>

                          <ShipmentRefnumValue>XXX</ShipmentRefnumValue>

                          </ShipmentRefnum>

                          <ServiceProviderGid>

                          <Gid>

                          <Xid>DHL EXPRESS</Xid>

                          </Gid>

                          </ServiceProviderGid>

                          <StartDate>20140902151500</StartDate>

                          <EndDate>20140903151500</EndDate>

                          <InvolvedParty>

                          <InvolvedPartyQualifierGid>

                          <Gid>

                          <DomainName>PUBLIC</DomainName>

                          <Xid>SHIPPER</Xid>

                          </Gid>

                          </InvolvedPartyQualifierGid>

                          <ContactRef>

                          <ContactGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>969000545</Xid>

                          </Gid>

                          </ContactGid>

                          </ContactRef>

                          </InvolvedParty>

                          <InvolvedParty>

                          <InvolvedPartyQualifierGid>

                          <Gid>

                          <DomainName>PUBLIC</DomainName>

                          <Xid>CONSIGNEE</Xid>

                          </Gid>

                          </InvolvedPartyQualifierGid>

                          <ContactRef>

                          <ContactGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>CONSIGNEE-9979028275</Xid>

                          </Gid>

                          </ContactGid>

                          </ContactRef>

                          </InvolvedParty>

                          <InvolvedParty>

                          <InvolvedPartyQualifierGid>

                          <Gid>

                          <DomainName>PUBLIC</DomainName>

                          <Xid>PAYEE</Xid>

                          </Gid>

                          </InvolvedPartyQualifierGid>

                          <ContactRef>

                          <ContactGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>PAYEE-969000545</Xid>

                          </Gid>

                          </ContactGid>

                          </ContactRef>

                          </InvolvedParty>

                          <Remark>

                          <RemarkSequence>114</RemarkSequence>

                          <RemarkQualifierGid>

                          <Gid>

                          <Xid>DESCRIPTION OF GOODS</Xid>

                          </Gid>

                          </RemarkQualifierGid>

                          <RemarkText>ECX</RemarkText>

                          </Remark>

                          <Remark>

                          <RemarkSequence>104</RemarkSequence>

                          <RemarkQualifierGid>

                          <Gid>

                          <Xid>TOTAL NUMBER OF PIECES</Xid>

                          </Gid>

                          </RemarkQualifierGid>

                          <RemarkText>1</RemarkText>

                          </Remark>

                          </ShipmentHeader>

                          <ShipmentHeader2>

                          <Perspective>B</Perspective>

                          <ShipmentTypeGid>

                          <Gid>

                          <Xid>TRANSPORT</Xid>

                          </Gid>

                          </ShipmentTypeGid>

                          <DimWeight>

                          <Weight>

                          <WeightValue>0.500</WeightValue>

                          <WeightUOMGid>

                          <Gid>

                          <Xid>KG</Xid>

                          </Gid>

                          </WeightUOMGid>

                          </Weight>

                          </DimWeight>

                          </ShipmentHeader2>

                          <SEquipment>

                          <SEquipmentGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>9979028275</Xid>

                          </Gid>

                          </SEquipmentGid>

                          </SEquipment>

                          <ShipmentStop>

                          <StopSequence>1</StopSequence>

                          <LocationRef>

                          <LocationGid>

                          <Gid>

                          <DomainName>PUBLIC</DomainName>

                          <Xid>LYS</Xid>

                          </Gid>

                          </LocationGid>

                          </LocationRef>

                          <IsPermanent>Y</IsPermanent>

                          <ShipmentStopDetail>

                          <Activity>P</Activity>

                          <ShipUnitGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>9979028275</Xid>

                          </Gid>

                          </ShipUnitGid>

                          </ShipmentStopDetail>

                          </ShipmentStop>

                          <ShipmentStop>

                          <StopSequence>99</StopSequence>

                          <LocationRef>

                          <LocationGid>

                          <Gid>

                          <DomainName>PUBLIC</DomainName>

                          <Xid>AMS</Xid>

                          </Gid>

                          </LocationGid>

                          </LocationRef>

                          <IsPermanent>Y</IsPermanent>

                          <ShipmentStopDetail>

                          <Activity>D</Activity>

                          <ShipUnitGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>9979028275</Xid>

                          </Gid>

                          </ShipUnitGid>

                          </ShipmentStopDetail>

                          </ShipmentStop>

                          <ShipUnit>

                          <ShipUnitGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>9979028275</Xid>

                          </Gid>

                          </ShipUnitGid>

                          <WeightVolume>

                          <Weight>

                          <WeightValue>1.700</WeightValue>

                          <WeightUOMGid>

                          <Gid>

                          <Xid>KG</Xid>

                          </Gid>

                          </WeightUOMGid>

                          </Weight>

                          <Volume>

                          <VolumeValue>0</VolumeValue>

                          <VolumeUOMGid>

                          <Gid>

                          <Xid>CUMTR</Xid>

                          </Gid>

                          </VolumeUOMGid>

                          </Volume>

                          </WeightVolume>

                          <UnitNetWeightVolume>

                          <Weight>

                          <WeightValue>0.00</WeightValue>

                          <WeightUOMGid>

                          <Gid>

                          <Xid>KG</Xid>

                          </Gid>

                          </WeightUOMGid>

                          </Weight>

                          <Volume>

                          <VolumeValue>0.00</VolumeValue>

                          <VolumeUOMGid>

                          <Gid>

                          <Xid>CUMTR</Xid>

                          </Gid>

                          </VolumeUOMGid>

                          </Volume>

                          </UnitNetWeightVolume>

                          <ShipUnitContent>

                          <LineNumber>1</LineNumber>

                          <ItemQuantity>

                          <WeightVolume>

                          <Weight>

                          <WeightValue>0</WeightValue>

                          <WeightUOMGid>

                          <Gid>

                          <Xid>KG</Xid>

                          </Gid>

                          </WeightUOMGid>

                          </Weight>

                          <Volume>

                          <VolumeValue>0</VolumeValue>

                          <VolumeUOMGid>

                          <Gid>

                          <Xid>CUMTR</Xid>

                          </Gid>

                          </VolumeUOMGid>

                          </Volume>

                          </WeightVolume>

                          </ItemQuantity>

                          <ShipUnitLineRefnum>

                          <ShipUnitLineRefnumQualifierGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>DNN</Xid>

                          </Gid>

                          </ShipUnitLineRefnumQualifierGid>

                          <ShipUnitLineRefnumValue>XXX</ShipUnitLineRefnumValue>

                          </ShipUnitLineRefnum>

                          </ShipUnitContent>

                          <Remark>

                          <RemarkSequence>103</RemarkSequence>

                          <RemarkQualifierGid>

                          <Gid>

                          <Xid>TOTAL BOOKED GROSS WEIGHT</Xid>

                          </Gid>

                          </RemarkQualifierGid>

                          <RemarkText>1.700 KG</RemarkText>

                          </Remark>

                          <Remark>

                          <RemarkSequence>104</RemarkSequence>

                          <RemarkQualifierGid>

                          <Gid>

                          <Xid>TOTAL NUMBER OF PIECES</Xid>

                          </Gid>

                          </RemarkQualifierGid>

                          <RemarkText>1</RemarkText>

                          </Remark>

                          <Remark>

                          <RemarkSequence>105</RemarkSequence>

                          <RemarkQualifierGid>

                          <Gid>

                          <Xid>CHARGEABLE WEIGHT</Xid>

                          </Gid>

                          </RemarkQualifierGid>

                          <RemarkText>0.500 KG</RemarkText>

                          </Remark>

                          <SEquipmentGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>9979028275</Xid>

                          </Gid>

                          </SEquipmentGid>

                          </ShipUnit>

                          </Shipment>

                          </ActualShipment>

                          </GLogXMLElement>

                          <GLogXMLElement>

                          <ShipmentStatus>

                          <ServiceProviderAlias>

                          <ServiceProviderAliasQualifierGid>

                          <Gid>

                          <Xid>GLOG</Xid>

                          </Gid>

                          </ServiceProviderAliasQualifierGid>

                          <ServiceProviderAliasValue>DHL EXPRESS</ServiceProviderAliasValue>

                          </ServiceProviderAlias>

                          <ShipmentRefnum>

                          <ShipmentRefnumQualifierGid>

                          <Gid>

                          <Xid>GLOG</Xid>

                          </Gid>

                          </ShipmentRefnumQualifierGid>

                          <ShipmentRefnumValue>P099.9979028275</ShipmentRefnumValue>

                          </ShipmentRefnum>

                          <StatusLevel>SHIPMENT</StatusLevel>

                          <StatusCodeGid>

                          <Gid>

                          <DomainName>P099</DomainName>

                          <Xid>SATA</Xid>

                          </Gid>

                          </StatusCodeGid>

                          <EventDate>20140903005200</EventDate>

                          <SSRemarks>EVENT_REMARK: &lt;BR&gt;SPECIFIC_REMARK: |||||||</SSRemarks>

                          <SSStop>

                          <SSLocation>

                          <LocationRefnumQualifierGid>

                          <Gid>

                          <Xid>GLOG</Xid>

                          </Gid>

                          </LocationRefnumQualifierGid>

                          <LocationID>BRU</LocationID>

                          </SSLocation>

                          </SSStop>

                          </ShipmentStatus>

                          </GLogXMLElement>

                          </TransmissionBody>

                          </Transmission>

                          • 10. Re: Re: Querying CLOB column (with XML content)
                            Jason_(A_Non)

                            To refine what Odie provided, this appears to solve your issue, but it is not tested, besides verifying the XPath returns nodes.  I did not confirm all the nodes you needed are returned so you will want to verify that

                            SELECT X.*
                            from   t
                                 , xmltable('/Transmission/TransmissionBody/GLogXMLElement/Location/*/Gid/DomainName'
                                     PASSING XMLTYPE(t.XML_BLOB)
                                     columns DomainName    varchar2(15)  path '.'
                                  ) X
                            
                            

                             

                            You won't find any CLOB/String parsing routines here, given you are dealing with XML.

                            • 11. Re: Querying CLOB column (with XML content)
                              Veera_V

                              If  I dont know the full path (how should i change the query)

                               

                              SELECT X.*

                              FROM   t

                                   , xmltable('/Transmission/*/DomainName'

                                       PASSING XMLTYPE(t.XML_BLOB)

                                       COLUMNS DOMAINNAME    VARCHAR2(15)  PATH './*'

                                    ) X

                              where t.no=317663815

                               

                              for Odie sql, i m getting ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

                              • 12. Re: Re: Querying CLOB column (with XML content)
                                odie_63

                                for Odie sql, i m getting ORA-19279: XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence

                                Yes, my query was assuming only one occurrence of the target node.

                                Use a positional predicate to select only the first one :


                                SELECT X.* 
                                FROM   t
                                     , xmltable('//DomainName[1]' 
                                         PASSING xmlparse(document t.XML_BLOB) 
                                         COLUMNS DOMAINNAME    VARCHAR2(15)  PATH '.' 
                                      ) X 
                                where t.no=317663815
                                

                                 

                                or

                                 

                                select xmlcast(
                                         xmlquery('//DomainName[1]' passing xmlparse(document t.xml_blob) returning content)  
                                         as varchar2(15)
                                       )
                                from t
                                where ...
                                
                                • 13. Re: Re: Querying CLOB column (with XML content)
                                  Veera_V

                                  Your 1st sql returln all domainname (p099 and public - seems searches all node in one xml). I only need 1st domain name (and omit domains with PUBLIC)

                                  1. SELECT X.*  
                                  2. FROM   t 
                                  3.      , xmltable('//DomainName[1]'  
                                  4.          PASSING xmlparse(document t.XML_BLOB)  
                                  5.          COLUMNS DOMAINNAME    VARCHAR2(15)  PATH '.'  
                                  6.       ) X  
                                  7. where t.no=317663815 


                                  • 14. Re: Querying CLOB column (with XML content)
                                    padders

                                    Getting the first node from any path in a potentially unordered document seems a rather non-deterministic requirement.

                                     

                                    The reason that '//DomainName[1]' doesn't work is that DomainName appears at multiple locations under Transmission/TransmissionBody, e.g.

                                     

                                    GLogXMLElement/ActualShipment/Shipment/ShipmentStop/ShipmentStopDetail/ShipUnitGid/Gid/DomainName
                                    GLogXMLElement/Location/Contact/ContactGid/Gid/DomainName
                                    GLogXMLElement/ActualShipment/Shipment/ShipUnit/SEquipmentGid/Gid/DomainName
                                    GLogXMLElement/ActualShipment/Shipment/ShipmentHeader/InvolvedParty/ContactRef/ContactGid/Gid/DomainName
                                    GLogXMLElement/ActualShipment/Shipment/ShipmentHeader/ShipmentGid/Gid/DomainName
                                    GLogXMLElement/ActualShipment/Shipment/ShipmentStop/LocationRef/LocationGid/Gid/DomainName
                                    GLogXMLElement/Location/LocationGid/Gid/DomainName
                                    GLogXMLElement/Location/ParentLocationGid/LocationGid/Gid/DomainName
                                    GLogXMLElement/ActualShipment/Shipment/ShipmentHeader/ShipmentRefnum/ShipmentRefnumQualifierGid/Gid/DomainName
                                    GLogXMLElement/ActualShipment/Shipment/ShipUnit/ShipUnitContent/ShipUnitLineRefnum/ShipUnitLineRefnumQualifierGid/Gid/DomainName
                                    GLogXMLElement/ShipmentStatus/StatusCodeGid/Gid/DomainName
                                    GLogXMLElement/ActualShipment/Shipment/ShipmentHeader/InvolvedParty/InvolvedPartyQualifierGid/Gid/DomainName
                                    GLogXMLElement/ActualShipment/Shipment/SEquipment/SEquipmentGid/Gid/DomainName
                                    GLogXMLElement/ActualShipment/Shipment/ShipUnit/ShipUnitGid/Gid/DomainName

                                     

                                    You could try something like '(//DomainName[.!="PUBLIC"])[1]' but as I said before it seems non-deterministic.

                                    1 2 Previous Next