XmlTable, extract and xmlupdate, XQuery In a Nutshell

Early this week i was working on xml data in oracle, an Avenue still not much explored by me . I was trying to update an xml data from one of our table. Dealing with xml in oracle used to get me on nerves. This post should be a help for those struggling to implement sql on xml in oracle database.

In one of my previous post i talked about JASON_TABLE. This post would include XML_TABLES, EXTRACT,EXTRACT_VALUE and UPDATEXML AND Xquery

Let' s Start with our example, and create some sample data.

CREATE TABLE EMPL
(
   id     NUMBER,
   data   XMLTYPE
);

INSERT INTO EMPL
     VALUES (1, xmltype ('<Employees>
    <Employee emplid="1111" type="admin">
        <firstname>John</firstname>
        <lastname>Watson</lastname>
        <age>30</age>
        <email>johnwatson@sh.com</email>
    </Employee>
    <Employee emplid="2222" type="admin">
        <firstname>Sherlock</firstname>
        <lastname>Homes</lastname>
        <age>32</age>
        <email>sherlock@sh.com</email>
    </Employee>
    <Employee emplid="3333" type="user">
        <firstname>Jim</firstname>
        <lastname>Moriarty</lastname>
        <age>52</age>
        <email>jim@sh.com</email>
    </Employee>
    <Employee emplid="4444" type="user">
        <firstname>Mycroft</firstname>
        <lastname>Holmes</lastname>
        <age>41</age>
        <email>mycroft@sh.com</email>
    </Employee>
</Employees>'));

We can Use Xml Table function to retrieve information from this xml.

SELECT t.id, x.*
     FROM empl t,
          XMLTABLE ('/Employees/Employee'
                    PASSING t.data
                    COLUMNS firstname VARCHAR2(30) PATH 'firstname', 
                            lastname VARCHAR2(30) PATH 'lastname') x
    WHERE t.id = 1;

-----------------------------------------------------------------------------------------------------------------------
	1	John	Watson
	1	Sherlock	Homes
	1	Jim	Moriarty
	1	Mycroft	Holmes

This was bit easy, but what if the requirement is to find some specific records from the xml, for eg. Reads the name of the employees who are admin.

   SELECT t.id, x.*
     FROM empl t,
          XMLTABLE ('/Employees/Employee[@type="admin"]'
                    PASSING t.data
                    COLUMNS firstname VARCHAR2(30) PATH 'firstname', 
                            lastname VARCHAR2(30) PATH 'lastname') x
    WHERE t.id = 1;

-----------------------------------------------------------------------
	1	John	Watson
	1	Sherlock	Homes

Read the employee name less than 40.

  SELECT t.id, x.*
     FROM empl t,
          XMLTABLE ('/Employees/Employee[age<40]'
                    PASSING t.data
                    COLUMNS firstname VARCHAR2(30) PATH 'firstname', 
                            lastname VARCHAR2(30) PATH 'lastname',
                            age VARCHAR2(30) PATH 'age') x
    WHERE t.id = 1;
-----------------------------------------------------------------------
	1	John	Watson	30
	1	Sherlock	Homes	32

Before we move on to xmlupdate feature in oracle lets see an example of extract in xml

 SELECT COLUMN_VALUE
    FROM empl x,
     table(xmlsequence(extract(x.data, '/Employees/Employee/firstname'))) d;

Now try and update the first name

    UPDATE empl SET DATA =
   UPDATEXML(DATA,
   '/Employees/Employee/firstname/text()','JOHN')

Try to do specific update very similar to specific select.

Since 12c recommended way to manipulate xml query is XQuery Update Facility.

Let me show this with an example.

create table so61_t(
 id number
,xml xmltype
);

insert into so61_t values(1, '<?xml version="1.0" encoding="WINDOWS-1252"?>
<View>
    <ReportValues>
        <SalaryValue variable="HR" value="999"/>
        <SalaryValue variable="floor" value="20"/>
    </ReportValues>
</View>');

insert into so61_t values(2, '<?xml version="1.0" encoding="WINDOWS-1252"?>
<View>
    <ReportValues>
        <SalaryValue variable="HR" value="998"/>
        <SalaryValue variable="floor" value="19"/>
    </ReportValues>
</View>');

update so61_t set xml =
xmlquery(
'copy $t := $x modify(
  (for $i in $t/View/ReportValues/SalaryValue[@variable="HR"]/@value
   return replace value of node $i with ''666'')
 ,(for $i in $t/View/ReportValues/SalaryValue[@variable="floor"]/@value
   return replace value of node $i with ''SALES'')
) return $t'
passing xml as "x" returning content
)
where id = 1
;

select  xmlserialize(content xml as varchar2(200)) as xml from so61_t;
---------------------------------------------------------------------
    
<View>
      <ReportValues>
        <SalaryValue variable="HR" value="666"/>
        <SalaryValue variable="floor" value="SALES"/>
      </ReportValues>
    </View>
   <?xml version="1.0" encoding="UTF-8"?>
    <View>
      <ReportValues>
        <SalaryValue variable="HR" value="998"/>
        <SalaryValue variable="floor" value="19"/>
      </ReportValues>
    </View>

There is much to cover in oracle xml. We have a whole API to manage it in oracle dbms_xmldom. XML are generally used in webservice application to allow the data flow between two systems or integration projects. I will try to post some more content on xml in future.