Oracle Extract ExistsNode Usage with Examples Part 4

We created a simple XML in our previous post to explain the power of using XPath expressions in EXTRACT and EXISTSNODE functions in Oracle. In this post, we will present some more powerful uses of XPATH inside Extract and Existsnode functions in Oracle. As before the XML we will be using for the examples is as 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>

10) Consider a scenario where you do not know the child tag names of ‘Notebook’ element and would like to select all the child tags of Notebook. This can be accomplished by using wildcards in the Extract function. An example of such an usage is provided below Query: SELECT EXTRACT(ORDERDATA,'/Laptop/Notebook/*') from orders Output: <Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title>                 <Price>450</Price>                 <Title Brand="Sony">Sony VAIO VPCEE45FX/WI 15.5 Laptop</Title>                 <Price>800</Price>                 <Title Brand="HP">HP Pavilion dv7-4165dx 17.3 Notebook PC</Title>                 <Price>700</Price>                 <Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title>                 <Price>1200</Price>

11) All of our Title tag contains “Brand” attribute. For this example, I’m knocking out the “Brand” attribute from the Toshiba Notebook model. The objective for us would be to pick up all Title tags that have any attribute. The SQL to accomplish this is provided below Query: SELECT EXTRACT(ORDERDATA,'/Laptop/Notebook/Title[@*]') from orders /* Pick all Title tags that have any attribute */ 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> /* Note: Toshiba brand does not show up as the Title tag for that does not contain any attribute*/

12) For this case, assume that the “Notebook” element contains another tag, “EnergyRating”. Now, you want to execute a SQL that will select the Title and Price tags only but not the “EnergyRating” tag. So, your Extract function needs to support an AND condition so that you can pick up these two tags alone. The SQL example for the Extract function in this case is provided below Query : SELECT EXTRACT(ORDERDATA,'/Laptop/Notebook/Title | /Laptop/Notebook/Price') FROM ORDERS /* | => AND condition */ Output:  /* Note: Only Title and Price tags are displayed in the output */ <Title Brand="HP">HP G62X-400 LD644AAR 15.6</Title><Price>450</Price><Title Brand="Sony">Sony VAIO VPCEE45FX/WI 15.5 Laptop</Title><Price>800</Price><Title Brand="HP">HP Pavilion dv7-4165dx 17.3 Notebook PC</Title><Price>700</Price><Title Brand="Toshiba">Toshiba Portege R700-S1312 13.3 Notebook</Title><Price>1200</Price>

More EXTRACT and EXISTSNODE example to follow in the upcoming blog post..