XML Editor
Sign up for a WebBoard account Sign Up Keyword Search Search More Options... Options
Chat Rooms Chat Help Help News News Log in to WebBoard Log in Not Logged in
Show tree view Topic
Go to previous topicPrev TopicGo to next topicNext Topic
Postnext
scott reachardSubject: 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>



test xml files
test1.xml
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Section_PageHeader_Invoice>
<Field_CustInvoiceJour_InvoiceId>test1 </Field_CustInvoiceJour_InvoiceId>
</Section_PageHeader_Invoice>
<Section_Body_BodyCustInvoiceTrans>
<Field_CustInvoiceTrans_SalesKitTypeDNT> BOM </Field_CustInvoiceTrans_SalesKitTypeDNT>
<Field_ItemId> 11-302-03PC </Field_ItemId>
</Section_Body_BodyCustInvoiceTrans>
<Section_Body_BodyCustInvoiceTrans>
<Field_CustInvoiceTrans_SalesKitTypeDNT> BOM </Field_CustInvoiceTrans_SalesKitTypeDNT>
<Field_ItemId> 11-302-06PC </Field_ItemId>
</Section_Body_BodyCustInvoiceTrans>
<Section_Body_BodyCustInvoiceTrans>
<Field_CustInvoiceTrans_SalesKitTypeDNT> Standard </Field_CustInvoiceTrans_SalesKitTypeDNT>
<Field_ItemId> 12-100-06 </Field_ItemId>
</Section_Body_BodyCustInvoiceTrans>
</Root>

test2.xml
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Section_PageHeader_Invoice>
<Field_CustInvoiceJour_InvoiceId>test2 </Field_CustInvoiceJour_InvoiceId>
</Section_PageHeader_Invoice>
<Section_Body_BodyCustInvoiceTrans>
<Field_CustInvoiceTrans_SalesKitTypeDNT> Standard </Field_CustInvoiceTrans_SalesKitTypeDNT>
<Field_ItemId> ODG1016S </Field_ItemId>
</Section_Body_BodyCustInvoiceTrans>
</Root>

test3.xml
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<Section_PageHeader_Invoice>
<Field_CustInvoiceJour_InvoiceId> test3 </Field_CustInvoiceJour_InvoiceId>
</Section_PageHeader_Invoice>
<Section_Body_BodyCustInvoiceTrans>
<Field_CustInvoiceTrans_SalesKitTypeDNT> BOM </Field_CustInvoiceTrans_SalesKitTypeDNT>
<Field_ItemId> AWS-18C </Field_ItemId>
</Section_Body_BodyCustInvoiceTrans>
<Section_Body_BodyCustInvoiceTrans>
<Field_CustInvoiceTrans_SalesKitTypeDNT> BOM </Field_CustInvoiceTrans_SalesKitTypeDNT>
<Field_ItemId> AWS-27 </Field_ItemId>
</Section_Body_BodyCustInvoiceTrans>
<Section_Body_BodyCustInvoiceTrans>
<Field_CustInvoiceTrans_SalesKitTypeDNT> BOM </Field_CustInvoiceTrans_SalesKitTypeDNT>
<Field_ItemId> AWSM-25 </Field_ItemId>
</Section_Body_BodyCustInvoiceTrans>
<Section_Body_BodyCustInvoiceTrans>
<Field_CustInvoiceTrans_SalesKitTypeDNT> BOM </Field_CustInvoiceTrans_SalesKitTypeDNT>
<Field_ItemId> AWSM-28 </Field_ItemId>
</Section_Body_BodyCustInvoiceTrans>
</Root>



Thanks in advance!
--scott

Postnext
(Deleted User) 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
()


Alberto

Posttop
scott reachardSubject: Better way to do this?
Author: scott reachard
Date: 12 Dec 2008 08:49 AM
Thanks!!

that makes sense, and it looks much better than how i did it!

 
Go to previous topicPrev TopicGo to next topicNext Topic
Download A Free Trial of Stylus Studio 6 XML Professional Edition Today! 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

Log In Options

Site Map | Privacy Policy | Terms of Use | Trademarks
Stylus Scoop XML Newsletter:
W3C Member
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.