i need to have it look something like this for each line and inv group:
<allLineData>
<line>
<item>1234</item>
<description>test item<description>
<qty>2</qty>
</line>
<inv><invdata>warehouse 1</invdata></inv>
<inv><invdata>batch 12345</invdata></inv>
<inv><invdata>location 1A</invdata></inv>
</allLineData>
there may be 0-3 inv sections following each line
everyting i've tried returns a 1 line followed by 1 inv duplicating the line or all the lines followed by all the invs
xquery version "1.0";
<root>
{
for $cf in (/Root/Line)
return
<allLineData>
$cf
for $cf1 in (/Root/inv)
return
$cf1
</allLineData>
}
</root>
declare function local:getRelatedInv($item) {
let $nextItem :=$item/following-sibling::*[local-name()!="inv"][1]
for $related in $item/following-sibling::*[local-name()="inv"]
where if ($nextItem) then $related << $nextItem else true()
return $related
};
for $line in //line
return
<allLineData>
{$line}
{
for $inv in local:getRelatedInv($line)
return $inv
}
</allLineData>
Subject:xquery for invoice data Author:scott reachard Date:02 Apr 2009 11:49 AM
Thanks!!! that worked great and the link was an interesting read. I think i actually understand some of it!.
I've got one more problem I can't get resolved.
there is header and footer information before and after the line data. I need to include those in the output. I can get it if i specifically name each section, but i was hoping to have a more flexible way to do it.
input document looks something like this:
Thanks to the above solution, i've got the line data combined
xquery version "1.0";
declare function local:getRelatedInv($item) {
let $nextItem :=$item/following-sibling::*[local-name()!="Section_Body_BodyInventDim"][1]
for $related in $item/following-sibling::*[local-name()="Section_Body_BodyInventDim"]
where if ($nextItem) then $related << $nextItem else true()
return $related
};
<root>
{
for $line in /Root/Section_Body_BodyInventPickingListJournalLine
return
<allLineData>
{$line/*}
{
for $inv in local:getRelatedInv($line)
let $det := $inv/Field_InventDimPrint
return
if (contains($det,"Batch")) then <Batch>{normalize-space(substring-after($det,":"))}</Batch> else
if (contains($det,"Warehouse")) then <Warehouse>{normalize-space(substring-after($det,":"))}</Warehouse> else
if (contains($det,"Location")) then <Location>{normalize-space(substring-after($det,":"))}</Location> else $det
}
</allLineData>
}
</root>
how can i include the header and footer nodes?
thanks for any help, I've been trying to figure this out on my own (i learn it better that way), but i've reached my I give up point.