SSDN - XQuery sort & extract data

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Conferences

	Stylus Studio Feature Requests (1192)
	Stylus Studio Technical Forum (14621)
	Website Feedback (249)
	XSLT Help and Discussion (7625)
	XQuery Help and Discussion (2017)
	Issue with Processing Instruct... (2)
	problem converting json to XML... (2)
	Problem base64 decoding string... (3)
	Problems posting multipart for... (5)
	trouble with download of price... (2)
	Problem with http-post not bei... (3)
	path problem, xps_file:writeAl... (9)
	Xquery update support? (2)
	problem with Stylus studio try... (5)
	adding dtd reference to xml ou... (4)
	xquery escaping ambarsand when... (3)
	Whitespace problem when return... (5)
	Problem with namespace prefix ... (5)
	Sending via SFTP returns unexp... (1)
	Query and Sftp clent (4)
	xquery and try - catch (3)
	Query + ddtek:http-post optio... (5)
	Example files referenced in do... (3)
	Automatic Error Detection and ... (3)
	Working with result of ddtek:h... (2)
	[1-20] [21-40] [41-60] Next
	Stylus Studio FAQs (159)
	Stylus Studio Code Samples & Utilities (364)
	Stylus Studio Announcements (113)

Topic

M Kline

Subject: 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">

<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 & 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

Ivan Pedruzzi

Subject: 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

M Kline

Subject: 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 & 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>

M Kline

Subject: 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

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.