SSDN - Whitespace problem when returning csv file within xquery

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Bruce Cantor

Subject: Whitespace problem when returning csv file within xquery
Author: Bruce Cantor
Date: 25 Jul 2022 01:20 PM
Originally Posted: 25 Jul 2022 01:19 PM

Hi Forum

I am boxing with a problem where the resulting csv file includes a whitespace character on every line except the header line.

I am iterating over data and returning a string for every iteration, problem is that every line includes a whitespace character as the first character in the line.

The simplified script can be seen here:

declare namespace a = "http://generic.xml.eetgroup.com";
declare variable $gv_ProductLinkPreFix := 'https://www.eetgroup.com/sv-se/itemId?itemid=';
declare variable $gv_headerline := 'Nr.;Beskrivning;Manufacturer Part Number;EAN;Your No.;Order no.;Order date;Referensnr.;Er referens;Antal;Återstår;Leveransdatum;Datum;Leveranssätt;Leveransadress;Valuta;Belopp;Totalt;Product Page
';
declare option ddtek:serialize "encoding=UTF-8";

$gv_headerline,
for $Dispatch in /a:OrderList/a:Order/a:OrderLines/a:OrderLine/a:Dispatch return return 'peter
'

Can anyone tell me how to get rid of the whitespace character?
I have tried everything I can think of, but whether I use replace, normalize-space ... I end up having a whitespace in the beginning of each line.

like this :

Nr.;Beskrivning;Manufacturer Part Number;EAN;Your No.;Order no.;Order date;Referensnr.;Er referens;Antal;Återstår;Leveransdatum;Datum;Leveranssätt;Leveransadress;Valuta;Belopp;Totalt;Product Page
<space>peter
<space>peter
<space>peter
<space>peter
<space>peter
<space>peter
<space>peter
<space>peter
<space>peter

should have been:

Nr.;Beskrivning;Manufacturer Part Number;EAN;Your No.;Order no.;Order date;Referensnr.;Er referens;Antal;Återstår;Leveransdatum;Datum;Leveranssätt;Leveransadress;Valuta;Belopp;Totalt;Product Page
peter
peter
peter
peter
peter
peter
peter
peter
peter

Kind regards
Bruce

Ivan Pedruzzi

Subject: Whitespace problem when returning csv file within xquery
Author: Ivan Pedruzzi
Date: 26 Jul 2022 12:37 AM

Bruce,

Your XQuery creates a sequence of items which when serialized are joined with a space character.

You need to use function fn:string-join which joins all items in a sequence and generate a single string, you can re-write your example as following

declare option ddtek:serialize "method=text";

let $all_items := (
$gv_headerline,
for $Dispatch in /a:OrderList/a:Order/a:OrderLines/a:OrderLine/a:Dispatch
return 'peter'
)
return fn:string-join($all_items, "
")

The approach above works if you are processing a small number of items, but does not scale if you have a large number of items, because you are loading the entire result of the join as string in memory.

If your XPS is licensed for the Flat File Converter you can stream any size into CSV.

You can output 3-level XML structure and the converter turns it into CSV

declare option ddtek:serialize "method=CSV,first=yes";

<root>
{
for $Dispatch in /a:OrderList/a:Order/a:OrderLines/a:OrderLine/a:Dispatch
return
<row>
<columnName>peter</columnName>
<row>
}
</root>

-Ivan

Ivan Pedruzzi
Stylus Studio Team

Bruce Cantor

Subject: Whitespace problem when returning csv file within xquery
Author: Bruce Cantor
Date: 26 Jul 2022 11:37 AM

Hi Ivan

Thanks for answering.

Our file can be very large so I need to use the 'not to much memory' solution.

I created a xslt script and used the convertFromXml component instead, which works fine in the development environment, but not so fine on the server.

When I input XML in this structure to the component (converter:CSV:first=yes:quotes=:sep=;)

<table>
<row>
<Nr>01186-001</Nr>
<Beskrivning>T8705 VIDEO DECODER</Beskrivning>
<ManufacturerPartNumber>01186-001</ManufacturerPartNumber>
<EAN>7331021061774</EAN>
<YourNo/>
<OrderNo>1852588</OrderNo>
<OrderDate>15-09-2021</OrderDate>
<Referensnr>4500923432</Referensnr>
<ErReferens>Ellen Rosen</ErReferens>
<Antal>4</Antal>
<Återstår>0</Återstår>
<Leveransdatum>4 st. 'Shipped'</Leveransdatum>
<Datum>07-12-2021</Datum>
<Leveranssätt>DHL-SE</Leveranssätt>
<Leveransadress>XXX Security Sverige AB, hus 8-9, Elektronvägen 25, , 141 49, HUDDINGE, SE</Leveransadress>
<Valuta>EUR</Valuta>
<Belopp>10.29</Belopp>
<Totalt>41.16</Totalt>
<ProductPage>https://www.eetgroup.com/sv-se/itemId?itemid=01186-001</ProductPage>
</row>
</table>

I get, on the server, an error that looks like this:

(host:172.16.0.73) jul. 26, 2022 1:16:45 PM com.ivitechnologies.pipeline.server.OperationRunner runInternal
SEVERE: Error during conversion: Error during conversion: java.lang.StringIndexOutOfBoundsException: String index out of range: 0
operation converter:CSV:first=yes:quotes=:sep=;
operation properties:
Adapter URL__isURL=true
annotation=false
Adapter URL=converter:CSV:first=yes:quotes=:sep=;
rotate=270
Name=Convert from XML
h=100
Operation=CONVERTFROMXML
ID=6
y=220
x=775
w=140
input URL file:///E:/Nav-PipelineServer-DataExchange/FilesFromNavision/Stanley/SE_5011817_20220726T131639.xml
I9.xmlconverter.exception.ConverterException: Error during conversion: Error during conversion: java.lang.StringIndexOutOfBoundsException: String index out of range: 0
at I9.xmlconverter.exception.ConverterException.wrapAsConverterException(ConverterException.java:117)
at I9.xmlconverter.platform.WrappedXMLReader$Dispatchable.runToEnd(WrappedXMLReader.java:70)
at I9.xmlconverter.impl.Job.runConversionNow(Job.java:299)
at I9.xmlconverter.impl.Job.runFromXml(Job.java:278)
at I9.xmlconverter.impl.Job.(Job.java:112)
at I9.xmlconverter.impl.ConverterImpl.convert(ConverterImpl.java:202)
at I9.xmlconverter.impl.ConverterImpl.convert(ConverterImpl.java:149)
at com.ivitechnologies.pipeline.server.OperationConvertFromXML.runInternal(OperationConvertFromXML.java:27)

Can you tell why I am getting this error on the Pipeline server, but not on the development environment?

Ivan Pedruzzi

Subject: Whitespace problem when returning csv file within xquery
Author: Ivan Pedruzzi
Date: 26 Jul 2022 03:00 PM

In the converter URL you have not specified the quotes character.Was this intentional?

converter:CSV:first=yes:quotes=:sep=;

I can reproduce the same error in Stylus Studio

The following URL works fine

converter:CSV:first=yes:sep=;

Ivan Pedruzzi
Stylus Studio Team

Bruce Cantor

Subject: Whitespace problem when returning csv file within xquery
Author: Bruce Cantor
Date: 23 Aug 2022 11:56 AM

Thanks, everything working fine now.

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.