[MarkLogic Dev General] Generate CSV Output
Tony Mariella
ajmariella at hotmail.com
Tue Oct 27 04:42:27 PST 2009
Thanks, I figured it out. You guys were all a big help.
-Tony
Subject: RE: [MarkLogic Dev General] Generate CSV Output
Date: Tue, 27 Oct 2009 12:26:09 +0000
From: Rob.Whitby at currentmedicinegroup.com
To: general at developer.marklogic.com
The example code I wrote works with the original xml you provided. You’ll have to modify it slightly if your xml is different.
From: general-bounces at developer.marklogic.com [mailto:general-bounces at developer.marklogic.com] On Behalf Of Tony Mariella
Sent: 27 October 2009 12:23
To: general at developer.marklogic.com
Subject: RE: [MarkLogic Dev General] Generate CSV Output
Does it matter if the elements are embedded ?
let $doc :=
<doc>
<if>
<id>Test1</id>
<geo>
<addr>10 Main St</addr>
<city>Baltimore</city>
<state>MD</state>
</geo>
<derivedField>
<test>TestA</test>
<test1>Test1A</test1>
</derivedField>
<geo>
<addr>20 Main St</addr>
<city>Linthicum</city>
<state>MD</state>
</geo>
<derivedField>
<test>TestB</test>
<test1>Test1B</test1>
</derivedField>
<geo>
<addr>30 Main St</addr>
<city>Columbia</city>
<state>MD</state>
</geo>
<derivedField>
<test>TestC</test>
<test1>Test1C</test1>
</derivedField>
</if>
<if>
<id>Test2</id>
<geo>
<addr>20 York St</addr>
<city>Pasadena</city>
<state>CA</state>
</geo>
<derivedField>
<test>Test2A</test>
<test1>Test2A</test1>
</derivedField>
</if>
</doc>
Subject: RE: [MarkLogic Dev General] Generate CSV Output
Date: Tue, 27 Oct 2009 11:58:20 +0000
From: Rob.Whitby at currentmedicinegroup.com
To: general at developer.marklogic.com
Give this a go:
for $f in $doc/if/derivedField
return
fn:string-join((
$f/../id/text(),
($f/preceding-sibling::addr)[last()]/text(),
($f/preceding-sibling::city)[last()]/text(),
($f/preceding-sibling::state)[last()]/text(),
$f/test/text(),
$f/test1/text()
), '|')
From: general-bounces at developer.marklogic.com [mailto:general-bounces at developer.marklogic.com] On Behalf Of Tony Mariella
Sent: 27 October 2009 11:39
To: general at developer.marklogic.com
Subject: RE: [MarkLogic Dev General] Generate CSV Output
Thanks for all the sugggestions, they work great. Now let me throw a curve in the XML:
let $doc :=
<doc>
<if>
<id>Test1</id>
<addr>10 Main St</addr>
<city>Baltimore</city>
<state>MD</state>
<derivedField>
<test>TestA</test>
<test1>Test1A</test1>
</derivedField>
<addr>20 Main St</addr>
<city>Linthicum</city>
<state>MD</state>
<derivedField>
<test>TestB</test>
<test1>Test1B</test1>
</derivedField>
<addr>30 Main St</addr>
<city>Columbia</city>
<state>MD</state>
<derivedField>
<test>TestC</test>
<test1>Test1C</test1>
</derivedField>
</if>
<if>
<id>Test2</id>
<addr>20 York St</addr>
<city>Pasadena</city>
<state>CA</state>
<derivedField>
<test>Test2A</test>
<test1>Test2A</test1>
</derivedField>
</if>
</doc>
How can I acheive this output:
Test1|10 Main St|Baltimore|MD|TestA|Test1A
Test1|20 Main St|Linthicum|MD|TestB|Test1B
Test1|30 Main St|Columbia|MD|TestC|Test1C
Test2|20 York St|Pasadena|CA|Test2A|Test2A
-Tony
From: bindu.wavell at flatironssolutions.com
Subject: Re: [MarkLogic Dev General] Generate CSV Output
Date: Mon, 26 Oct 2009 12:07:39 -0600
To: general at developer.marklogic.com
After fixing some more items in the XML I was able to produce the following:
let $doc :=
<doc>
<if>
<id>Test1</id>
<addr>10 Main St</addr>
<city>Baltimore</city>
<state>MD</state>
<derivedField>
<test>TestA</test>
<test1>Test1A</test1>
</derivedField>
<derivedField>
<test>TestB</test>
<test1>Test1B</test1>
</derivedField>
<derivedField>
<test>TestC</test>
<test1>Test1C</test1>
</derivedField>
</if>
<if>
<id>Test2</id>
<addr>20 York St</addr>
<city>Pasadena</city>
<state>CA</state>
<derivedField>
<test>Test2A</test>
<test1>Test2A</test1>
</derivedField>
</if>
</doc>a
for $fi in $doc/*
for $de in $fi/derivedField
return fn:concat($fi/id/text(), "|", $fi/addr/text(), "|", $fi/city/text(), "|", $fi/state/text(), "|", $de/test/text(), "|", $de/test1/text())
Bindu Wavell
Consultant: Tech Lead
+1 (720) 436-8146 | Office - Cell
Bindu.Wavell at flatironssolutions.com
Flatirons Solutions
http://www.flatironssolutions.com
On Oct 26, 2009, at 11:39 AM, Tony Mariella wrote:
They're just text nodes Test1 and Test2. Typo, my mistake
From: bindu.wavell at flatironssolutions.com
Subject: Re: [MarkLogic Dev General] Generate CSV Output
Date: Mon, 26 Oct 2009 11:36:30 -0600
To: general at developer.marklogic.com
Tony,
Should Test1 and Test2 be elements <Test1/>, <Test2/> or are they supposed to be text nodes for the id <id>Test1</id> and <id>Test2</id>?
Bindu Wavell
Consultant: Tech Lead
+1 (720) 436-8146 | Office - Cell
Bindu.Wavell at flatironssolutions.com
Flatirons Solutions
http://www.flatironssolutions.com
On Oct 26, 2009, at 11:28 AM, Tony Mariella wrote:
If I have data returned from my query that looks like this:
<if>
<id><Test1</id>
<addr>10 Main St<addr>
<city>Baltimore</city>
<stateMD</state>
<derivedField>
<test>TestA</test>
<test1>Test1A</test>
<derivedField>
<derivedField>
<test>TestB</test>
<test1>Test1B</test>
<derivedField>
<derivedField>
<test>TestC</test>
<test1>Test1C</test>
<derivedField>
</if>
<if>
<id><Test2</id>
<addr>20 York St<addr>
<city>Pasadena</city>
<state>CA</state>
<derivedField>
<test>Test2A</test>
<test1>Test2A</test>
<derivedField>
</if>
I would like to have the following returned:
Test1|10 Main St|Baltimore|MD|TestA|Test1A
Test1|10 Main St|Baltimore|MD|TestB|Test1B
Test1|10 Main St|Baltimore|MD|TestC|Test1C
Test2|20 York St|Pasadena|CA|Test2A|Test2A
How would I loop through each derivedField tag and only return a single <test> field through each loop ?
-Tony
From: Douglass.A.Glidden at boeing.com
To: general at developer.marklogic.com
Date: Wed, 21 Oct 2009 11:46:13 -0500
Subject: RE: [MarkLogic Dev General] Generate CSV Output
Yes, that's correct—an easy way to do it might be with the fn:string() function:
local:return-in-csv((fn:string($id), fn:string($description), fn:string($city), fn:string($country), fn:string($hosts)))
That will convert any empty sequences to empty strings. Not saying that's necessarily the best way, just probably the easiest.
Doug Glidden
Software Engineer
The Boeing Company
Douglass.A.Glidden at boeing.com
From: general-bounces at developer.marklogic.com [mailto:general-bounces at developer.marklogic.com] On Behalf Of Tony Mariella
Sent: Wednesday, October 21, 2009 12:00
To: general at developer.marklogic.com
Subject: RE: [MarkLogic Dev General] Generate CSV Output
Never mind I fixed it. Needed to initialize the data to "" if nothing is present.
-Tony
From: ajmariella at hotmail.com
To: general at developer.marklogic.com
Subject: RE: [MarkLogic Dev General] Generate CSV Output
Date: Wed, 21 Oct 2009 11:36:39 -0400
Sorry, about that, trying to copy from one system to another.
When no data is present... mean that there is sometimes empty data in any field Example Lets say this call is in a loop.
$id := 'Test1'
$descrption := 'test1 Data'
$city := 'Baltimore'
$country := 'USA'
$hosts := 5
then
$id := 'Test2'
$descrption := ''
$city := ''
$country := ''
$hosts := 4
local:return-in-csv(($id, $description, $city, $country, $hosts))
I should get this:
Test1|test1 Data|Baltimore|USA|5
Test2|||4
But I end up getting this:
Test1|test1 Data|Baltimore|USA|5
Test2|4
The extra "|"'s are missing.
From: Douglass.A.Glidden at boeing.com
To: general at developer.marklogic.com
Date: Wed, 21 Oct 2009 10:03:42 -0500
Subject: RE: [MarkLogic Dev General] Generate CSV Output
Tony,
The way you've entered it here, there's no way this function can work at all, because it has a couple of syntax errors:
It is missing an else (on the let $field2 :=... line).
It is missing a $ (on the let $field3 :=... line).
The "codepoints-to-string" function is misspelled (on the same line).
Beyond those errors, though, I have to say I don't quite understand what you mean by "when no data is present"—your sample call has only variables, which is not very enlightening as to what data is actually getting passed into the function. Can you give an example of what specific data causes the function not to work correctly?
Doug Glidden
Software Engineer
The Boeing Company
Douglass.A.Glidden at boeing.com
From: general-bounces at developer.marklogic.com [mailto:general-bounces at developer.marklogic.com] On Behalf Of Tony Mariella
Sent: Wednesday, October 21, 2009 09:35
To: general at developer.marklogic.com
Subject: [MarkLogic Dev General] Generate CSV Output
I have a function I created that generates a delimited output instead
of XML. But there seems to be a problem. The function works fine in CQ, but when I try to call the xquery programmatically, all the delimiters do not seem to appear when no data is present.
I am delimiting using the "|" because my data may contain commas.
Here is my function:
define function local:return-in-csv(
$sequence as xs:string*
)
as xs:string?
{
string-join(
for $field in $sequence
let $must-quote := (contains($field, "|") or contains ($field, codepoints-to-string((10))))
let $field2 := if ($must-quote) then
replace($field, '"', '""')
let $field3 := if (contains(field2, codeopints-to-string((13)))) then
replace($field2, codepoints-to-string((13)), "")
else $field2
let $field4 := if ($must-quote) then
concat('"', $field3, '"')
else $field3
return $field4
,
"|"
)
}
Here is the call to the function:
local:return-in-csv(($id, $description, $city, $country, $hosts))
Tony Mariella
_______________________________________________
General mailing list
General at developer.marklogic.com
http://xqzone.com/mailman/listinfo/general
_______________________________________________
General mailing list
General at developer.marklogic.com
http://xqzone.com/mailman/listinfo/general
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://xqzone.marklogic.com/pipermail/general/attachments/20091027/2f7d221a/attachment-0001.html
More information about the General
mailing list