Hokkaido Hokkaido - 6 months ago 8
SQL Question

ORACLE SQL: How can I get the XML elements from within a column of XMLTYPE

I have an multiple xml documents which look like this:

<farm>
<name>Johns Farm</name>
<size>50 hectares</size>
<employees>20</employees>
<fruits>
<fruit>Banana</fruit>
<fruit>Apple</fruit>
<fruit>Watermelon</fruit>
</fruits>
</farm>


I have got this in an oracle database like this:

CREATE TABLE FARM_XML_TABLE
(FARM_NUMBER NUMBER NOT NULL PRIMARY KEY, FARM_XML XMLTYPE NOT NULL)
XMLTYPE COLUMN FARM_XML STORE AS BINARY XML
XMLSCHEMA
"http://myproject.com/farmschema.xsd"
ELEMENT "farm";


The various farms are stored in the
FARM_XML
column. There is about 5 of them

How can I retrieve only the name and employee elements from the
FARM_XML
column of multiple farms in the form of an XML. I want the output to be like this:

<farm>
<name>Johns Farm</name>
<employees>20</employees>
</farm>

<farm>
<name>Harrys Farm</name>
<employees>10</employees>
</farm>


I have tried doing this, but the output isn't all the xml elements that I want.

SELECT extract(FARM_XML, 'farm/name').getStringVal()
FROM FARM_XML_TABLE;

MT0 MT0
Answer

Oracle Setup:

CREATE TABLE FARM_XML_TABLE(
  FARM_NUMBER NUMBER PRIMARY KEY,
  FARM_XML XMLTYPE NOT NULL
)
XMLTYPE COLUMN FARM_XML STORE AS BINARY XML;

INSERT INTO FARM_XML_TABLE VALUES(
  1,
  XMLtype( '<farm>
    <name>Johns Farm</name>
    <size>50 hectares</size>
    <employees>20</employees>
    <fruits>
        <fruit>Banana</fruit>
        <fruit>Apple</fruit>
        <fruit>Watermelon</fruit>
    </fruits>
</farm>' )
);

Query 1 - Rebuild the XML:

SELECT FARM_NUMBER,
       XMLElement(
         "farm",
         XMLElement( "name",      EXTRACTVALUE( farm_xml, '/farm/name' ) ),
         XMLElement( "employees", EXTRACTVALUE( farm_xml, '/farm/employees' ) )
       ) AS xml
FROM   FARM_XML_TABLE;

or (updated - simpler version):

SELECT FARM_NUMBER,
       XMLElement(
         "farm",
         EXTRACT( farm_xml, '/farm/name' ),
         EXTRACT( farm_xml, '/farm/employees' )
       ) AS xml
FROM   FARM_XML_TABLE;

Query 2 - Delete non-matching elements from the existing XML:

SELECT FARM_NUMBER,
       DELETEXML(
         farm_xml,
         '/farm/*[name()!=''name''][name()!=''employees'']'
       ) AS xml
FROM   FARM_XML_TABLE;

Output:

(Both output the same)

FARM_NUMBER XML
----------- -------------------------------------------------------------
          1 <farm><name>Johns Farm</name><employees>20</employees></farm>
Comments