Subject:Better way to do this? Author:scott reachard Date:10 Dec 2008 10:11 AM
first time posting here! I'm an sql guy trying to be an xquery guy, I've been working on learning xquery and xml for about a week and i'm starting to make some sense out of this. I have a directory of invoice XML documents and i need to find and extract all invoice documents that contain a inovice line with a type of BOM. I need the entire invoice, not just the line with the BOM type. I've got something that works, but think there might be a better way to do it.
the actual purpose isn't to get a line count, but you get the idea. My question is is there a better way to do the join so I don't have to go to the filesystem twice?
here's what i've got:
the query:
xquery version "1.0";
let $invoices := distinct-values (
for $cft in collection("file:///C:/Invoices?select=test*.xml")
let $cf :=saxon:discard-document($cft)
let $invoice :=$cf//Root/Section_PageHeader_Invoice/Field_CustInvoiceJour_InvoiceId
for $item in $cf//Root/Section_Body_BodyCustInvoiceTrans
where normalize-space($item/Field_CustInvoiceTrans_SalesKitTypeDNT ) ='BOM'
return $invoice)
for $cft1 in collection("file:///C:/Invoices?select=test*.xml")
let $cf1 :=saxon:discard-document($cft1)
where data($cf1//Root/Section_PageHeader_Invoice/Field_CustInvoiceJour_InvoiceId) =$invoices
return
<Invoice>
{$cf1//Root/Section_PageHeader_Invoice/Field_CustInvoiceJour_InvoiceId}
<lines>{count($cf1//Root/Section_Body_BodyCustInvoiceTrans)}</lines>
</Invoice>
Subject:Better way to do this? Author:(Deleted User) Date:12 Dec 2008 05:55 AM
Hi Scott,
you can use the feature of XQuery that allows running functions inside an XPath expression to build a set of normalized invoice types; at that point, a single test can tell you if there is at least one BOM invoice in the entire file.
xquery version "1.0";
for $cft in collection("file:///C:/Invoices?select=test*.xml")
let $cf :=saxon:discard-document($cft)
return
if($cf//Root/Section_Body_BodyCustInvoiceTrans/normalize-space(Field_CustInvoiceTrans_SalesKitTypeDNT )='BOM')
then
<Invoice>
{$cf//Root/Section_PageHeader_Invoice/Field_CustInvoiceJour_InvoiceId}
<lines>{count($cf//Root/Section_Body_BodyCustInvoiceTrans)}</lines>
</Invoice>
else
()