Sawyer Sawyer - 1 year ago 76
SQL Question

How to return a substring from a text in Oracle

I am fetching a value from Oracle DB which of type BLOB and in the form of XML. The XML is huge and I would like to search for strings which consits "Department" in it for example

<ElectronicsDepartment>
<term>I year</term>
<Capacity>60</Capacity>
</ElectronicsDepartment>
<ComputersDepartment>
<term>I year</term>
<Capacity>65</Capacity>
<ComputersDepartment>
<MechanicalDepartment>
<term>I year</term>
<Capacity>65</Capacity>
</MechanicalDepartment>


The XML is very long covering all the terms from I year to IV year of Engineering Degree. Now I would like to fetch details from the DB such the results should be in the format below

Department Term Capactity
Electronics I year 60
Computers I year 65
Mechanical I year 65


I have been trying the below query

SELECT Department/(SELECT Department FROM University where Department like '%Department %' ), Term, Capacity From University


but the query is showing the error

ORA-00932: inconsistent datatypes: expected NUMBER got CLOB

Answer Source

If you are actually starting from a CLOB that contains an XML document, of which you've shown a (invalid) fragment, then you can use the built-in XML DB functions to extract the data directly from the XML.

You seem to want to match any node ending with Department, and to extract the first part of that node name as the department name, along with the term and capacity values under it.

You can do that with XMLTable and a suitable XPath, e.g.:

-- CTE to represent your raw XML CLOB, with dummy root node
with university (department) as (
select to_clob('<root>
  <ElectronicsDepartment>
      <term>I year</term>
      <Capacity>60</Capacity>
  </ElectronicsDepartment>
  <ComputersDepartment>
      <term>I year</term>
      <Capacity>65</Capacity>
  </ComputersDepartment>
  <MechanicalDepartment>
      <term>I year</term>
      <Capacity>65</Capacity>
  </MechanicalDepartment>
</root>') from dual
)
-- end of CTE, actual query below
select x.department, x.term, x.capacity
from university u
cross join xmltable (
  '//*[ends-with(name(), "Department")]'
  passing xmltype(u.department)
  columns department varchar2(20) path 'substring(name(), 1, string-length(name()) - 10)',
    term varchar2(10) path 'term',
    capacity number path 'Capacity'
) x;

DEPARTMENT           TERM         CAPACITY
-------------------- ---------- ----------
Electronics          I year             60
Computers            I year             65
Mechanical           I year             65

The '//*[ends-with(name(), "Department")]' only matches nodes that end with Department. The 'substring(name(), 1, string-length(name()) - 10)' extracts all but the last 10 characters from that node name, getting Computers or whatever. The other two columns are more straightforward.

If you need to filter which CLOBs are included, you can add a where clause as normal after the from and the join to the XMLTable, e.g. to filter on a timestamp column in the university table:

select x.department, x.term, x.capacity
from university u
cross join xmltable (
  '//*[ends-with(name(), "Department")]'
  passing xmltype(u.department)
  columns department varchar2(20) path 'substring(name(), 1, string-length(name()) - 10)',
    term varchar2(10) path 'term',
    capacity number path 'Capacity'
) x
where your_timestamp_col >= timestamp '2017-06-01 00:00:00'
and your_timestamp_col < timestamp '2017-07-01 00:00:00';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download