Union Action Block

[Former Member]

When the Union action block adds a resultset to another resultset, it adds another <Rowset>/<Row> complex to the first resultset including another <Columns> definition as well. I know grids and that will work with this but it means that if I want to apply further actions to the merged resultset, I have to have logic that traverses the multiple rowsets.

I tried append xml like this -

Local.WorkStockpileXml{/Rowsets/Rowset} = MappedXML2_2ndLeg.Output{/Rowsets/Rowset/Row}

but this only appends one row.

Is there a way where I can put the second resultset rows into the same rowset as the first in one step (i.e. without having to loop)?

Cheers.

Message was edited by:

Laurie Paulin

Message was edited by:

Laurie Paulin

[sufw]

Hi Laurie,

in addition to Rick's suggestion, if both Rowsets contain time-series data (i.e. have a DateTime column with time stamps), you can use the TimeInterpolator action to merge them even if they have a different number of rows.

Sascha

[Former Member]

Rick/Sascha,

Thanks for both your answers. I probably didn't explain myself too well.

The Normalize and Time Interpolator Action blocks merge rowsets "across" the columns.

I have two rowsets like this:

<Rowsets>
  <Rowset>
     <Row>Row1</Row>
     <Row>Row2</Row>
     <Row>Row3</Row>
  </Rowset>
</Rowsets>

and

<Rowsets>
  <Rowset>
     <Row>Row4</Row>
     <Row>Row5</Row>
  </Rowset>
</Rowsets>

I want them merged like this:

<Rowsets>
  <Rowset>
     <Row>Row1</Row>
     <Row>Row2</Row>
     <Row>Row3</Row>
     <Row>Row4</Row>
     <Row>Row5</Row>
  </Rowset>
</Rowsets>

and not like this:

<Rowsets>
  <Rowset>
     <Row>Row1</Row>
     <Row>Row2</Row>
     <Row>Row3</Row>
  </Rowset>
  <Rowset>
     <Row>Row4</Row>
     <Row>Row5</Row>
  </Rowset>
</Rowsets>

and I want to do it one pass without having to loop.

The Union action block creates it the last way with multiple Rowsets in the hierarchy.

An XPath expression like this

Local.WorkStockpileXml{/Rowsets/Rowset} = MappedXML2_2ndLeg.Output{/Rowsets/Rowset/Row}

only copies the first Row node. (Aside,this might be a bug, as the expression "MappedXML2_2ndLeg.Output{/Rowsets/Rowset/Row}" should return a collection?)

I am sure there must a way to do this in an Append XML link.

Cheers for anyone who waded through this lengthy post.

[sufw]

Hi Laurie,

sorry for the misunderstanding. I can't think of any way of appending one dataset to another that doesn't involve some kind of loop, but you can use the AppendXML assignment type inside a loop:

First save the larger document to a local property (no need for a Union here).

Then run a repeater over each row in the smaller document and do the following assignment:

