OK. Solved it.
Below is the final query:
declare variable $in external; declare variable $out external; declare variable $vendor external;
file:write-text-lines($out, 'Name,Host,Path,Count,Time'), let $text := file:read-text($in) let $xml := csv:parse($text, map { 'header': true() })
for $x in $xml//record[contains(vendors,string($vendor))]
let $count := string($x/ATTACKCOUNT) let $name := $x/attackname let $time := string($x/TIME_STAMP) let $path := string($x/path) let $host := string($x/host) let $result := concat ($name,',',$host,',',$path,',',$count,',',$time)
return file:append-text-lines($out, $result)
I played with it so long, I'm not even sure what I finally did. But it works, so I'm not touching it :)
Thanks again Christian for all you help! Noam
On Wed, May 27, 2015 at 11:21 PM, Noam Green green.noam@gmail.com wrote:
Hi Christian,
The input file is quite large, so I tried to edit it and leave only 4 lines. The strangest thing happened: when trying to run the shortened file in the editor, I now get the same error "Content is not allowed in prolog.".
Below is my query code: declare variable $in external; declare variable $out external; declare variable $vendor external;
(: Trying to work with CSV let $text := file:read-text($in) let $xml := csv:parse($text, map { 'header': true() }) let $inputcsv := csv:serialize($xml, map { 'header': true() } )
:)
file:write-text-lines($out, 'Name,Host,Path,Count,Time'), for $x in doc($in)//record[contains(vendors,string($vendor))]
let $count := string($x/ATTACKCOUNT) let $name := $x/attackname let $time := string($x/TIME_STAMP) let $path := string($x/path) let $host := string($x/host) let $result := concat ($name,',',$host,',',$path,',',$count,',',$time) let $nothing := string('')
return file:append-text-lines($out, $result)
I tried adding some CSV manipulation lines (see commented-out above), as once I remove the comment, the editor comments: "Unexpected end of query: 'with CSV let $..."
It doesn't help even if I add a comma after the serialize command.
I know I'm missing something stupid, but can't seem to get around it.
Cheers, Noam
On Wed, May 27, 2015 at 10:42 PM, Christian Grün < christian.gruen@gmail.com> wrote:
Why does it work in the editor [...]
This surprises me. Could you attach me your input file and the query?
In general, the input of doc() must always be an XML document. However, you can use csv:parse for that (once again, please check out our Wiki for an example).
On Wed, May 27, 2015 at 10:33 PM, Christian Grün <
christian.gruen@gmail.com>
wrote:
C:\Temp>basex -b in=export_new.csv -b out=output.csv -b vendor=IID CSV_Query.xq
It looks as if you are trying to parse a CSV file (export_new.csv) as XML. Is this really what you wanna do?
Christian
I get the follwing error: Stopped at C:/Temp/CSV_Query.xq, 6/15: [FODC0002] "C:/Temp/export_new.csv" (Line 1): Content is not allowed
in
prolog.
This seems to be failing on the basic for $x in doc($in) command.
What am I doing wrong?
Thanks, Noam
On Wed, May 27, 2015 at 10:02 PM, Christian Grün christian.gruen@gmail.com wrote:
Hi Noam,
> I missed the option of adding a comma after the initial file:write > command > (the editor was constantly asking for a return command).
In XQuery, multiple expressions can always be separated with commas. For example, the following XQuery expression returns 4 items as results:
1, "string", <xml/>, file:read-text('abc.xml')
As XQuery itself, due to its functional nature, provides no
guarantee
that the four expressions of the above query will be evaluated one after another, the XQuery Scripting Extension [1] was proposed. It offers a semicolon to separate expressions with side-effects (such
as
file functions). Due to its complexity, however, it was not implemented by many XQuery implementations.
At least in BaseX (and I think in Saxon, eXist-db and Zorba as
well),
you can be assured that expressions separated with commas will
always
be evaluated one after another.
Well, this was probably more than you were asking for, but maybe
it's
of some interest anyway ;)
Christian
[1] http://www.w3.org/TR/xquery-sx-10/
> > Thanks again. It worked perfectly (although I must admit I used
the
> dirty > option, as the CSV examples are mainly on adapting CSV into XML, > while I > need the other direction). > > Noam > > On Wed, May 27, 2015 at 9:40 PM, Christian Grün > christian.gruen@gmail.com > wrote: >> >> Hi Noam, >> >> > let $csv := csv:serialize($result) >> > return file:write-text($out, $csv) >> > >> > The CVS that comes out only includes one line [...] >> >> As there are unlimited ways to represent XML nodes as CSV, there
is
>> no >> way to automatically a representation that always works best. For >> more >> information on creating an XML representation that will yield
good
>> results as CSV, please check out the documentation on our CSV
Module
>> [1]. >> >> > Now this works, but I can't seem to find a way to add the
headers
>> > to >> > the >> > first line of the file. >> >> Obviously, I would recommend you to use the existing CSV
features,
>> because it will take care of all the usal nifty details. However, >> here >> is one simple way to let your file start with a header line: >> >> file:write-text-lines($out, 'Name,Host,Path,Count,Time'), >> let $result := concat >> ($name,',',$host,',',$path,',',$count,',',$time) >> return file:append-text-lines($out, $result) >> >> Hope this helps, >> Christian >> >> [1] http://docs.basex.org/wiki/CSV_Module > >