XQuery/Displaying Lists

Motivation

You have a list of items in an XML structure and you want to display a comma separated list of the values in an output string.

Method

XQuery provides the "string-join()" function that will take a sequence of items and a separator string and create and output string with the separator between each of the items. The format of the function is: string-join(nodeset, separator) where nodeset is a list of nodes and separator the string that you would like to separate the values with.

Sample Program

xquery version "1.0";

let $tags :=
<tags>
   <tag>x</tag>
   <tag>y</tag>
   <tag>z</tag>
   <tag>d</tag>
</tags>

return
<results>
   <comma-separated-values>{
      string-join($tags/tag, ',')
  }</comma-separated-values>
</results>

Output

<results>
   <comma-separated-values>x,y,z,d</comma-separated-values>
</results>

Create a CSV file from XML

We will use two "string-join()" functions, one for all rows and one for each row. We will create one large string and then use the "response:stream()" function to return the results.

xquery version "1.0";
declare option exist:serialize "method=text media-type=text/csv omit-xml-declaration=yes";

(: The newline character used as a separator between lines :)
let $nl := "
"

let $input :=
<rows>
   <row>
      <c1>Row1 Col1</c1>
      <c2>Row1 Col2</c2>
      <c3>Row1 Col3</c3>
      <c4>Row1 Col4</c4>
   </row>
   <row>
      <c1>Row2 Col1</c1>
      <c2>Row2 Col2</c2>
      <c3>Row2 Col3</c3>
      <c4>Row2 Col4</c4>
   </row>
   <row>
      <c1>Row3 Col1</c1>
      <c2>Row3 Col2</c2>
      <c3>Row3 Col3</c3>
      <c4>Row3 Col4</c4>
   </row>
   <row>
      <c1>Row4 Col1</c1>
      <c2>Row4 Col2</c2>
      <c3>Row4 Col3</c3>
      <c4>Row2 Col4</c4>
   </row>
</rows>

(: we construct a single string that has all the newlines and commas in the right places :)
let $file-as-csv-string :=
  string-join(
        for $row in $input//row
        return
          string-join(
             for $col in $row/*
             return
                $col/text()
          , ',')
    , $nl)

(: set the HTTP response headers with the content type and file name :)
let $set-content-type := response:set-header('Content-Type', 'text/csv')
let $set-file-name := response:set-header('Content-Disposition',  'attachment; filename="my-table.csv"')

(: There is no documentation on what the stream options are.
http://exist-db.org/exist/apps/fundocs/view.html?uri=http://exist-db.org/xquery/response&location=java:org.exist.xquery.functions.response.ResponseModule
:)
return response:stream($file-as-csv-string, '')

Which returns:

  Row1 Col1,Row1 Col2,Row1 Col3,Row1 Col4
  Row2 Col1,Row2 Col2,Row2 Col3,Row2 Col4
  Row3 Col1,Row3 Col2,Row3 Col3,Row3 Col4
  Row4 Col1,Row4 Col2,Row4 Col3,Row4 Col4

Discussion

The "string-join()" function takes two arguments, the first is the sequence of strings to be joined and the second is the separator.