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
M KlineSubject: XQuery sort & extract data
Author: M Kline
Date: 25 Oct 2006 07:08 PM
Hello -
I'm new to XQuery but I was able to extract data from my existing XML document. The resulting XML is good, however because the Customer sends the XML with all <Value> elements, I'm not sure how to extract/pick...sorry for wrong terminology...only the elements that I want, then sort them because they're all <Value>.

I really only want the "Shipper's ID# for Shipment" value, the "Pay Item" value, the "Purchase Order Number" value, "Sellers Inv #" value, the "Invoice" value (which includes a date, quantity, piece price and part number), "Order Number" value, "Check Number" value, "Received" which is a date Value.....then sort that by the "Shipper ID Number".

Also, once I can get this done, can I somehow put this in a Word document so our Accounting Department can use it?

Here's an example of the resulting XQuery in hopes it makes more sense:

<xqr:xpath-query-result xmlns:xqr="http://www.stylusstudio.com/xpath-query-result">
<!-- Query Result: Element(s) -->
<Value Display="Remittance Information Only">I</Value>
<Value>60214.59</Value>
<Value Display="Credit">C</Value>
<Value Display="Automated Clearing House (ACH)">ACH</Value>
<Value Display="Vendor Payment">VEN</Value>
<Value Display="Current Transaction Trace Numbers">1</Value>
<Value>2006102053094</Value>
<Value Display="Transaction Creation" Function="UnknownItem">097</Value>
<Value>20061020</Value>
<Value>50039</Value>
<Value Display="Payer">PR</Value>
<Value>MYCUSTOMER</Value>
<Value Display="D-U-N-S Number, Dun &amp; Bradstreet">1</Value>
<Value>123456789</Value>
<Value Display="Accounts Payable Department">AP</Value>
<Value>ACCOUNTING PERSON</Value>
<Value Display="Telephone">TE</Value>
<Value>1-555-555-5555</Value>
<Value Display="Electronic Mail">EM</Value>
<Value>blahblahblah@customer.com</Value>
<Value>1</Value>
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
<Value>090838</Value>
<Value Display="Pay Item">PI</Value>
<Value>26.8</Value>
<Value Display="Purchase Order Number" Function="PONum">PO</Value>
<Value>00300000</Value>
<Value Display="Seller's Invoice Number" Function="UnknownItem">IV</Value>
<Value>ERS Invoice37209</Value>
<Value Display="Invoice" Function="UnknownItem">003</Value>
<Value>20061019</Value>
<Value>2000</Value>
<Value>0.0134</Value>
<Value>0001072000</Value>
<Value Display="Order Number" Function="UnknownItem">OQ</Value>
<Value>3725789</Value>
<Value Display="Check Number">CK</Value>
<Value>1166481</Value>
<Value Display="Received" Function="ReceivedDate">050</Value>
<Value>20061004</Value>
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
<Value>090838</Value>
<Value Display="Pay Item">PI</Value>
<Value>5</Value>
<Value Display="Purchase Order Number" Function="PONum">PO</Value>
<Value>00300000</Value>
<Value Display="Seller's Invoice Number" Function="UnknownItem">IV</Value>
<Value>ERS Invoice37209</Value>
<Value Display="Invoice" Function="UnknownItem">003</Value>
<Value>20061019</Value>
<Value>1000</Value>
<Value>0.005</Value>
<Value>0001087000</Value>
<Value Display="Order Number" Function="UnknownItem">OQ</Value>
<Value>3725789</Value>
<Value Display="Check Number">CK</Value>
<Value>1166481</Value>
<Value Display="Received" Function="ReceivedDate">050</Value>
<Value>20061004</Value>
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
<Value>090835</Value>
<Value Display="Pay Item">PI</Value>
<Value>132</Value>
<Value Display="Purchase Order Number" Function="PONum">PO</Value>
<Value>00300000</Value>
<Value Display="Seller's Invoice Number" Function="UnknownItem">IV</Value>
<Value>ERS Invoice37209</Value>
<Value Display="Invoice" Function="UnknownItem">003</Value>
<Value>20061019</Value>
<Value>500</Value>
<Value>0.264</Value>
<Value>0001134000</Value>
<Value Display="Order Number" Function="UnknownItem">OQ</Value>
<Value>3725786</Value>
<Value Display="Check Number">CK</Value>
<Value>1166481</Value>
<Value Display="Received" Function="ReceivedDate">050</Value>
<Value>20061004</Value>
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
<Value>090835</Value>
<Value Display="Pay Item">PI</Value>
<Value>73.32</Value>
<Value Display="Purchase Order Number" Function="PONum">PO</Value>
<Value>00300000</Value>
<Value Display="Seller's Invoice Number" Function="UnknownItem">IV</Value>
<Value>ERS Invoice37209</Value>
<Value Display="Invoice" Function="UnknownItem">003</Value>
<Value>20061019</Value>
<Value>50</Value>
<Value>1.4664</Value>
<Value>0001140000</Value>
<Value Display="Order Number" Function="UnknownItem">OQ</Value>
<Value>3725786</Value>
<Value Display="Check Number">CK</Value>
<Value>1166481</Value>
<Value Display="Received" Function="ReceivedDate">050</Value>
<Value>20061004</Value>
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
<Value>090833</Value>
<Value Display="Pay Item">PI</Value>
<Value>6.57</Value>
<Value Display="Purchase Order Number" Function="PONum">PO</Value>
<Value>00300000</Value>
<Value Display="Seller's Invoice Number" Function="UnknownItem">IV</Value>
<Value>ERS Invoice37209</Value>
<Value Display="Invoice" Function="UnknownItem">003</Value>
<Value>20061019</Value>
<Value>140</Value>
<Value>0.0469</Value>
<Value>0003035000</Value>
<Value Display="Order Number" Function="UnknownItem">OQ</Value>
<Value>3725784</Value>
<Value Display="Check Number">CK</Value>
<Value>1166481</Value>
<Value Display="Received" Function="ReceivedDate">050</Value>
<Value>20061004</Value>
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
<Value>090833</Value>
..........then repeats with the next Shipper ID Number information, etc. until the end of the XML

