Re: [xsl] Solution Needed for DB output

["G. Ken Holman" <gkholman@xxxxxxxxxxxxxxxxxxxx>]

At 2009-04-18 10:42 -0700, balaganesh mohan wrote:
> I get the following collection as the output when querying oracle database
> with complex query.

Your content is not well formed.

> <getMatrixOutputCllection>
> <outputVar>
> <level_id>1<level_id>
> <assignee>jcooper<assignee>
> <reg>1000<reg>
> <gold>2000<gold>
> <sublevel>1<sublevel>
> <status>approve<status>
> <outputVar>
>
> <outputVar>
> <level_id>1<level_id>
> <assignee>jcooper<assignee>
> <reg>1000<reg>
> <gold>2000<gold>
> <sublevel>1<sublevel>
> <status>reject<status>
> <outputVar>
>
> <outputVar>
> <level_id>1<level_id>
> <assignee>jcooper<assignee>
> <reg>1000<reg>
> <gold>2000<gold>
> <sublevel>2<sublevel>
> <status>pending<status>
> <outputVar>
>
>
> <outputVar>
> <level_id>1<level_id>
> <assignee>jcooper<assignee>
> <reg>1000<reg>
> <gold>2000<gold>
> <sublevel>3<sublevel>
> <status>yes<status>
> <outputVar>
>
> <outputVar>
> <level_id>1<level_id>
> <assignee>jcooper<assignee>
> <reg>1000<reg>
> <gold>2000<gold>
> <sublevel>3<sublevel>
> <status>no<status>
> <outputVar>
>
>
> <outputVar>
> <level_id>2<level_id>
> <assignee>jstein<assignee>
> <reg>6000<reg>
> <gold>4000<gold>
> <sublevel>1<sublevel>
> <status>go<status>
> <outputVar>
>
>
> <outputVar>
> <level_id>2<level_id>
> <assignee>jstein<assignee>
> <reg>6000<reg>
> <gold>4000<gold>
> <sublevel>2<sublevel>
> <status>proceed<status>
> <outputVar>
>
> <outputVar>
> <level_id>2<level_id>
> <assignee>jstein<assignee>
> <reg>6000<reg>
> <gold>4000<gold>
> <sublevel>2<sublevel>
> <status>pending<status>
> <outputVar>
> <getMatrixOutputCllection>
>
> I need the output should be ordered accordingly the level_id as follows,
>
> <----desired output as follows----->

Your desired output looks like it is supposed to be a well-formed 
external parsed general entity, not an XML document.  And yet it, 
too, is not well formed.

> <level_id>1<level_id>
> <assignee>jcooper<assignee>
> <reg>1000<reg>
> <gold>2000<gold>
>
> <sublevel>1<sublevel>
> <status>approve<status>

What happened to <status>reject</status>?  Are these to be removed?

> <sublevel>2<sublevel>
> <status>pending<status>
>
> <sublevel>3<sublevel>
> <status>yes<status>
> <status>no<status>
>
> <level_id>2<level_id>
> <assignee>jstein<assignee>
> <reg>6000<reg>
> <gold>4000<gold>
>
> <sublevel>1<sublevel>
> <status>go<status>
>
> <sublevel>2<sublevel>
> <status>proceed<status>
> <status>pending<status>
>
> <--end--->
>
> NOTE
> ----
> The output will yield more repeat collections according to the table
> values.(i.e)<level_id>1<level_id> may contain more <sublevel> and
> more relative <status> elements.
> But the values of <reg>,<gold>and<assignee>will be same for the 
> particular <level_id>.
>
> I am very new to xslt  and i know basics of xslt only.
> I tried to achieve the above output via key() function as explained
> in your Mueneuch -2 ( grouping - thread)method. But i get the same
> output as the db output.

You have a multi-level grouping issue, and I find that is addressed 
more easily with variables than with the key() function.

> I use jdeveloper as designing tool and oracle soa suite 10.1.3.1.0
> as server and xslt version 1.0 ( i dont know whether xslt 2.0 is
> supported in soa server)
>
> So kindly request you to provide solution for the above scenario.

I am unsure why you are asking for a complete solution rather than 
just asking questions regarding where you are having problems 
understanding the operation of XSLT.

Thankfully an answer is very easy to produce, but you do not make it 
easy for volunteers to help you when you do not provide useable inputs.

. . . . . . . . . Ken


