Oracle XMLFOREST and XMLCOLATTVAL Example

I got introduced to two functions that are slightly similar in behaviour. XMLFOREST and XMLCOLATTIVAL..To understand the finer difference between these these two, consider the following example;

So, let us start with a table and some insert statements..

CREATE TABLE GPS_MODEL
(
MODEL_NUMBER VARCHAR2(20),
GPS_BRAND VARCHAR2(50),
GPS_PRICE VARCHAR2(30)
)

INSERT INTO GPS_MODEL VALUES('Nuvi 1450T','Garmin','$289')
INSERT INTO GPS_MODEL VALUES('Nuvi 3760','Garmin','$399')
INSERT INTO GPS_MODEL VALUES('Nuvi 1350','Garmin','$198')
INSERT INTO GPS_MODEL VALUES('MY60T','Navman','$198')
INSERT INTO GPS_MODEL VALUES('EZY30','Navman','$129')
INSERT INTO GPS_MODEL VALUES('GO1000','TOMTOM','$338')
INSERT INTO GPS_MODEL VALUES('XXL 540','TOMTOM','$149')
INSERT INTO GPS_MODEL VALUES('GO1050','TOMTOM','$449')

We are ready to go now.The objective for us would be to to construct an XML for TOMTOM GO1050 brand, using XMLFOREST and XMLCOLATTVAL..In doing so, we will be able to understand the way both these functions work and the difference in output.

We run the following SQL Query to see the output in the case of XMLCOLATIVAL

SELECT XMLELEMENT("GPS",
                  XMLCOLATTVAL(E.MODEL_NUMBER, E.GPS_BRAND, E.GPS_PRICE)) "GPS BRAND"
  FROM GPS_MODEL E
 WHERE MODEL_NUMBER = 'MY60T'

The output is

<GPS>

<column name="MODEL_NUMBER">MY60T</column>

<column name="GPS_BRAND">Navman</column>

<column name="GPS_PRICE">$198</column>

</GPS>

As you see from the output, each nested element (selected inside XMLCOLATTVAL) has a "name" attribute with the field name as the attribute value. The value of each tag is the actual value stored for the select condition. Oracle automatically gives each XML Fragment with the tag name as "Column". What happens with XMLFOREST is something interesting;

SELECT XMLELEMENT("GPS",
                  XMLFOREST(E.MODEL_NUMBER, E.GPS_BRAND, E.GPS_PRICE))
  FROM GPS_MODEL E
 WHERE MODEL_NUMBER = 'MY60T'

The output is

<GPS>

<MODEL_NUMBER>MY60T</MODEL_NUMBER>

<GPS_BRAND>Navman</GPS_BRAND>

<GPS_PRICE>$198</GPS_PRICE>

</GPS>

As you see from the output, in the case of XMLFOREST there is no attribute concept when XMLFOREST is used. The column name becomes the tag name and the column value is the tag value.

So, when to use XMLFOREST and when to use XMLCOLATTVAL? Well, it purely depends on the requirements. Just see how you want your final XML to be and choose your options accordingly..

If you don't like the tag name "GPS_BRAND" and want something as 'BRAND_NAME" you can use alias in your SQL as shown below

SELECT XMLELEMENT("GPS",
                  XMLFOREST(E.MODEL_NUMBER AS BRAND_NUMBER, E.GPS_BRAND AS BRAND_NAME, E.GPS_PRICE AS COST))
  FROM GPS_MODEL E
 WHERE MODEL_NUMBER = 'MY60T'

<GPS>

<BRAND_NUMBER>MY60T</BRAND_NUMBER>

<BRAND_NAME>Navman</BRAND_NAME>

<COST>$198</COST></GPS>

If you'd used XMLCOLATTVAL instead of XMLFOREST, the attribute value will hold the alias names, as shown below

SELECT XMLELEMENT("GPS",
                  XMLCOLATTVAL(E.MODEL_NUMBER AS BRAND_NUMBER, E.GPS_BRAND AS BRAND_NAME, E.GPS_PRICE AS COST))
  FROM GPS_MODEL E
 WHERE MODEL_NUMBER = 'MY60T'

<GPS>

<column name="BRAND_NUMBER">MY60T</column>

<column name="BRAND_NAME">Navman</column>

<column name="COST">$198</column>

</GPS>

Why did we insert so many rows into our GPS table? Some more examples will follow based on this table in the upcoming blog posts..