SSDN - xquery for invoice data

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

scott reachard

Subject: xquery for invoice data
Author: scott reachard
Date: 27 Mar 2009 03:19 PM

i have an invoice document that looks like this

<root>
<header> header info</header>
<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>
<line>
<item>1234</item>
<description>test item<description>
<qty>2</qty>
</line>
<inv><invdata>warehouse 1</invdata></inv>
<inv><invdata>location 1A</invdata></inv>
</root>

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>

thanks for any help

Minollo I.

Subject: xquery for invoice data
Author: Minollo I.
Date: 27 Mar 2009 09:54 PM

Looks like a typical positional grouping problem; see here for a more complex example: http://www.xml-connection.com/2008/02/convertig-proprietary-file-formats-to.html

In your case, something like:

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>

scott reachard

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:

<root>
<CreatedBy> user, date, system</CreatedBy>
<routing> printer, copies, etc</routing>
<header> header info</header>
<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>
<line>
<item>1234</item>
<description>test item<description>
<qty>2</qty>
</line>
<inv><invdata>warehouse 1</invdata></inv>
<inv><invdata>location 1A</invdata></inv>
<totals> total data </totals>
<footerMsg> remit to ....</footerMsg>
</root>

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.

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.