Creating tree formed XML from flat XML with Xquery -

i have xml in following format:

<data>    <row>       <id>1</id>       <parent_id/>    </row>     <row>       <id>2</id>       <parent_id>1</parent_id>    </row>     <row>       <id>3</id>       <parent_id>1</parent_id>    </row>     <row>       <id>4</id>       <parent_id>5</parent_id>    </row>     <row>       <id>5</id>       <parent_id/>    </row>    <row>       <id>6</id>       <parent_id>2</parent_id>    </row>    <row>       <id>7</id>       <parent_id>4</parent_id>    </row> </data> 

i'm trying turn this:

<data>   <row>     <id>1</id>     <children>       <row>          <id>2</id>          <parent_id>1</parent_id>          <children>             <id>6</id>             <parent_id>2</parent_id>             <children/>          </children>       </row>       <row>          <id>3</id>          <parent_id>1</parent_id>          <children/>       </row>     </children>     <parent_id/>   </row>   <row>      <id>5</id>      <parent_id/>      <children>        <row>                    <id>4</id>           <parent_id>5</parent_id>           <children>              <row>               <id>7</id>               <parent_id>4</parent_id>               <children/>              </row>           </children>        </row>      </children>   </row> </data> 

i sort flat data parent ids multiple trees if there multiple root nodes (no parents). following children recursively added <children> element of parent.

i'm pretty new xquery use in how approach kind of recursion. i've managed produce root , second level how should recurse , take account every child level have multiple paths go through? bonus i'd interested in how other way around: starting leafs , adding similar structure inside parent element there on.

my current code returns root element , children:

declare function local:root() {    let $root := doc("source.xml")/result/rows/row[parent_object_id = '']    return $root };  declare function local:recurse($input) { let    $children := doc("source.xml")/result/rows/row[parent_object_id =   $input/object_id]    return $children };  <result>    <object_id>{local:root()/object_id/text()}</object_id>    <parent_object_id>{local:root()/parent_object_id/text()}   </parent_object_id>    <children>{local:recurse(local:root())}</children> </result> 

you can use recursive function that:

declare function local:nest-children($data, $id) {   <row>{     $id,     <children>{       $child in $data/row[parent_id = $id]       return local:nest-children($data, $child/id)     }</children>   }</row> };  <data>{   $outer in $data/row[empty(parent_id/text())]   return local:nest-children($data, $outer/id) }</data> 

this returns following result:

<data>   <row>     <id>1</id>     <children>       <row>         <id>2</id>         <children>           <row>             <id>6</id>             <children/>           </row>         </children>       </row>       <row>         <id>3</id>         <children/>       </row>     </children>   </row>   <row>     <id>5</id>     <children>       <row>         <id>4</id>         <children>           <row>             <id>7</id>             <children/>           </row>         </children>       </row>     </children>   </row> </data>