T:\ftemp>type balaganesh.xml
<getMatrixOutputCllection>
<outputVar>
<level_id>1</level_id>
<assignee>jcooper</assignee>
<reg>1000</reg>
<gold>2000</gold>
<sublevel>1</sublevel>
<status>approve</status>
</outputVar>

<outputVar>
<level_id>1</level_id>
<assignee>jcooper</assignee>
<reg>1000</reg>
<gold>2000</gold>
<sublevel>1</sublevel>
<status>reject</status>
</outputVar>

<outputVar>
<level_id>1</level_id>
<assignee>jcooper</assignee>
<reg>1000</reg>
<gold>2000</gold>
<sublevel>2</sublevel>
<status>pending</status>
</outputVar>


<outputVar>
<level_id>1</level_id>
<assignee>jcooper</assignee>
<reg>1000</reg>
<gold>2000</gold>
<sublevel>3</sublevel>
<status>yes</status>
</outputVar>

<outputVar>
<level_id>1</level_id>
<assignee>jcooper</assignee>
<reg>1000</reg>
<gold>2000</gold>
<sublevel>3</sublevel>
<status>no</status>
</outputVar>


<outputVar>
<level_id>2</level_id>
<assignee>jstein</assignee>
<reg>6000</reg>
<gold>4000</gold>
<sublevel>1</sublevel>
<status>go</status>
</outputVar>


<outputVar>
<level_id>2</level_id>
<assignee>jstein</assignee>
<reg>6000</reg>
<gold>4000</gold>
<sublevel>2</sublevel>
<status>proceed</status>
</outputVar>

<outputVar>
<level_id>2</level_id>
<assignee>jstein</assignee>
<reg>6000</reg>
<gold>4000</gold>
<sublevel>2</sublevel>
<status>pending</status>
</outputVar>
</getMatrixOutputCllection>

T:\ftemp>call xslt balaganesh.xml balaganesh.xsl
<?xml version="1.0" encoding="utf-8"?>
<level_id>1</level_id>
<assignee>jcooper</assignee>
<reg>1000</reg>
<gold>2000</gold>

<sublevel>1</sublevel>
<status>approve</status>
<status>reject</status>

<sublevel>2</sublevel>
<status>pending</status>

<sublevel>3</sublevel>
<status>yes</status>
<status>no</status>

<level_id>2</level_id>
<assignee>jstein</assignee>
<reg>6000</reg>
<gold>4000</gold>

<sublevel>1</sublevel>
<status>go</status>

<sublevel>2</sublevel>
<status>proceed</status>
<status>pending</status>

T:\ftemp>type balaganesh.xsl
<?xml version="1.0" encoding="US-ASCII"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform";
                version="1.0">

<xsl:output indent="yes"/>

<xsl:key name="level" match="outputVar" use="level_id"/>

<xsl:template match="getMatrixOutputCllection">
  <xsl:for-each select="outputVar[generate-id(.)=
                                  generate-id(key('level',level_id)[1])]">
    <xsl:copy-of select="level_id"/>
    <xsl:copy-of select="assignee"/>
    <xsl:copy-of select="reg"/>
    <xsl:copy-of select="gold"/>
    <xsl:text>
</xsl:text>
    <xsl:variable name="vars-by-sublevel" select="key('level',level_id)"/>
    <xsl:for-each select="$vars-by-sublevel">
      <xsl:if test="generate-id(.)=
                    generate-id($vars-by-sublevel
                                [sublevel=current()/sublevel][1])">
        <xsl:copy-of select="sublevel"/>
        <xsl:copy-of select="$vars-by-sublevel
                             [sublevel=current()/sublevel]/status"/>
        <xsl:text>
</xsl:text>
      </xsl:if>
    </xsl:for-each>
  </xsl:for-each>
</xsl:template>

</xsl:stylesheet>

T:\ftemp>rem Done!



--
XSLT/XSL-FO/XQuery hands-on training - Los Angeles, USA 2009-06-08
Training tools: Comprehensive interactive XSLT/XPath 1.0/2.0 video
Video lesson:    http://www.youtube.com/watch?v=PrNjJCh7Ppg&fmt=18
Video overview:  http://www.youtube.com/watch?v=VTiodiij6gE&fmt=18
G. Ken Holman                 mailto:gkholman@xxxxxxxxxxxxxxxxxxxx
Crane Softwrights Ltd.          http://www.CraneSoftwrights.com/s/
Male Cancer Awareness Nov'07  http://www.CraneSoftwrights.com/s/bc
Legal business disclaimers:  http://www.CraneSoftwrights.com/legal