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 := "&#10;"

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.

This article is issued from Wikibooks. The text is licensed under Creative Commons - Attribution - Sharealike. Additional terms may apply for the media files.