How to add CSV data to already available XML using XQuery file used in eXist Database

Question

I am working on eXist database, I am have a new Idea that I have to implement XML file using XQuery.

I want to convert CSV file to XML which is already in the database collection. And this XML file contains only necessary tags and information. And this converted data will be saved into XML in eXist Database.

XML like this: its name is 'createXML.xml'

<?xml version="1.0" encoding="UTF-8"?>
<records>
// All the Records from CSV file want to put here..... between Records tags
</records>

now the CSV file like this:

name,subject,marks    //header lines
krunal,maths,95
abc,sub1,87
def,sub2,67
...

Output like this

<?xml version="1.0" encoding="UTF-8"?>
<records>
   <user>
       <name>krunal</name>
       <subject>maths</subject>
       <marks>95</marks>
   </user>
   <user>
       <name>abc</name>
       <subject>sub1</subject>
       <marks>87</marks>
   </user>
   <user>
       <name>def</name>
       <subject>sub2</subject>
       <marks>67</marks>
   </user>
      .
      .
      .
</records>

Can anyone provide me how to add CSV data to already available XML using XQuery in eXist database and performs this function.

Answer 1

For reading a file look at your XQuery implementations documentations, here reading a file for eXist.

Wikibooks has an excellent example on how to parse CSV:

let $csv := 'name,faculty
alice,anthropology
bob,biology'

let $lines := tokenize($csv, '\n')
let $head := tokenize($lines[1], ',')
let $body := remove($lines, 1)
return
    <people>
        {
            for $line in $body
            let $fields := tokenize($line, ',')
            return
                <person>
                    {
                        for $key at $pos in $head
                        let $value := $fields[$pos]
                        return
                            element { $key } { $value }
                    }
                </person>
        }
    </people>

Another possibility would be to use another XQuery engine with builtin csv import support like Zorba or BaseX.

Answer 2

Assuming (1) that your CSV file is already in the database and (2) that you want to store the result of the CSV-to-XML in a new XML file, then there are 3 parts to your XQuery:

1. Get the contents of the CSV file using util:binary-doc() and util:binary-to-string(). Note that util:binary-to-string() assumes the file is encoded with UTF-8, but as the documentation shows you can tell the function the encoding scheme if your CSV file has a non-UTF-8 encoding.
2. Transform the CSV to your desired structure, using the XQuery Wikibook article on parsing CSV (already mentioned in @Ranon's reply). I assume you know enough XQuery to adapt this routine into your own function — which I will call local:csv-to-xml() below.
3. Store the resulting XML in the database using use the xmldb:store() function

(Note that the util and xmldb module are eXist-db specific modules, as this functionality is not covered by the XQuery specification. If you use a different XQuery implementation, you will need to use their implementation-specific methods.)

So here is the eXist-db solution:

let $csv-file := '/db/myCSV.csv'
let $csv-binary := util:binary-doc($csv-file)
let $csv := util:binary-to-string($csv-binary)
let $xml := local:csv-to-xml($csv)
return
    xmldb:store('/db', 'createXML.xml', $xml)

This will store the converted version of myCSV.csv as createXML.xml at the root of your eXist database: /db/createXML.xml.

If, instead, you wanted to append the XML result to an existing XML file, you will want to apply XQuery Update syntax — which lets you insert nodes, replace nodes, and delete nodes in documents stored in the database. In eXist-db, the documentation for eXist's XQuery Update syntax is at http://exist-db.org/exist/update_ext.xml.