CODEWITHSUNDEEP
xmloracle-database
Nianios
Nianios

Reputation: 1436

extract oracle xml node

I have a oracle clob and I want to extract part of it : The clob:

<JobDetails>
 <ObjectDetails />
 <DestinationDetails>
  <AddressTo>[email protected]</AddressTo>
  <AddressCc></AddressCc>
  <AddressBcc></AddressBcc>
  <Subject>Automated email </Subject>
  <Message>Automated email </Message>
  <Attachment></Attachment>
  <Importance>False</Importance>
 </DestinationDetails>
</JobDetails>

I want to get from up to /DestinationDetails> is not include every time the same nodes. I don't want to just delete the beginning and the end of the string.

Upvotes: 0

Views: 97

Answers (1)

Chris Cameron-Mills
Chris Cameron-Mills

Reputation: 4657

Cast it to XMLTYPE and use XPath/XQuery

Older, deprecated syntax:

SELECT EXTRACT(XMLTYPE(clob_col), '/*/DestinationDetails')
FROM some_table

Newer XQuery syntax:

SELECT
  XMLQUERY('/*/DestinationDetails' PASSING XMLTYPE(clob_col) RETURNING CONTENT) xml_fragment
FROM some_table

Upvotes: 0

Related Questions