[xquery-talk] solutions to an Excel XML processing problem

[John Snelson]

Something like this seems to do the job:

declare function local:expand($column as xs:integer, $max as xs:integer,
$input as element()?) as element()*
{
   if($input) then
     if($input/@i) then (
       for $i in ($column to xs:integer($input/@i) - 1) return <c/>,
       <c>{ $input/node() }</c>,
       local:expand(xs:integer($input/@i) + 1, $max,
$input/following-sibling::c[1])
     )
     else (
       $input,
       local:expand($column + 1, $max, $input/following-sibling::c[1])
     )
   else for $i in ($column to $max) return <c/>
};

let $in := <row cnt="30">
<c i="3">third</c>
<c>fouth</c>
<c>fifth</c>
<c i="10">tenth</c>
<c>eleventh</c>
<c i="20">twentieth</c>
<c>twenty-one</c>
<c>twenty-two</c>
<c>twenty-three</c>
</row>

return
   local:expand(1, xs:integer($in/@cnt), $in/c[1])

John

Daniela Florescu wrote:
> Dear all,
> 
> I am trying to find various solutions to the following XML to XML 
> transformation problem.
>  It is taken out of real world Excel spreadsheets data processing.
> 
> (Input) The data is composed of cells and some of the cells are empty 
> (no content).
> This version of the data is in compressed form, i.e. does not contain 
> the empty cells and is using the
> attribute @i to express jumps to the  next non empty cell (using the 
> position). It looks like this:
> =====================================================================
> 
> 
> <row cnt="30">
> <c i="3">third</c>
> <c>fouth</c>
> <c>fifth</c>
> <c i="10">tenth</c>
> <c>eleventh</c>
> <c i="20">twentieth</c>
> <c>twenty-one</c>
> <c>twenty-two</c>
> <c>twenty-three</c>
> </row>
> 
> 
> (Ouput) Write an XQuery to transform the XML data in the uncompressed form
> that includes the empty cells, as follows:
> =============================================================
> 
> <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 i="20">twentieth</c>
> <c>twenty-one</c>
> <c>twenty-two</c>
> <c>twenty-three</c>
>  ...
> </row>
> 
> 
> I know it can be done in a variety of ways, I am just looking for the 
> "best" (i.e. simplest) XQuery
> to achieve this purpose that people on this list can help me find.
> 
> Thanks in advance,
> Dana
> 
> _______________________________________________
> talk at xquery.com
> http://xquery.com/mailman/listinfo/talk