CODEWITHSUNDEEP
xpathxquery
toitnoice
toitnoice

Reputation: 11

XPath for xquery to apply where clause on each element

I have an xml document of the following format

<MyDoc>
   <People>
     <Person>
        <Id id = 1>
        <Ownerships>
           <Ownership>
               <Owns companyId = 2/>
               <SharesOwned> 200 </SharesOwned>
          </Ownership>
          <Ownership>
               <Owns companyId = 3/>
               <SharesOwned> 100 </SharesOwned>
          </Ownership> 
     <Ownerships>
   </Person> 
   <Person>
        <Id id = 2>
        <Ownerships>
           <Ownership>
               <Owns companyId = 4/>
               <SharesOwned> 400 </SharesOwned>
          </Ownership>
          <Ownership>
               <Owns companyId = 3/>
               <SharesOwned> 20 </SharesOwned>
          </Ownership> 
     <Ownerships>
   </Person> 
 </People>
</MyDoc>

For each person I want to query the ownership where shares owned is > 150, for this I wrote the following query

for $person in doc('test.xml')//People/Person
let $ownership := $person/Ownerships/Ownership
where $ownership/SharesOwned > 150
return $ownership

After this, I expected that the query would return only ownership with company id as 2 for person 1 and company id of 4 for person 2, but it is returning all the 4 ownerships.

Is there some problem with the document structure or how should I write the query to get the desired result.

EDIT 1: If I want my expected output to be '''

<People>
     <Person>
        <Id id = 1>
        <Ownerships>
           <Ownership>
               <Owns companyId = 2/>
               <SharesOwned> 200 </SharesOwned>
          </Ownership>
    <Ownerships>
   </Person> 
   <Person>
        <Id id = 2>
        <Ownerships>
           <Ownership>
               <Owns companyId = 4/>
               <SharesOwned> 400 </SharesOwned>
          </Ownership>
     <Ownerships>
   </Person> 
 </People>

''' What should the query look like?

Upvotes: 0

Views: 207

Answers (2)

Michael Kay
Michael Kay

Reputation: 163342

In your query

for $person in doc('test.xml')//People/Person
let $ownership := $person/Ownerships/Ownership
where $ownership/SharesOwned > 150
return $ownership

your mistake is to use let in the second clause, rather than for. This binds the variable $ownership to the set of ownerships for a given person, and the where clause selects that $ownership if any of the items in this set satisfy the predicate.

Personally, I find the XPath formulation of queries like this much simpler and more readable (and of course it's also available in XQuery):

doc('test.xml')//Ownership[SharesOwned > 150]

As regards your edit1, you're now constructing a tree that is a modified version of the original. This is much easier to do in XSLT than in XQuery (but of course if you're running it against a database then XQuery might be all you've got). In XSLT 3.0 it's:

<xsl:transform version="3.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:mode on-no-match="shallow-copy"/>
  <xsl:template match="Ownership[SharesOwned le 150]"/>
</xsl:transform>

In XQuery this is easiest to achieve using XQuery Update, if that's available to you: the syntax is

delete nodes //Ownership[SharesOwned le 150]

Upvotes: 4

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your xpath. If you want to get the Ownerships/Ownership.

//MyDoc/People/Person/Ownerships/Ownership[SharesOwned > 150]

Upvotes: 0

Related Questions