Local.MergedDoc{/Rowsets/Rowset/Row[last()]} = Document_1.Output{/Rowsets/Rowset/Row[#Repeater_0.CurrentItem#]}

Make sure the assignment type is set to "Append After XML".

As your XPath issue, I think that xMII implicitly wraps your XPath statement in the XPath string() function and thus always returns the <i>content</i> of a node, rather than the node itself. According to the XPath specs, /Rowsets/Rowset/Row[last()] would return an XML node, but xMII usually makes that expression return its content. This is just me taking a stab in the dark as I wasn't involved in the design of the application.

Rick, Jeremy, can you shed any light on this?

Sascha

[Former Member]

Should

Local.WorkStockpileXml{/Rowsets/Rowset} = MappedXML2_2ndLeg.Output{/Rowsets/Rowset/Row}

be

Local.WorkStockpileXml{/Rowsets/Rowset} = MappedXML2_2ndLeg.Output{/Rowsets/Rowset}?

The first line will only grab one row (1st in the Java Xacute, last in the .NET Xacute). Don't you want to assign the full Rowset to the Local variable?

Message was edited by:

Ryan Miller

[Former Member]

Actually doing this will nest a Rowset in a Rowset, something you don't want. Interesting...

It's almost like we want an "Overlay and Combine" function instead of Append or Replace.

Ah! What if you create an XSL stylesheet and use the XSLTransform block to combine the two? But, that will require an xsl:for-each (looping)... nevermind.

[jcgood25]

Laurie,

What is the source of your two data sets? Not to overlook the obvious, but if they are coming from the same source it may be just as easy to do this 'unioning' in the original query.

Here is the syntax for what the xslt Ryan mentioned would look like (already had one handy

<?xml version="1.0" encoding="UTF-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<Rowsets DateCreated="{Rowsets/@DateCreated}" Version="{Rowsets/@Version}" StartDate="{Rowsets/@StartDate}" EndDate="{Rowsets/@EndDate}">

<xsl:copy-of select="/Rowsets/FatalError"/>

<xsl:copy-of select="/Rowsets/Messages"/>

<xsl:copy-of select="/Rowsets/HyperLinks"/>

<xsl:if test="count(/Rowsets/FatalError) = '0'">

<Rowset>

<xsl:for-each select="Rowsets/Rowset">

<xsl:variable name="RowsetNo">

<xsl:value-of select="position()"/>

</xsl:variable>

<xsl:if test="$RowsetNo = 1">

<xsl:copy-of select="Columns"/>

</xsl:if>

<xsl:copy-of select="Row"/>

</xsl:for-each>

</Rowset>

</xsl:if>

</Rowsets>

</xsl:template>

</xsl:stylesheet>

If you save this as an xsl file on your webserver you can use it with a simple http://localhost/ProjectFolder/MultipleRowsetCombiner.xsl call in an XSLT Transformation action block, where you link the results of your Union action block into the Input. The Output will be your desired single Rowset document with the rows being merged together.

Regards,

Jeremy Good

[Former Member]

Thanks for the posts. I was looking for a performance optimised approach without having to loop and/or use XSL. I have just pushed it back to the database with a "union all" clause which performs okay.

The technique is for a "to-and-from" view of iron ore shipped to a port from a mine(with different mappings on each side), i.e. the same resultset doubled up. I thought it would be better to have the least amount of rows returned to the trx and then duplicate, map and merge in memory, however, there seems to be little difference between the two approaches.

Thanks to all.

[sufw]

Hi Laurie,

in my opinion this is the preferred approach anyways since the source system should always be more efficient for operations on its own native data such as unions, sums, etc.

Hope your implementation is going well

Sascha

[jcgood25]

Laurie,

The choices that xMII offers from a flexible solution standpoint presents challenges like this quite often.

In all cases if you can do the work, like your sql union query (I suspected it was related to the previous issue I helped you with), in the native data source that should be optimal. If the underlying source system only gives you only a few basic options (on none at all) for returning data, then combinations of the technologies like XSL, BLS, etc. can be applied in the resulting solution, but the same combinations and order are not always applicable to every situation.

Glad to hear you have your solution working!

Regards,

Jeremy Good

[Former Member]

I found similar syntax in a transaction I inherited as shown in the line in your example that looks like this -- Local.MergedDoc{/Rowsets/Rowset/Row[last()]} = Document_1.Output{/Rowsets/Rowset/Row[#Repeater_0.CurrentItem#]}. I had not seen the Row[last()] notation nor the Row[#Repeater_0.CurrentItem#] notation which both indicate there is a way to explicitly reference a specific row in a rowset. I can't find this documented anywhere in the xMII BLS help. I was worried that it might be an undocumented and therefore unsupported feature. I certainly could make use of it but am hesitant to do so if I can't find documentation for it. Can you tell me where it is documented? Thanks!

[Former Member]

Hi Candy,

The last() method is a standard XSL method (not specific to xMII). The "#something#" is a dynamic link in xMII. Search for dynamic link in xMII and you should find some helpful lit.

[Former Member]

Hi, Laurie. This is what the "Normalize" action will do for you. The only requirement is that both resultsets have the same # of rows.