XMLCONCAT and XMLFOREST Oracle Tips by Laurent Schneider

 

Laurent Schneider is considered one of the top Oracle SQL experts, and he is the author of the book "Advanced SQL Programming" by Rampant TechPress.  The following is an excerpt from the book.

XMLCONCAT and XMLFOREST

XMLCONCATjoins multiple xml elements together; XMLFORESTworks similarly but accepts number, characters, dates, and XMLTYPE.

SELECT
   XMLCONCAT
   (
      XMLELEMENT(EMPNO, EMPNO),
      XMLELEMENT(ENAME, ENAME)
   ),
   XMLFOREST
   (
      EMPNO,
      ENAME
   )
FROM
   EMP
WHERE
   ENAME='SCOTT';
 

XMLCONCAT(XMLELEMENT(E  XMLELE XMLFOREST(EMPNO,ENAME)
---------------------- -----------------------------
<EMPNO>7788</EMPNO>    <EMPNO>7788</EMPNO>
<ENAME>SCOTT</ENAME>   <ENAME>SCOTT</ENAME>
XPATH

Oracle supports XPATH expressions.

-- *************************************************
-- Copyright - 2015 by Rampant TechPress
-- This script is free for non-commercial purposes
-- with no warranties.  Use at your own risk.
--
-- To license this script for a commercial purpose,
-- contact rtp@rampant.cc
-- *************************************************

-- Id     : $Id: tablexmltype.sql,v 1.2 2015/05/04 19:40:48 Laurent Exp $
-- Author : $Author: Laurent $
-- Date   : $Date: 2015/05/04 19:40:48 $
--
-- Create WORLD XML Table in current schema
--

WHENEVER SQLERROR EXIT

EXEC EXECUTE IMMEDIATE 'DROP TABLE WORLD'; EXCEPTION WHEN OTHERS THEN NULL

-- Create XML Table
CREATE TABLE
   WORLD
OF XMLTYPE;
 
INSERT INTO
   WORLD
VALUES
(
   XMLTYPE
   ('<COUNTRY ID="CH">
<NAME>Switzerland</NAME>
<CANTON_LIST>
<CANTON ID="ZH">
<NAME>Zurich</NAME>
<DETAILS>
<ENTRY>1351-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="BE">
<NAME>Bern</NAME>
<DETAILS>
<ENTRY>1353-01-01</ENTRY>
<LANGUAGE_LIST>
<LANGUAGE>German</LANGUAGE>
<LANGUAGE>French</LANGUAGE>
</LANGUAGE_LIST>
</DETAILS>
</CANTON>
<CANTON ID="LU">
<NAME>Lucerne</NAME>
<DETAILS>
<ENTRY>1332-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="UR">
<NAME>Uri</NAME>
<DETAILS>
<ENTRY>1291-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="SZ">
<NAME>Schwyz</NAME>
<DETAILS>
<ENTRY>1291-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="OW">
<NAME>Obwald</NAME>
<DETAILS>
<ENTRY>1291-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="NW">
<NAME>Nidwald</NAME>
<DETAILS>
<ENTRY>1291-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="GL">
<NAME>Glarus</NAME>
<DETAILS>
<ENTRY>1352-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="ZG">
<NAME>Zug</NAME>
<DETAILS>
<ENTRY>1352-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="FR">
<NAME>Fribourg</NAME>
<DETAILS>
<ENTRY>1481-01-01</ENTRY>
<LANGUAGE_LIST>
<LANGUAGE>French</LANGUAGE>
<LANGUAGE>German</LANGUAGE>
</LANGUAGE_LIST>
</DETAILS>
</CANTON>
<CANTON ID="SO">
<NAME>Solothurn</NAME>
<DETAILS>
<ENTRY>1481-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="BS">
<NAME>Basel-City</NAME>
<DETAILS>
<ENTRY>1501-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="BL">
<NAME>Basel-Country</NAME>
<DETAILS>
<ENTRY>1501-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="SH">
<NAME>Schaffhausen</NAME>
<DETAILS>
<ENTRY>1501-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="AR">
<NAME>AppenzellOuterRhodes</NAME>
<DETAILS>
<ENTRY>1513-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="AI">
<NAME>AppenzellInnerRhodes</NAME>
<DETAILS>
<ENTRY>1513-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="SG">
<NAME>St.Gall</NAME>
<DETAILS>
<ENTRY>1803-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="GR">
<NAME>Graubuenden</NAME>
<DETAILS>
<ENTRY>1803-01-01</ENTRY>
<LANGUAGE_LIST>
<LANGUAGE>German</LANGUAGE>
<LANGUAGE>Romansh</LANGUAGE>
<LANGUAGE>Italian</LANGUAGE>
</LANGUAGE_LIST>
</DETAILS>
</CANTON>
<CANTON ID="AG">
<NAME>Aargau</NAME>
<DETAILS>
<ENTRY>1803-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="TG">
<NAME>Thurgau</NAME>
<DETAILS>
<ENTRY>1803-01-01</ENTRY>
<LANGUAGE>German</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="TI">
<NAME>Ticino</NAME>
<DETAILS>
<ENTRY>1803-01-01</ENTRY>
<LANGUAGE_LIST>
<LANGUAGE>Italian</LANGUAGE>
<LANGUAGE>German</LANGUAGE>
</LANGUAGE_LIST>
</DETAILS>
</CANTON>
<CANTON ID="VD">
<NAME>Vaud</NAME>
<DETAILS>
<ENTRY>1803-01-01</ENTRY>
<LANGUAGE>French</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="VS">
<NAME>Valais</NAME>
<DETAILS>
<ENTRY>1815-01-01</ENTRY>
<LANGUAGE_LIST>
<LANGUAGE>French</LANGUAGE>
<LANGUAGE>German</LANGUAGE>
</LANGUAGE_LIST>
</DETAILS>
</CANTON>
<CANTON ID="NE">
<NAME>Neuchatel</NAME>
<DETAILS>
<ENTRY>1815-01-01</ENTRY>
<LANGUAGE>French</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="GE">
<NAME>Geneva</NAME>
<DETAILS>
<ENTRY>1815-01-01</ENTRY>
<LANGUAGE>French</LANGUAGE>
</DETAILS>
</CANTON>
<CANTON ID="JU">
<NAME>Jura</NAME>
<DETAILS>
<ENTRY>1979-01-01</ENTRY>
<LANGUAGE>French</LANGUAGE>
</DETAILS>
</CANTON>
</CANTON_LIST>
</COUNTRY>')
);
 

COMMIT;

The table WORLD contains one row of XMLTYPE.

The EXTRACTfunction requires two arguments: an XMLTYPE element and an XPATH string. An optional third parameter specifies the namespace. EXTRACT matches the XPATH expression to the XMLTYPE element and returns an XMLTYPE instance. The XPATH must resolve to a node or node content. The XPATH syntax is defined by the World Wide Web Consortium at http://www.w3.org/TR/xpath.

SELECT
   EXTRACT
   (
      OBJECT_VALUE,
      '/COUNTRY/CANTON_LIST/CANTON[@ID="GE" or @ID="GR"]'
   ) CANTON
FROM
   WORLD; 
CANTON
-----------------------------------
<CANTON ID="GR">
   <NAME>Graubuenden</NAME>
   <DETAILS>
      <ENTRY>1803-01-01</ENTRY>
      <LANGUAGE_LIST>
         <LANGUAGE>German</LANGUAGE>
         <LANGUAGE>Romansh</LANGUAGE>
         <LANGUAGE>Italian</LANGUAGE>
      </LANGUAGE_LIST>
   </DETAILS>
</CANTON>
<CANTON ID="GE">
   <NAME>Geneva</NAME>
   <DETAILS>
      <ENTRY>1815-01-01</ENTRY>
      <LANGUAGE>French</LANGUAGE>
   </DETAILS>
</CANTON>

OBJECT_VALUEpoints to the column of the table WORLD. The node of the cantons with the attribute ID equals to GE or GR in the canton list of the country are returned as a well-formed content.

SELECT
   EXTRACT
   (
      OBJECT_VALUE,
      '/COUNTRY/NAME/text()'
   ) COUNTRY_NAME
FROM
   WORLD;

COUNTRY_NAME
------------------
Switzerland

The text content of the name of the country is returned. This XMLTYPE is not well-formed.

SELECT
   EXTRACT
   (
      OBJECT_VALUE,
      '/COUNTRY/CANTON_LIST/CANTON[NAME="Zurich" or NAME="Zug"]/@ID
   ) CANTON
FROM
   WORLD;
CANTON
---------- 

ZHZG

The attributes of the cantons named Zurich or Zug are returned. This XMLTYPE is not well-formed.

The EXTRACTVALUEfunction returns a scalar.

SELECT
   EXTRACTVALUE
   (
      OBJECT_VALUE,
      '/COUNTRY/CANTON_LIST/CANTON[@ID="NE"]//LANGUAGE/text()'
   ) LANGUAGE
FROM
   WORLD
; 

LANGUAGE
----------
French

For the canton NE, the language is returned as a VARCHAR2 string. Note the // between the canton and the language. // is typically slower because it implies a search in the node. The LANGUAGE is not necessarily located directly under CANTON.

EXISTSNODEcheck the existence of an XPATH expression:

SELECT
   DECODE
   (
      EXISTSNODE
      (
         OBJECT_VALUE,
         '/COUNTRY/CANTON_LIST/CANTON[NAME="Zurich"]'
      ),
      1, 'TRUE',
      0, 'FALSE'
   ) EXISTSNODE
FROM
   WORLD; 

EXISTSNODE
----------
TRUE

The value of 1 reveals that there is at least one canton with a name of Zurich that exists in the document.