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
Conferences Close Tree View
+ Stylus Studio Feature Requests (1192)
+ Stylus Studio Technical Forum (14621)
+ Website Feedback (249)
+ XSLT Help and Discussion (7625)
- XQuery Help and Discussion (2016)
-> + Problems with FTP upload via X... (3)
-> + What path notation works when ... (6)
-> - Xquery Exception (1)
-> + [java] [Fatal Error] Test.xml:... (2)
-> + unable to produce correct XML ... (2)
-> + where can I find stylusxql uti... (3)
-> + Trouble Mapping Multiple Eleme... (3)
-> - input parameter (1)
-> - set element value (1)
-> + Xquery Performance Issue (21)
-> + Run Xquery Program using Java (3)
-> + XQuery XML to CSV (3)
-> + XQuery MySQL connector (4)
-> + XQuery with JDBC connection is... (3)
-> + Create FTP Server (2)
-> + XQuery Nested Loop Lookup (5)
-> + Xpath using a Variable for [@I... (2)
-> - My xqDoc option is disabled. (1)
-> + Xpath ancestor selection (4)
-> + Exception: sequence of more th... (2)
-- [1-20] [21-40] [41-60] Next
+ Stylus Studio FAQs (159)
+ Stylus Studio Code Samples & Utilities (364)
+ Stylus Studio Announcements (113)
Topic  
Postnext
Roel van der HoevenSubject: Outer joins
Author: Roel van der Hoeven
Date: 15 Oct 2012 04:38 AM
I'm having a bit of trouble with outer joins and calculating with output from them.

Lets say I have an XML ($input) with the following content:

<?xml version="1.0" encoding="UTF-8"?>
<some>
<path>
<list>
<listA>
<listType>A</listType>
<value1>3</value1>
<value2>0</value2>
<value3>5</value3>
<value4>2</value4>
</listA>
<listA>
<listType>B</listType>
<value1>1</value1>
<value2>2</value2>
<value3>3</value3>
<value4>2</value4>
</listA>
<listA>
<listType>C</listType>
<value1>2</value1>
<value2>2</value2>
<value3>0</value3>
<value4>1</value4>
</listA>
<listB>
<listType>A</listType>
<value1>3</value1>
<value2>0</value2>
<value3>5</value3>
<value4>2</value4>
</listB>
<listB>
<listType>B</listType>
<value1>1</value1>
<value2>2</value2>
<value3>3</value3>
<value4>2</value4>
</listB>
<listB>
<listType>C</listType>
<value1>2</value1>
<value2>2</value2>
<value3>0</value3>
<value4>1</value4>
</listB>
<listB>
<listType>D</listType>
<value1>2</value1>
<value2>2</value2>
<value3>0</value3>
<value4>1</value4>
</listB>
<listB>
<listType>E</listType>
<value1>2</value1>
<value2>2</value2>
<value3>0</value3>
<value4>1</value4>
</listB>
</list>
</path>
</some>

I want to loop over listA and listB and group them by listType. In the output I'll add the values together, so it'll be something like:

<some>
<path>
<list>
<listX>
<listType>[UniqueListType]</listType>
<value1>[sum(listA.value1 + listB.value1)]</value1>
<value2>[sum(listA.value2 + listB.value2)]</value2>
<value3>[sum(listA.value3 + listB.value3)]</value3>
<value4>[sum(listA.value4 + listB.value4)]</value4>
</listX>
</list>
</path>
</some>

Any thoughts?

Postnext
Ivan PedruzziSubject: Outer joins
Author: Ivan Pedruzzi
Date: 15 Oct 2012 10:26 AM
Hi Roel,

See if the following query helps

Ivan Pedruzzi
Stylus Studio Team


declare variable $input1 as document-node(element(*, xs:untyped)) external;

<some>
<path>
<list>
<listX>
{
for $listType in distinct-values($input1/some/path/list/*/listType)
return
let $listX := $input1/some/path/list/*[listType = $listType]
return
(<listType>{ $listType }</listType>,
for $value in $listX[1]/*[local-name() != 'listType'],
$name in name($value)
return element { $name } {sum($listX/*[name() = $name])})
}
</listX>
</list>
</path>
</some>






--------------------------------------
Result

<some>
<path>
<list>
<listX>
<listType>A</listType>
<value1>6</value1>
<value2>0</value2>
<value3>10</value3>
<value4>4</value4>
<listType>B</listType>
<value1>2</value1>
<value2>4</value2>
<value3>6</value3>
<value4>4</value4>
<listType>C</listType>
<value1>4</value1>
<value2>4</value2>
<value3>0</value3>
<value4>2</value4>
<listType>D</listType>
<value1>2</value1>
<value2>2</value2>
<value3>0</value3>
<value4>1</value4>
<listType>E</listType>
<value1>2</value1>
<value2>2</value2>
<value3>0</value3>
<value4>1</value4>
</listX>
</list>
</path>
</some>



Postnext
Roel van der HoevenSubject: Outer joins
Author: Roel van der Hoeven
Date: 17 Oct 2012 04:16 AM
Ivan,

Thanks for your input. It has helped me a bit in understanding the base. Still I can't seem to get it to work in my situation. Attached is the actual input. The elements I need to loop over are in a loop with variable $xlCargoDetails called xlBerthVisitContainerHandling and xlBerthVisitFlatHandling. They have children xlEquipmentCategoryCode (key field), xlNumberFullEquipmentLoaded, xlNumberEmptyEquipmentLoaded, xlNumberFullEquipmentDischarged and xlNumberEmptyEquipmentDischarged.

This will have to be transformed in the output as berthVisitEquipmentHandling with children equipmentCategoryCode (the former key field), numberFullLoaded, numberEmptyLoaded, numberFullDischarged and numberEmptyDischarged.


DocumentOZA-704.xml

Posttop
Ivan PedruzziSubject: Outer joins
Author: Ivan Pedruzzi
Date: 17 Oct 2012 04:08 PM

The following query does what you have described, it also outputs the input document structure

Hope this helps
Ivan Pedruzzi
Stylus Studio Team



declare variable $input1 as document-node(element(*, xs:untyped)) external;

<xlPortHarbourDuesStatement>
{
for $xlPortHarbourDuesStatement in $input1/xlPortHarbourDuesStatement
return
<xlPortHarbourDuesStatement>{
for $xlBerthInformation in $input1/xlPortHarbourDuesStatement/xlBerthInformation
return
<xlBerthInformation>{(
$xlBerthInformation/xlSequenceNumber,
for $xlCargoDetails in $xlBerthInformation/xlCargoDetails
return
<xlCargoDetails>{
for $xlEquipmentCategoryCode in distinct-values($xlCargoDetails/*/xlEquipmentCategoryCode)
return
let $xlBertVisitHandling := $xlCargoDetails/*[xlEquipmentCategoryCode = $xlEquipmentCategoryCode]
return
<berthVisitEquipmentHandling>
<equipmentCategoryCode>{$xlEquipmentCategoryCode}</equipmentCategoryCode>
<numberFullLoaded>{sum($xlBertVisitHandling/*[name() = "xlNumberFullEquipmentLoaded"])}</numberFullLoaded>
<numberEmptyLoaded>{sum($xlBertVisitHandling/*[name() = "xlNumberEmptyEquipmentLoaded"])}</numberEmptyLoaded>
<numberFullDischarged>{sum($xlBertVisitHandling/*[name() = "xlNumberFullEquipmentDischarged"])}</numberFullDischarged>
<numberEmptyDischarged>{sum($xlBertVisitHandling/*[name() = "xlNumberEmptyEquipmentDischarged"])}</numberEmptyDischarged>
</berthVisitEquipmentHandling >
}</xlCargoDetails>
)}</xlBerthInformation>
}</xlPortHarbourDuesStatement>
}
</xlPortHarbourDuesStatement>


------------------------------------
result


<xlPortHarbourDuesStatement>
<xlPortHarbourDuesStatement>
<xlBerthInformation>
<xlSequenceNumber>1</xlSequenceNumber>
<xlCargoDetails>
<berthVisitEquipmentHandling>
<equipmentCategoryCode>20FT</equipmentCategoryCode>
<numberFullLoaded>2</numberFullLoaded>
<numberEmptyLoaded>2</numberEmptyLoaded>
<numberFullDischarged>2</numberFullDischarged>
<numberEmptyDischarged>2</numberEmptyDischarged>
</berthVisitEquipmentHandling>
</xlCargoDetails>
</xlBerthInformation>
<xlBerthInformation>
<xlSequenceNumber>2</xlSequenceNumber>
<xlCargoDetails>
<berthVisitEquipmentHandling>
<equipmentCategoryCode>20FT</equipmentCategoryCode>
<numberFullLoaded>40</numberFullLoaded>
<numberEmptyLoaded>40</numberEmptyLoaded>
<numberFullDischarged>40</numberFullDischarged>
<numberEmptyDischarged>40</numberEmptyDischarged>
</berthVisitEquipmentHandling>
<berthVisitEquipmentHandling>
<equipmentCategoryCode>25FT</equipmentCategoryCode>
<numberFullLoaded>50</numberFullLoaded>
<numberEmptyLoaded>50</numberEmptyLoaded>
<numberFullDischarged>50</numberFullDischarged>
<numberEmptyDischarged>50</numberEmptyDischarged>
</berthVisitEquipmentHandling>
<berthVisitEquipmentHandling>
<equipmentCategoryCode>30FT</equipmentCategoryCode>
<numberFullLoaded>30</numberFullLoaded>
<numberEmptyLoaded>30</numberEmptyLoaded>
<numberFullDischarged>30</numberFullDischarged>
<numberEmptyDischarged>30</numberEmptyDischarged>
</berthVisitEquipmentHandling>
<berthVisitEquipmentHandling>
<equipmentCategoryCode>35FT</equipmentCategoryCode>
<numberFullLoaded>35</numberFullLoaded>
<numberEmptyLoaded>35</numberEmptyLoaded>
<numberFullDischarged>35</numberFullDischarged>
<numberEmptyDischarged>35</numberEmptyDischarged>
</berthVisitEquipmentHandling>
<berthVisitEquipmentHandling>
<equipmentCategoryCode>40FT</equipmentCategoryCode>
<numberFullLoaded>40</numberFullLoaded>
<numberEmptyLoaded>40</numberEmptyLoaded>
<numberFullDischarged>40</numberFullDischarged>
<numberEmptyDischarged>40</numberEmptyDischarged>
</berthVisitEquipmentHandling>
<berthVisitEquipmentHandling>
<equipmentCategoryCode>45FT</equipmentCategoryCode>
<numberFullLoaded>45</numberFullLoaded>
<numberEmptyLoaded>45</numberEmptyLoaded>
<numberFullDischarged>45</numberFullDischarged>
<numberEmptyDischarged>45</numberEmptyDischarged>
</berthVisitEquipmentHandling>
</xlCargoDetails>
</xlBerthInformation>
</xlPortHarbourDuesStatement>
</xlPortHarbourDuesStatement>

   
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.