How to Extract a value from XML file by using Oracle SQL

Extract(XML) function is used to get a node or nodes inside a XML document stored in Oracle DB. The syntax for EXTRACT function is;

EXTRACT(XML-Document-Column, ‘XPath expression‘)

EXTRACT(XML-Document-Column, ‘XPath expression’, ‘namespace’)

Example usages;

CREATE TABLE LIBRARY
(
ID_COLUMN NUMBER PRIMARY KEY,
XML_DATA_COLUMN XMLType
);

INSERT INTO LIBRARY(ID_COLUMN ,XML_DATA_COLUMN) VALUES
( 1
, XMLType(‘<?xml version=”1.0″ encoding=”UTF-8″?>
<LIBRARY>
    <BOOKS>
        <BOOK isbn=”ABCD7327923″>
            <NAME>Java Programing</NAME>
            <SUBJECT>Java J2EE</SUBJECT>
            <AUTHORS>
                <AUTHOR>Tuna TORE</AUTHOR>
                <AUTHOR>Linus Torvalds</AUTHOR>
                <AUTHOR>James Gosling</AUTHOR>
            </AUTHORS>
        </BOOK>
        <BOOK isbn=”DFGH09093232″>
            <NAME>XPATH for Dummies</NAME>
            <SUBJECT>XPATH development</SUBJECT>
            <AUTHORS>
                <AUTHOR>Linus Torvalds</AUTHOR>
                <AUTHOR>John Hawking</AUTHOR>
            </AUTHORS>
        </BOOK>
        <BOOK isbn=”DSKL2393A”>
            <NAME>J2EE Patterns</NAME>
            <SUBJECT>Design Patterns</SUBJECT>
            <AUTHORS>
                <AUTHOR>Aka Tuna</AUTHOR>
            </AUTHORS>
        </BOOK>
    </BOOKS>
    <DVDS>
        <DVD id=”123456″>
            <NAME>Music DVD</NAME>
            <CONTENT>Music</CONTENT>
            <AUTHORS>
                <AUTHOR>James Gosling</AUTHOR>
                <AUTHOR>Bill Gates</AUTHOR>
            </AUTHORS>
        </DVD>
        <DVD id=”3213324″>
            <NAME>Natural Science</NAME>
            <CONTENT>Science</CONTENT>
            <AUTHORS>
                <AUTHOR>John Green</AUTHOR>
                <AUTHOR>Bill Gates</AUTHOR>
            </AUTHORS>
        </DVD>
        <DVD id=”4353534″>
            <NAME>Rally</NAME>
            <CONTENT>Race</CONTENT>
            <AUTHORS>
                <AUTHOR>Tuna</AUTHOR>
            </AUTHORS>
        </DVD>
    </DVDS>
</LIBRARY>
‘));

–you can get all DVDs in the library with the following query
SELECT ID_COLUMN, EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD‘)
FROM LIBRARY

–result will be

<DVD id=”123456″>
    <NAME>Music DVD</NAME>
    <CONTENT>Music</CONTENT>
    <AUTHORS>
        <AUTHOR>James Gosling</AUTHOR>
        <AUTHOR>Bill Gates</AUTHOR>
    </AUTHORS>
</DVD>
<DVD id=”3213324″>
    <NAME>Natural Science</NAME>
    <CONTENT>Science</CONTENT>
    <AUTHORS>
        <AUTHOR>John Green</AUTHOR>
        <AUTHOR>Bill Gates</AUTHOR>
    </AUTHORS>
</DVD>
<DVD id=”4353534″>
    <NAME>Rally</NAME>
    <CONTENT>Race</CONTENT>
    <AUTHORS>
        <AUTHOR>Tuna</AUTHOR>
    </AUTHORS>
</DVD>

–or you can get a specific DVD with the following query DVD having id –> 4353534
SELECT ID_COLUMN, EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=4353534]‘)
FROM LIBRARY

— the result is
<DVD id=”4353534″>
    <NAME>Rally</NAME>
    <CONTENT>Race</CONTENT>
    <AUTHORS>
        <AUTHOR>Tuna</AUTHOR>
    </AUTHORS>
</DVD>

You can also use the EXTRACTVALUE function for getting only the value inside XML tags
If you want to get value (name) for the DVD having id number –> 3213324

SELECT ID_COLUMN, EXTRACTVALUE(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]/NAME‘)
FROM LIBRARY

— the result is
Natural Science

And if you want to get XML tags for the above operation use EXTRACT instead of using EXTRACTVALUE

SELECT ID_COLUMN, EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]/NAME‘)
FROM LIBRARY

— the result is
<NAME>Natural Science</NAME>

You can also use EXTRACTVALUE in the WHERE clause of a SQL
like the following query

SELECT EXTRACT(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]‘) FROM LIBRARY
WHERE EXTRACTVALUE(XML_DATA_COLUMN, ‘/LIBRARY/DVDS/DVD[@id=3213324]/NAME‘) = ‘Natural Science’;

— the result is
<DVD id=”3213324″>
    <NAME>Natural Science</NAME>
    <CONTENT>Science</CONTENT>
    <AUTHORS>
        <AUTHOR>John Green</AUTHOR>
        <AUTHOR>Bill Gates</AUTHOR>
    </AUTHORS>
</DVD>