Skip to Main Content
  • Questions

  • Return nodes with non-null elements from XMLTYPE

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Steve.

Asked: February 01, 2017 - 4:37 pm UTC

Last updated: February 25, 2017 - 1:42 am UTC

Version: 10.2

Viewed 1000+ times

You Asked

Hi

I have an xml document that looks something like:
<books>
   <book>
     <title>To kill a mocking bird</title>
     <price>5.75</price>
     <outofstockdate>2017-01-01</outofstockdate>
   </book>
   <book>
     <title>Swallows and Amazons</title>
     <price>6.99</price>
     <outofstockdate/>
   </book>
</books>


What I need to do is return an xmltype of books that are in stock so:

<books>
 <book>
     <title>Swallows and Amazons</title>
     <price>6.99</price>
     <outofstockdate/>
   </book>
</books>



and Chris said...

So you want to find book nodes where outofstockdate is null?

Here's one way to do it using XQuery:

with xml as (
  select xmltype('<books>
   <book>
     <title>To kill a mocking bird</title>
     <price>5.75</price>
     <outofstockdate>2017-01-01</outofstockdate>
   </book>
   <book>
     <title>Swallows and Amazons</title>
     <price>6.99</price>
     <outofstockdate/>
   </book>
</books>') x from dual
)
  select xt.column_value.getStringVal()
  from   xml, xmltable(
   'for $i in /books/book where $i/outofstockdate eq ""
    return <books>{$i}</books>'
    passing x 
  ) xt;

XT.COLUMN_VALUE.GETSTRINGVAL()                                                                       
<books><book><title>Swallows and Amazons</title><price>6.99</price><outofstockdate/></book></books>


But I'm no XML expert, so there may be better ways.

You can read more about this at:

http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#ADXDB1700

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great but...

Steve Jelfs, February 23, 2017 - 8:57 am UTC

What if I needed to exclude by an attribute, for example;

<books>
   <book>
     <title genre="fiction">To kill a mocking bird</title>
     <price>5.75</price>
     <outofstockdate>2017-01-01</outofstockdate>
   </book>
   <book>
     <title>Swallows and Amazons</title>
     <price>6.99</price>
     <outofstockdate/>
   </book>
</books>


So now I need to find all books (one in this case) where there is no genre set.
Connor McDonald
February 25, 2017 - 1:42 am UTC 

SQL> with xml as (
  2    select xmltype('<books>
  3     <book>
  4       <title genre="fiction">To kill a mocking bird</title>
  5       <price>5.75</price>
  6       <outofstockdate>2017-01-01</outofstockdate>
  7     </book>
  8     <book>
  9       <title>Swallows and Amazons</title>
 10       <price>6.99</price>
 11       <outofstockdate/>
 12     </book>
 13  </books>') x from dual
 14  )
 15    select xt.column_value.getStringVal()
 16    from   xml, xmltable(
 17     'for $i in /books/book return if (not(exists($i/title[@genre]))) then <books>{$i}</books> else ()'
 18      passing x
 19    ) xt;

XT.COLUMN_VALUE.GETSTRINGVAL()
---------------------------------------------------------------------------------------------------------------
<books><book><title>Swallows and Amazons</title><price>6.99</price><outofstockdate/></book></books>


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.