Re: [xsl] Solution Needed for DB output

Cart

XML Editor - Download a Free Trial >

See What's New >

Buy Now >

[Home] [By Thread] [By Date] [Recent Entries]

Subject: Re: Solution Needed for DB output
From: "G. Ken Holman" <gkholman@xxxxxxxxxxxxxxxxxxxx>
Date: Sat, 18 Apr 2009 14:05:58 -0400

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

Current Thread
Solution Needed for DB output balaganesh mohan - 18 Apr 2009 17:42:45 -0000 Martin Honnen - 18 Apr 2009 18:05:48 -0000 G. Ken Holman - 18 Apr 2009 18:09:07 -0000 <=

<- Previous	Index	Next ->
Re: Solution Needed for DB ou, Martin Honnen	Thread
Re: Solution Needed for DB ou, Martin Honnen	Date
	Month

XML Editor - Download a 15 Day Free Trial Now >

See What's New in Stylus Studio >

Buy Stylus Studio - XML Editor - Now >