Thanks much!

Mary



Postnext
Ivan PedruzziSubject: XQuery sort & extract data
Author: Ivan Pedruzzi
Date: 25 Oct 2006 10:46 PM
Hi Mary,

Could you post a sample of your input document?


Ivan Pedruzzi
Stylus Studio Team
http://www.stylusstudio.com/xml_download.html

Postnext
M KlineSubject: XQuery sort & extract data
Author: M Kline
Date: 26 Oct 2006 05:55 AM
Sure thing. It's a huge file, so I've copied and pasted some of it here.
Thank You.

<?xml version="1.0"?>
<!DOCTYPE FormData [
]>
<FormData DocType="Remittance Advice" Name="Generic Remittance Advice" SenderID="XXXX" ReceiverID="XXX" Version="1.0" TestProd="Test" SupplierID="Ixxxxxx" PartnerID="XXXXXXXXXXX" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="file:///c:/Documents%20and%20Settings/mkline/Desktop/StylusStryker820.xsd">
<Form Name="RMMain">
<Item Name="TransHandlingCode">
<Value Display="Remittance Information Only">I</Value>
</Item>
<Item Name="Amount">
<Value>60214.59</Value>
</Item>
<Item Name="CreditDebitFlag">
<Value Display="Credit">C</Value>
</Item>
<Item Name="PaymentMethodCode">
<Value Display="Automated Clearing House (ACH)">ACH</Value>
</Item>
<Item Name="BusinessFunctionCode">
<Value Display="Vendor Payment">VEN</Value>
</Item>
<Item Name="TraceCode">
<Value Display="Current Transaction Trace Numbers">1</Value>
</Item>
<Item Name="RMNum" Info2="1">
<Value>xxxxxxxxxxxx</Value>
</Item>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Transaction Creation" Function="UnknownItem">097</Value>
</Item>
<Item Name="Date">
<Value>20061020</Value>
</Item>
<Item Name="Time">
<Value>50039</Value>
</Item>
</Group>
</Form>
<Form Name="NameAddressDetail">
<Item Name="Type" NadType="PR">
<Value Display="Payer">PR</Value>
</Item>
<Item Name="Name" NadName="PR">
<Value>CUSTOMER NAME</Value>
</Item>
<Item Name="Qualifier" NadQual="PR">
<Value Display="D-U-N-S Number, Dun &amp; Bradstreet">1</Value>
</Item>
<Item Name="Code" NadCode="PR">
<Value>XXXXXXXXX</Value>
</Item>
<Form Name="ContactDetail">
<Group>
<Item Name="Type">
<Value Display="Accounts Payable Department">AP</Value>
</Item>
<Item Name="Name" NadContactName="PR">
<Value>XXXX XXXX</Value>
</Item>
<Item Name="CommType">
<Value Display="Telephone">TE</Value>
</Item>
<Item Name="CommNum">
<Value>1-555-555-5555</Value>
</Item>
<Item Name="CommType2">
<Value Display="Electronic Mail">EM</Value>
</Item>
<Item Name="CommNum2">
<Value>XXXX.XXXXXX@XXXXXXX.com</Value>
</Item>
</Group>
</Form>
</Form>
<Form Name="EntityDetail">
<Item Name="AssignedNum">
<Value>1</Value>
</Item>
<Form Name="RMItemDetail">
<Item Name="RefIDQual">
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
</Item>
<Item Name="RefID">
<Value>090838</Value>
</Item>
<Item Name="PaymentActionCode">
<Value Display="Pay Item">PI</Value>
</Item>
<Item Name="PaymentAmount">
<Value>26.8</Value>
</Item>
<Form Name="ReferenceNums">
<Group>
<Item Name="Qualifier">
<Value Display="Purchase Order Number" Function="PONum">PO</Value>
</Item>
<Item Name="Number">
<Value>00300000</Value>
</Item>
</Group>
<Group>
<Item Name="Qualifier">
<Value Display="Seller's Invoice Number" Function="UnknownItem">IV</Value>
</Item>
<Item Name="Number">
<Value>ERS Invoice37209</Value>
</Item>
</Group>
</Form>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Invoice" Function="UnknownItem">003</Value>
</Item>
<Item Name="Date">
<Value>20061019</Value>
</Item>
</Group>
</Form>
<Form Name="ItemDetail">
<Item Name="Quantity">
<Value>2000</Value>
</Item>
<Item Name="UnitPrice">
<Value>0.0134</Value>
</Item>
<Item Name="BuyerPartNum" PxrTheirPartNumber="1">
<Value>0001072000</Value>
</Item>
<Form Name="ReferenceDetail">
<Item Name="Qualifier">
<Value Display="Order Number" Function="UnknownItem">OQ</Value>
</Item>
<Item Name="Number">
<Value>3725789</Value>
</Item>
<Item Name="Qualifier2">
<Value Display="Check Number">CK</Value>
</Item>
<Item Name="Number2">
<Value>1166481</Value>
</Item>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Received" Function="ReceivedDate">050</Value>
</Item>
<Item Name="Date">
<Value>20061004</Value>
</Item>
</Group>
</Form>
</Form>
</Form>
</Form>
<Form Name="RMItemDetail">
<Item Name="RefIDQual">
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
</Item>
<Item Name="RefID">
<Value>090838</Value>
</Item>
<Item Name="PaymentActionCode">
<Value Display="Pay Item">PI</Value>
</Item>
<Item Name="PaymentAmount">
<Value>5</Value>
</Item>
<Form Name="ReferenceNums">
<Group>
<Item Name="Qualifier">
<Value Display="Purchase Order Number" Function="PONum">PO</Value>
</Item>
<Item Name="Number">
<Value>00300000</Value>
</Item>
</Group>
<Group>
<Item Name="Qualifier">
<Value Display="Seller's Invoice Number" Function="UnknownItem">IV</Value>
</Item>
<Item Name="Number">
<Value>ERS Invoice37209</Value>
</Item>
</Group>
</Form>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Invoice" Function="UnknownItem">003</Value>
</Item>
<Item Name="Date">
<Value>20061019</Value>
</Item>
</Group>
</Form>
<Form Name="ItemDetail">
<Item Name="Quantity">
<Value>1000</Value>
</Item>
<Item Name="UnitPrice">
<Value>0.005</Value>
</Item>
<Item Name="BuyerPartNum" PxrTheirPartNumber="1">
<Value>0001087000</Value>
</Item>
<Form Name="ReferenceDetail">
<Item Name="Qualifier">
<Value Display="Order Number" Function="UnknownItem">OQ</Value>
</Item>
<Item Name="Number">
<Value>3725789</Value>
</Item>
<Item Name="Qualifier2">
<Value Display="Check Number">CK</Value>
</Item>
<Item Name="Number2">
<Value>1166481</Value>
</Item>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Received" Function="ReceivedDate">050</Value>
</Item>
<Item Name="Date">
<Value>20061004</Value>
</Item>
</Group>
</Form>
</Form>
</Form>
</Form>
<Form Name="RMItemDetail">
<Item Name="RefIDQual">
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
</Item>
<Item Name="RefID">
<Value>090835</Value>
</Item>
<Item Name="PaymentActionCode">
<Value Display="Pay Item">PI</Value>
</Item>
<Item Name="PaymentAmount">
<Value>132</Value>
</Item>
<Form Name="ReferenceNums">
<Group>
<Item Name="Qualifier">
<Value Display="Purchase Order Number" Function="PONum">PO</Value>
</Item>
<Item Name="Number">
<Value>00300000</Value>
</Item>
</Group>
<Group>
<Item Name="Qualifier">
<Value Display="Seller's Invoice Number" Function="UnknownItem">IV</Value>
</Item>
<Item Name="Number">
<Value>ERS Invoice37209</Value>
</Item>
</Group>
</Form>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Invoice" Function="UnknownItem">003</Value>
</Item>
<Item Name="Date">
<Value>20061019</Value>
</Item>
</Group>
</Form>
<Form Name="ItemDetail">
<Item Name="Quantity">
<Value>500</Value>
</Item>
<Item Name="UnitPrice">
<Value>0.264</Value>
</Item>
<Item Name="BuyerPartNum" PxrTheirPartNumber="1">
<Value>0001134000</Value>
</Item>
<Form Name="ReferenceDetail">
<Item Name="Qualifier">
<Value Display="Order Number" Function="UnknownItem">OQ</Value>
</Item>
<Item Name="Number">
<Value>3725786</Value>
</Item>
<Item Name="Qualifier2">
<Value Display="Check Number">CK</Value>
</Item>
<Item Name="Number2">
<Value>1166481</Value>
</Item>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Received" Function="ReceivedDate">050</Value>
</Item>
<Item Name="Date">
<Value>20061004</Value>
</Item>
</Group>
</Form>
</Form>
</Form>
</Form>
<Form Name="RMItemDetail">
<Item Name="RefIDQual">
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
</Item>
<Item Name="RefID">
<Value>090835</Value>
</Item>
<Item Name="PaymentActionCode">
<Value Display="Pay Item">PI</Value>
</Item>
<Item Name="PaymentAmount">
<Value>73.32</Value>
</Item>
<Form Name="ReferenceNums">
<Group>
<Item Name="Qualifier">
<Value Display="Purchase Order Number" Function="PONum">PO</Value>
</Item>
<Item Name="Number">
<Value>00300000</Value>
</Item>
</Group>
<Group>
<Item Name="Qualifier">
<Value Display="Seller's Invoice Number" Function="UnknownItem">IV</Value>
</Item>
<Item Name="Number">
<Value>ERS Invoice37209</Value>
</Item>
</Group>
</Form>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Invoice" Function="UnknownItem">003</Value>
</Item>
<Item Name="Date">
<Value>20061019</Value>
</Item>
</Group>
</Form>
<Form Name="ItemDetail">
<Item Name="Quantity">
<Value>50</Value>
</Item>
<Item Name="UnitPrice">
<Value>1.4664</Value>
</Item>
<Item Name="BuyerPartNum" PxrTheirPartNumber="1">
<Value>0001140000</Value>
</Item>
<Form Name="ReferenceDetail">
<Item Name="Qualifier">
<Value Display="Order Number" Function="UnknownItem">OQ</Value>
</Item>
<Item Name="Number">
<Value>3725786</Value>
</Item>
<Item Name="Qualifier2">
<Value Display="Check Number">CK</Value>
</Item>
<Item Name="Number2">
<Value>1166481</Value>
</Item>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Received" Function="ReceivedDate">050</Value>
</Item>
<Item Name="Date">
<Value>20061004</Value>
</Item>
</Group>
</Form>
</Form>
</Form>
</Form>
<Form Name="RMItemDetail">
<Item Name="RefIDQual">
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
</Item>
<Item Name="RefID">
<Value>090833</Value>
</Item>
<Item Name="PaymentActionCode">
<Value Display="Pay Item">PI</Value>
</Item>
<Item Name="PaymentAmount">
<Value>6.57</Value>
</Item>
<Form Name="ReferenceNums">
<Group>
<Item Name="Qualifier">
<Value Display="Purchase Order Number" Function="PONum">PO</Value>
</Item>
<Item Name="Number">
<Value>00300000</Value>
</Item>
</Group>
<Group>
<Item Name="Qualifier">
<Value Display="Seller's Invoice Number" Function="UnknownItem">IV</Value>
</Item>
<Item Name="Number">
<Value>ERS Invoice37209</Value>
</Item>
</Group>
</Form>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Invoice" Function="UnknownItem">003</Value>
</Item>
<Item Name="Date">
<Value>20061019</Value>
</Item>
</Group>
</Form>
<Form Name="ItemDetail">
<Item Name="Quantity">
<Value>140</Value>
</Item>
<Item Name="UnitPrice">
<Value>0.0469</Value>
</Item>
<Item Name="BuyerPartNum" PxrTheirPartNumber="1">
<Value>0003035000</Value>
</Item>
<Form Name="ReferenceDetail">
<Item Name="Qualifier">
<Value Display="Order Number" Function="UnknownItem">OQ</Value>
</Item>
<Item Name="Number">
<Value>3725784</Value>
</Item>
<Item Name="Qualifier2">
<Value Display="Check Number">CK</Value>
</Item>
<Item Name="Number2">
<Value>1166481</Value>
</Item>
<Form Name="DateTime">
<Group>
<Item Name="Qualifier">
<Value Display="Received" Function="ReceivedDate">050</Value>
</Item>
<Item Name="Date">
<Value>20061004</Value>
</Item>
</Group>
</Form>
</Form>
</Form>
</Form>
<Form Name="RMItemDetail">
<Item Name="RefIDQual">
<Value Display="Shipper's Identifying Number for Shipmen">SI</Value>
</Item>
<Item Name="RefID">
<Value>090833</Value>
</Item>
<Item Name="PaymentActionCode">
<Value Display="Pay Item">PI</Value>
</Item>

