Oracle XMLQuery on XMLTYPE Column Part 6

In part 5 of this series, we discussed some basic examples that showed how to query data from XMLTYPE column using XMLQuery function in Oracle. In this post, let us discuss some more advanced examples which will explain how to pull data from XMLTYPE column. We will be using the same tables as provided in part 5, so make sure that you have the table in place before you try the examples.

The objective now would be to list all Toshiba models with Brand, Model and Type information. The XQuery used inside the XMLQuery needs to be slightly altered for this as shown below

SELECT * FROM ( SELECT TVID,        XMLQuery('for $i in /TV           where  $i/BRAND eq  "Toshiba"  /* Pull only those records where TV brand equals Toshiba */           return <TVSPECS>                    <TV Brand="{$i/BRAND}"/>                    <TV MODEL="{$i/MODEL}"/>                    <TV TYPE="{$i/TYPE}"/>                  </TVSPECS>'          PASSING TVDESCR RETURNING CONTENT).getstringval() AS "TVDETAILS" FROM LEDTVS) WHERE TVDETAILS IS NOT NULL

The output of this SQL is shown below

1    <TVSPECS><TV Brand="Toshiba"></TV><TV MODEL="26SL400"></TV><TV TYPE="LED HDTV"></TV></TVSPECS> 4    <TVSPECS><TV Brand="Toshiba"></TV><TV MODEL="55HT1U"></TV><TV TYPE="LCD HDTV"></TV></TVSPECS>

Now, let us try to add another condition. If the size of the TV is greater than 40 inches for a Toshiba model, then we have to bring in an additional tag that states “BIG”. Otherwise, the tag value should read “SMALL”. We need to apply some kind of DECODE / IF THEN logic in XMLQuery. Let us see how to do this. The SQL is shown below

SELECT * FROM ( SELECT TVID,        XMLQuery('for $i in /TV           where  $i/BRAND eq  "Toshiba"           return <TVSPECS>                    <TV Brand="{$i/BRAND}"/>                    <TV MODEL="{$i/MODEL}"/>                    <TV SIZE="{if ($i/SIZE > "40") then "BIG" else "SMALL"}"/>                    <TV TYPE="{$i/TYPE}"/>                  </TVSPECS>'          PASSING TVDESCR RETURNING CONTENT).getstringval() AS "TVDETAILS" FROM LEDTVS) WHERE TVDETAILS IS NOT NULL

In the highlighted section of the SQL above, we have added an IF condition, that checks the SIZE of the TV to be greater than 40 inches or not. If it is greater than 40 inches it sets the value of SIZE attribute to BIG, else it sets the value of SIZE attribute to SMALL. The output of this SQL is shown below

1    <TVSPECS><TV Brand="Toshiba"></TV><TV MODEL="26SL400"></TV><TV SIZE="SMALL"></TV><TV TYPE="LED HDTV"></TV></TVSPECS> 4    <TVSPECS><TV Brand="Toshiba"></TV><TV MODEL="55HT1U"></TV><TV SIZE="BIG"></TV><TV TYPE="LCD HDTV"></TV></TVSPECS>

How to specify more than one condition in XQuery? To do this, you can just add an and condition. As an example, if we are after only Toshiba LED HDTV models, then we can change the SQL query as shown below

SELECT * FROM ( SELECT TVID,        XMLQuery('for $i in /TV           where  $i/BRAND eq  "Toshiba"           and $i/TYPE eq "LED HDTV"           return <TVSPECS>                    <TV Brand="{$i/BRAND}"/>                    <TV MODEL="{$i/MODEL}"/>                    <TV SIZE="{if ($i/SIZE > "40") then "BIG" else "SMALL"}"/>                    <TV TYPE="{$i/TYPE}"/>                  </TVSPECS>'          PASSING TVDESCR RETURNING CONTENT).getstringval() AS "TVDETAILS" FROM LEDTVS) WHERE TVDETAILS IS NOT NULL

The output for this query will include only row number 1 shown earlier. In the next post, we will discuss how to apply XMLTABLE to XMLTYPE column and compare the output with the output we have got in this post.