CODEWITHSUNDEEP
xmlxquery
ravz
ravz

Reputation: 23

XQuery how to filter nodes & its child nodes

How to select only top 2 child nodes from a node that is being looped in 'for'

e.g. I have this xml, i need to select Person with Qualified 'yes' and i only want his top 2 associations. My output should be same XML structure with filtered nodes. I know this is simple task with XSLT but just wondering if there is a simple way with XQuery. 

    <?xml version="1.0" encoding="UTF-8"?>
<Persons>
    <Person>
        <Name>Sam</Name>
        <DOB>12-2-1981</DOB>
        <Qualified>Yes</Qualified>
        <Assosiation>
            <Code>1</Code>
            <Descreption>Assosiatoin1</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>2</Code>
            <Descreption>Assosiatoin2</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>3</Code>
            <Descreption>Assosiatoin3</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>4</Code>
            <Descreption>Assosiatoin4</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>5</Code>
            <Descreption>Assosiatoin5</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>6</Code>
            <Descreption>Assosiatoin6</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>7</Code>
            <Descreption>Assosiatoin7</Descreption>
        </Assosiation>
        <DynamicElement>Unkown</DynamicElement>
    </Person>
    <Person>
        <Name>James</Name>
        <DOB>12-2-1975</DOB>
        <Qualified>No</Qualified>
        <Assosiation>
            <Code>1</Code>
            <Descreption>Assosiatoin1</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>2</Code>
            <Descreption>Assosiatoin2</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>3</Code>
            <Descreption>Assosiatoin3</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>4</Code>
            <Descreption>Assosiatoin7</Descreption>
        </Assosiation>
        <DynamicElement>Unkown</DynamicElement>
    </Person>
    <Person>
        <Name>Jon</Name>
        <DOB>12-2-1983</DOB>
        <Qualified>Yes</Qualified>
        <Assosiation>
            <Code>1</Code>
            <Descreption>Assosiatoin1</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>2</Code>
            <Descreption>Assosiatoin2</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>3</Code>
            <Descreption>Assosiatoin3</Descreption>
        </Assosiation>
        <Assosiation>
            <Code>4</Code>
            <Descreption>Assosiatoin7</Descreption>
        </Assosiation>
        <DynamicElement>Unkown</DynamicElement>
    </Person>
</Persons>

Upvotes: 0

Views: 1735

Answers (2)

Jens Erat
Jens Erat

Reputation: 38732

A non-XQuery Update solution:

element Persons {
  for $person in $xml/Persons/Person
  where $person/Qualified = 'Yes'
  return element Person {
    $person/Name,
    $person/DOB,
    $person/Qualified,
    $person/Assosiation[position() = (1,2)],
    $person/DynamicElement
  }
}

If you want to be less specific regarding the names of the other elements:

element Persons {
  for $person in $xml/Persons/Person
  where $person/Qualified = 'Yes'
  return element Person {
    for $element in $person/*
    return
      $element[
        local-name($element) != 'Assosiation' or
        count($element/preceding-sibling::Assosiation) < 2
      ]
  }
}

Upvotes: 2

dirkk
dirkk

Reputation: 6218

Since Oracle 11.2.0.3.0 Oracle also supports XQuery Update, which is the correct tool to use in this case.

XQuery itself does not manipulate XML data, hence you would need to reconstruct the complete tree and just leave out the unwanted nodes. Using XQuery Update you can modify XML and thus remove only the unwanted nodes:

copy $c := .
modify
  for $p in $c//Person[Qualified = "Yes"][position() > 2]
  return delete node $p
return $c

However, in your example data there are only two <Person /> elements with Qualified set to Yes, so nothing will be removed. If you meant to say you want the two first elements, but just the ones with Qualified set to Yes, you would have to exchange the predicates, i.e. $c//Person[position() > 2][Qualified = "Yes"]

Upvotes: 0

Related Questions