CODEWITHSUNDEEP
xmlxquery
Kim Lindqvist
Kim Lindqvist

Reputation: 393

Creating tree formed XML from flat XML with Xquery

I have some XML in the 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 to turn it into something like 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 would like to sort this flat data with only parent ids into multiple trees if there are multiple root nodes (no parents). All the following children would be recursively added into the <children> element of their parent.

I'm pretty new to Xquery so I could use some help in how to approach this kind of recursion. I've managed to produce the root and second level but how should I recurse this and take into account that every child level could have multiple paths to go through? As a bonus I'd also be interested in how to do this the other way around: Starting from the leafs and adding a similar structure inside a parent element from there on.

My current code that returns the root element and its 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>

Upvotes: 1

Views: 159

Answers (1)

Leo W&#246;rteler
Leo W&#246;rteler

Reputation: 4241

You can use a recursive function to do that:

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

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

This returns the 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>

Upvotes: 0

Related Questions