Today I’m going to be looking at the XML SQL functions, such as XMLElement and XMLAttributes. First things first though: if you want to experiment with functions that return XMLType values, you’ll need a client tool that has certain abilities. To display XMLTypes without any conversion you will need SQL Developer 1.1 or a 10g version of SQL*Plus. Failing that, most client tools have the ability to display CLOBs, and so you can call the getClobVal() member function to convert the XMLType to a CLOB.

The most fundamental XML SQL function is XMLElement; this function is used to return either a simple or complex XML Element structure.

This example returns a simple XML Element: Remember: the .getClobVal() can be omitted if your client tool supports it.

SELECT  XMLElement("DATE_OF_BIRTH", date_of_birth).getClobVal()
FROM    employees
WHERE   person_id = 455;

This will return:

 <DATE_OF_BIRTH>27-SEP-61</DATE_OF_BIRTH>

Note the rather strange syntax of the function - the first parameter is an “identifier” which needs to be in double-quotes. This can be quite confusing when you’re normally used to using single-quotes. The second parameter can be a normal scalar value (as in our example), or it can be another XMLType. This is used to build complex XML structures, as shown in the next example:

SELECT  XMLElement("EMPLOYEE"
          , XMLElement("FIRST_NAME", first_name)
          , XMLElement("LAST_NAME", last_name)
          , XMLElement("DATE_OF_BIRTH", date_of_birth)
          ).getClobVal()
FROM    employees
WHERE   person_id = 455;

This returns the following:

 <EMPLOYEE><FIRST_NAME>Brian</FIRST_NAME><LAST_NAME>Crook</LAST_NAME><DATE_OF_BIRTH>27-SEP-61</DATE_OF_BIRTH></EMPLOYEE>

In XML an Element can have Attributes associated with it. Attributes are often used for “key” information; in our example we’re going to add the Person ID as an attribute of the EMPLOYEE element:

SELECT  XMLElement("EMPLOYEE"
          , XMLAttributes(person_id AS “PERSON_ID”)
          , XMLElement(”FIRST_NAME”, first_name)
          , XMLElement(”LAST_NAME”, last_name)
          , XMLElement(”DATE_OF_BIRTH”, date_of_birth)
          ).getClobVal()
FROM    employees
WHERE   person_id = 455;

This extra function alters the returned XML to:

<EMPLOYEE PERSON_ID=”455″><FIRST_NAME>Brian</FIRST_NAME>
<LAST_NAME>Crook</LAST_NAME><DATE_OF_BIRTH>27-SEP-61</DATE_OF_BIRTH></EMPLOYEE>

XMLAttributes must be the first function call within an XMLElement and it can accept as many “expression/identifier” pairs as you care to throw at it (separated by commas). Notice that the method of passing in identifiers is different to XMLElement, using the AS clause instead.

Now, if we alter our SQL so that we fetch four Employee records, we will get four separate CLOBs. This is where we can use the XMLAgg group function to aggregate the four rows into a single XMLType instance:

SELECT  XMLElement("EMPLOYEES"
          , XMLAgg
            ( XMLElement(”EMPLOYEE”
              , XMLAttributes(person_id AS “PERSON_ID”)
              , XMLElement(”FIRST_NAME”, first_name)
              , XMLElement(”LAST_NAME”, last_name)
              , XMLElement(”DATE_OF_BIRTH”, date_of_birth)
              )
            )
          ).getClobVal()
FROM    employees
WHERE   person_id IN (455,461,462,466);

This will give us:

<EMPLOYEES>
  <EMPLOYEE PERSON_ID=”455″><FIRST_NAME>Brian</FIRST_NAME>
    <LAST_NAME>Crook</LAST_NAME><DATE_OF_BIRTH>27-SEP-61</DATE_OF_BIRTH></EMPLOYEE>
  <EMPLOYEE PERSON_ID=”461″><FIRST_NAME>Ruwan</FIRST_NAME>
    <LAST_NAME>De Silva</LAST_NAME><DATE_OF_BIRTH>12-JUN-63</DATE_OF_BIRTH></EMPLOYEE>
  <EMPLOYEE PERSON_ID=”462″><FIRST_NAME>Brian</FIRST_NAME>
    <LAST_NAME>Dean</LAST_NAME><DATE_OF_BIRTH>12-SEP-65</DATE_OF_BIRTH></EMPLOYEE>
  <EMPLOYEE PERSON_ID=”466″><FIRST_NAME>Jamie</FIRST_NAME>
    <LAST_NAME>Doyle</LAST_NAME><DATE_OF_BIRTH>14-MAR-65</DATE_OF_BIRTH></EMPLOYEE>
</EMPLOYEES>

I have added some formatting to the above example output to assist in reading; in actuality the XML is one long string. If you want the XML “pretty-printed” you can use another one of the XMLType member functions: extract(). Normally this function is passed an XPath construct in order to extract an element or group of elements from an XMLType instance. A side effect of this extraction process is that the XML is pretty-printed. I will look at XPath in more detail in a future blog.

In the final example we pass in an XPath construct that selects all of the elements, thus pretty-printing the complete XML:

SELECT  XMLElement("EMPLOYEES"
          , XMLAgg
            ( XMLElement("EMPLOYEE"
              , XMLAttributes(person_id AS "PERSON_ID")
              , XMLElement("FIRST_NAME", first_name)
              , XMLElement("LAST_NAME", last_name)
              , XMLElement("DATE_OF_BIRTH", date_of_birth)
              )
            )
          ).extract(’/*’).getClobVal()
FROM    employees
WHERE   person_id IN (455,461,462,466);

Which returns the following which is much easier to read:

<EMPLOYEES>
   <EMPLOYEE PERSON_ID=”455″>
      <FIRST_NAME>Brian</FIRST_NAME>
      <LAST_NAME>Crook</LAST_NAME>
      <DATE_OF_BIRTH>27-SEP-61</DATE_OF_BIRTH>
   </EMPLOYEE>
   <EMPLOYEE PERSON_ID=”461″>
      <FIRST_NAME>Ruwan</FIRST_NAME>
      <LAST_NAME>De Silva</LAST_NAME>
      <DATE_OF_BIRTH>12-JUN-63</DATE_OF_BIRTH>
   </EMPLOYEE>
   <EMPLOYEE PERSON_ID=”462″>
      <FIRST_NAME>Brian</FIRST_NAME>
      <LAST_NAME>Dean</LAST_NAME>
      <DATE_OF_BIRTH>12-SEP-65</DATE_OF_BIRTH>
   </EMPLOYEE>
   <EMPLOYEE PERSON_ID=”466″>
      <FIRST_NAME>Jamie</FIRST_NAME>
      <LAST_NAME>Doyle</LAST_NAME>
      <DATE_OF_BIRTH>14-MAR-65</DATE_OF_BIRTH>
   </EMPLOYEE>
</EMPLOYEES>

That covers the basic XML SQL functions. We’ve covered XMLElement, XMLAttributes and XMLAgg, and we’ve looked at a couple of the XMLType member functions. Other XML SQL functions that you might find useful are:

• XMLRoot adds the XML root information (or prolog) to the start of the XML 
• XMLForest accepts one or more value expressions and returns a corresponding set of XML elements. If any of the expressions evaluate to NULL, the element is omitted from the XML fragment
• XMLConcat concatenates two or more XMLType instances
• XMLCData creates a CDATA section that can be used as the content of an XML element. CDATA sections can contain any character data.

This entry was posted on Wednesday, February 14th, 2007 at 11:45 pm and is filed under - Global Oracle Contractors Network. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.