[MarkLogic Dev General] Generate CSV Output
Glidden, Douglass A
Douglass.A.Glidden at boeing.com
Tue Oct 27 04:17:08 PST 2009
Alternatively:
for $id in $doc//id
return
for $addr in $id/following-sibling::addr
return fn:string-join(($id, $addr, ($addr/following-sibling::city)[1], ($addr/following-sibling::state)[1], ($addr/following-sibling::derivedField)[1]/test, ($addr/following-sibling::derivedField)[1]/test1), "|")
Note: Rob's solution is probably more efficient because it uses only one FLWOR expression-I'm mainly just providing this for purposes of illustration of the variety of ways of accomplishing things with XPath.
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 Whitby, Rob, CMG
Sent: Tuesday, October 27, 2009 07:58
To: General Mark Logic Developer Discussion
Subject: RE: [MarkLogic Dev General] Generate CSV Output
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<mailto:Bindu.Wavell at flatironssolutions.com>
Flatirons Solutions
http://www.flatironssolutions.com<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<mailto: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<mailto: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<mailto:Bindu.Wavell at flatironssolutions.com>
Flatirons Solutions
http://www.flatironssolutions.com<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<mailto:Douglass.A.Glidden at boeing.com>
To: general at developer.marklogic.com<mailto: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<mailto:Douglass.A.Glidden at boeing.com>
_____
From: general-bounces at developer.marklogic.com<mailto: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<mailto: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<mailto:ajmariella at hotmail.com>
To: general at developer.marklogic.com<mailto: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<mailto:Douglass.A.Glidden at boeing.com>
To: general at developer.marklogic.com<mailto: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<mailto:Douglass.A.Glidden at boeing.com>
_____
From: general-bounces at developer.marklogic.com<mailto: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<mailto: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<mailto:General at developer.marklogic.com>
http://xqzone.com/mailman/listinfo/general
_______________________________________________
General mailing list
General at developer.marklogic.com<mailto: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/d252aeca/attachment-0001.html
More information about the General
mailing list