solutions to an Excel XML processing problem

[David Carlisle]

I just saw Mike posted a solution to this, but here's one using a
recursive function


declare function local:f ($c,$n){
if (exists($c/@i)) then
((for $x in (1 to xs:integer($c/@i - $n -1)) return (<c/>)),
<c>{$c/node()}</c>,$c/following-sibling::c[1]/local:f(.,$c/@i))
else 
(<c>{$c/node()}</c>,$c/following-sibling::c[1]/local:f(.,$n + 1))
};
for $r in row
return
<row>
{$r/@*,local:f($r/c[1],0)}
</row>

again it uses one of the attributes that is optional in xquery, if your
xquery engine doesn't support it then complain to the vendor (why on
earth these are optional, one can only guess:-)

David

$ saxon8q -s cells.xml cells.xq
<?xml version="1.0" encoding="UTF-8"?>
<row cnt="30">
   <c/>
   <c/>
   <c>third</c>
   <c>fouth</c>
   <c>fifth</c>
   <c/>
   <c/>
   <c/>
   <c/>
   <c>tenth</c>
   <c>eleventh</c>
   <c/>
   <c/>
   <c/>
   <c/>
   <c/>
   <c/>
   <c/>
   <c/>
   <c>twentieth</c>
   <c>twenty-one</c>
   <c>twenty-two</c>
   <c>twenty-three</c>
</row>