CODEWITHSUNDEEP
xmloracle-database
user2114865
user2114865

Reputation: 15

extract xml tag value from clob field using oracle

I have the following XML data stored in DB field:

<FM>
<SectionsList>
<Section>
<SectionId>Section_one</SectionId>
</Section>
<Section>
<SectionId>Section_two</SectionId> 
<Fields>
<FormField>
<FieldId>REQUESTID</FieldId>
<FieldValue>ABC1234</FieldValue>
</FormField>
<FormField>
<FieldId>REQUESTNAME</FieldId>
<FieldValue>JASMINE</FieldValue>
</FormField>
</Fields>
</Section>
</SectionsList>
</FM>

I want to retrieve the value of FieldValue of FormField tag having FieldId of REQUESTNAME from section having SectionId of Section_two. Result should be JASMINE.

I am executing query in Oracle as:

SELECT EXTRACTVALUE(xmltype(req_details),
  '/FM/SectionsList/Section[@SectionId="Section_two"]/Fields/FormField/FieldValue[@FieldId="REQUESTNAME"]')
from table

But result is NULL. How can i extract the value in Oracle?

Upvotes: 1

Views: 11551

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

You are confusing attribute and node selection. SectionId is not an attribute of the section, which is what your [@SectionId=...] is looking for.

You could do this by identifying the node text values and walking back up the tree:

select extractvalue(xmltype(req_details),
  '/FM/SectionsList/Section/SectionId[text()="Section_two"]/../Fields/FormField/FieldId[text()="REQUESTNAME"]/../FieldValue')
  as result
from your_table

RESULT              
--------------------
JASMINE

or as extractvalue() is deprecated, with an XMLQuery instead:

select xmlquery(
  '/FM/SectionsList/Section/SectionId[text()="Section_two"]/../Fields/FormField/FieldId[text()="REQUESTNAME"]/../FieldValue/text()'
  passing xmltype(req_details)
  returning content) as result
from your_table


RESULT              
--------------------
JASMINE

Or with a more explicit XPath that avoids having to walk back up the tree (so a little easier to follow, and harder to get lost):

select xmlquery(
  'for $i in /FM/SectionsList/Section where $i/SectionId="Section_two"
   return
     for $j in $i/Fields/FormField where $j/FieldId="REQUESTNAME"
       return $j/FieldValue/text()'
  passing xmltype(req_details)
  returning content) as result
from your_table;

RESULT              
--------------------
JASMINE

Upvotes: 0

Related Questions