0

This is my XML Content stored same as it is in a Column name "Xml_Column" in the table "TEST_TABLE" and the data type of the column is clob. 

<ns0:TEST_EVENTS xmlns:ns0="http://TEST.APPLICATION.ABC.Schemas.XML_Target_TESTEvents">
   <compname>Sherlock</compname>
        <Add>Homes</Add>
    <Employee>
        <firstname>Jim</firstname>
        <lastname>Moriarty</lastname>
        <age>52</age>
        <email>jim@sh.com</email>
    </Employee>
</ns0:TEST_EVENTS>

My requirement is to fetch the firstname and last name from this column where my xml content is stored and display. Any help

Anshu Kumar
  • 51
  • 1
  • 7
  • see http://stackoverflow.com/questions/3200766/how-to-get-first-element-by-xpath-in-oracle for an XPATH in SQL expression. – Conffusion Dec 10 '14 at 10:35
  • http://stackoverflow.com/questions/15139329/extract-specfic-value-from-clob-containing-xml-while-creating-one-delimited-st?rq=1 Hi guys I found the answer in this link.... – Anshu Kumar Dec 10 '14 at 12:48

2 Answers2

0

Use EXTRACTVALUE, it's a simpliest way: https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions052.htm

For optimization, you should make inner query for constructing XMLType instance based on your clob; in the outer query use EXTRACTVALUE to select every value you wish.

Sanders the Softwarer
  • 2,478
  • 1
  • 13
  • 28
0

For example:

  SELECT extractValue(OBJECT_VALUE, '/firstname'),
         extractValue(OBJECT_VALUE, '/lastname')
         FROM TEST_TABLE;

All firstname and lastname:

SELECT  XMLQuery('for $d in /Employee
             return ($d/firstname/text(),$d/lastname/text())
            PASSING OBJECT_VALUE
            RETURNING CONTENT) FROM TEST_TABLE;
TociBala
  • 1