Fetch Xml Content from a table in oracle

Question

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>[email protected]</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

see stackoverflow.com/questions/3200766/… for an XPATH in SQL expression. — Conffusion
– Conffusion, Commented Dec 10, 2014 at 10:35
stackoverflow.com/questions/15139329/… Hi guys I found the answer in this link.... — Anshu Kumar
– Anshu Kumar, Commented Dec 10, 2014 at 12:48

Sanders the Softwarer · Accepted Answer · 2014-12-10 10:36:38Z

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.

answered Dec 10, 2014 at 10:36

Sanders the Softwarer

2,5061 gold badge15 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TociBala · Accepted Answer · 2015-02-16 11:08:30Z

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;

answered Feb 16, 2015 at 11:08

TociBala

1

Collectives™ on Stack Overflow

Fetch Xml Content from a table in oracle

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related