Posttop
M KlineSubject: Re: XQuery sort & extract data
Author: M Kline
Date: 26 Oct 2006 08:32 PM

Hi Ivan

Here's the attachment.

Thanks much!

Mary

(See attached file: 820Copy.xml)


|---------+--------------------------------------------------->
| | "stylus-studio-xquery Listmanager" |
| | <stylus-studio-xquery.listmanager@stylus|
| | studio.com> |
| | |
| | 10/25/2006 10:49 PM |
| | Please respond to "XQuery Help and |
| | Discussion" |
|---------+--------------------------------------------------->
>---------------------------------------------------------------------------------------------------------------------------------------------|
| |
| To: (Recipients of 'stylus-studio-xquery' suppressed) |
| cc: |
| Subject: XQuery sort & extract data |
>---------------------------------------------------------------------------------------------------------------------------------------------|




From: Ivan Pedruzzi

Hi Mary,

Could you post a sample of your input document?


Ivan Pedruzzi
Stylus Studio Team
http://www.stylusstudio.com/xml_download.html


--
To reply:stylus-studio-xquery.17322@stylusstudio.com
To start a new topic:stylus-studio-xquery@stylusstudio.com
To login:http://www.stylusstudio.com/SSDN/
To (un)subscribe:stylus-studio-xquery.list-request@stylusstudio.com



Unknown820Copy.xml

 
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.