Oracle ExistsNode Extract Function Examples - Part 3

While discussing UpdateXML with ExistsNode in our previous blog post, we got introduced to ExistsNode and in this post, we will see how to make use of this function to the fullest with some very good examples. To cover off all the examples of using ExistsNode in a comprehensive way, we need a good XML to make use of. For explaining the usage of ExistsNode function, we will be using the XML as shown below:

<Laptop>
            <Notebook>
                        <Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>
                        <Price>450</Price>
            </Notebook>
            <Notebook>
                        <Title Brand="Sony">Sony VAIO VPCEE45FX/WI 15.5 Laptop</Title>
                        <Price>800</Price>
            </Notebook>
            <Notebook>
                        <Title Brand="HP">HP Pavilion dv7-4165dx 17.3 Notebook PC</Title>
                        <Price>700</Price>
            </Notebook>
            <Notebook>
                        <Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title>
                        <Price>1200</Price>
            </Notebook>
</Laptop>

As we run through this post, for some cases we will use EXTRACT as an example. With this XML, let us answer some How Tos..(make sure that you put this XML in a XMLTYPE column to try out the examples below)

1) How to select the first "Notebook" node using Extract?

Query: SELECT EXTRACT(A1.ORDERDATA,'/Laptop/Notebook[1]') FROM ORDERS A1

Output: <Notebook><Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title><Price>450</Price></Notebook>

2) How to select the last "Notebook" node under Laptop?

Query: SELECT EXTRACT(A1.ORDERDATA,'/Laptop/Notebook[last()]') FROM ORDERS A1

Output: <Notebook><Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title><Price>1200</Price></Notebook>

3) How to select the last "Notebook" node but one under Laptop?

Query: SELECT EXTRACT(A1.ORDERDATA,'/Laptop/Notebook[last()-1]') FROM ORDERS A1

Output: <Notebook><Title Brand="HP">HP Pavilion dv7-4165dx 17.3 Notebook PC</Title><Price>700</Price></Notebook>

4) How to select the first two "Notebook" nodes under Laptop XML?

Query: SELECT EXTRACT(A1.ORDERDATA,'/Laptop/Notebook[position()<3]') FROM ORDERS A1

Output:

<Notebook><Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title><Price>450</Price></Notebook>

<Notebook><Title Brand="Sony">Sony VAIO VPCEE45FX/WI 15.5 Laptop</Title><Price>800</Price></Notebook>

5) Using Extract Function, how to select all "Title" elements that have an attribute "Brand" ?

Query: SELECT EXTRACT(A1.ORDERDATA,'//Title[@Brand]') FROM ORDERS A1

Output:

<Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>

<Title Brand="Sony">Sony VAIO VPCEE45FX/WI 15.5 Laptop</Title>

<Title Brand="HP">HP Pavilion dv7-4165dx 17.3 Notebook PC</Title>

<Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title>

6) Using Extract Function, how to select all "Title" tags where the brand is HP?

Query: SELECT EXTRACT(A1.ORDERDATA,'//Title[@Brand="HP"]') FROM ORDERS A1

Output:

<Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>

<Title Brand="HP">HP Pavilion dv7-4165dx 17.3 Notebook PC</Title>

7) Using Extract Function how to select all "Notebook" tags where price is greater than or equal to 800?

Query: SELECT EXTRACT(A1.ORDERDATA,'/Laptop/Notebook[Price>=800]') FROM ORDERS A1

Output:

<Notebook><Title Brand="Sony">Sony VAIO VPCEE45FX/WI 15.5 Laptop</Title><Price>800</Price></Notebook>

<Notebook><Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title><Price>1200</Price></Notebook>

8)  Using Extract SQL Funtion how to select only the Title tags for all Notebooks, where price is less than 800?

Query: SELECT EXTRACT(A1.ORDERDATA,'/Laptop/Notebook[Price<800]/Title') FROM ORDERS A1

Output:

<Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>

<Title Brand="HP">HP Pavilion dv7-4165dx 17.3 Notebook PC</Title>

9) Using Extract SQL Function how to select only the price tags for all Notebooks, where Brand is HP?

Query: SELECT EXTRACT(A1.ORDERDATA,'/Laptop/Notebook[Title[@Brand="HP"]]/Price') FROM ORDERS A1

Output:

<Price>450</Price>

<Price>700</Price>

As we move on in subsequent blog posts, we will provide more complex usages of Extract and ExistsNode functions in Oracle.