Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

XLST grouping

Posted on 2013-10-24
18
Medium Priority
?
194 Views
Last Modified: 2013-10-24
Hello Experts,

I have the below XLST I have :

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>


<xsl:template match="/">
<xsl:for-each select="/ROWSET/*">
  [
 CAP STATUS: <xsl:value-of select="CAP_SATUS"/>
  {
  <xsl:for-each select="./*">
    "<xsl:value-of select="name()"/>":"<xsl:value-of select="text()"/>"<xsl:choose>
      <xsl:when test="position()!= last()">,</xsl:when>
    </xsl:choose>
   </xsl:for-each>
  }]
  <xsl:choose>
      <xsl:when test="position() != last()">,</xsl:when>
    </xsl:choose>
   </xsl:for-each>
</xsl:template></xsl:stylesheet>

Open in new window


Now I want to group by all the columns with "CAP_STATUS"
0
Comment
Question by:Swadhin Ray
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
18 Comments
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39596845
Not having a test XML available...
Try this...

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="html"/>
    
    <xsl:key name="by-cap" match="*" use="CAP_SATUS"/>
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key('by-cap', CAP_SATUS )[1])]">
            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            [
            CAP STATUS: <xsl:value-of select="CAP_SATUS"/>
            <xsl:apply-templates select="key('by-cap', CAP_SATUS )" mode="grouped-cap"/>
            ]
        </xsl:for-each>
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-cap">
        <xsl:if test="not(position() = 1)">
            <xsl:text>,</xsl:text>
        </xsl:if>
        {
        <xsl:apply-templates select="*" mode="inner"/>
        }
    </xsl:template>

    <xsl:template match="*" mode="inner">
        <xsl:if test="not(position() = 1)">
            <xsl:text>,</xsl:text>
        </xsl:if>
        <xsl:text>"</xsl:text>
        <xsl:value-of select="name()"/>
        <xsl:text>":"</xsl:text>
        <xsl:value-of select="text()"/>
        <xsl:text>"</xsl:text>
    </xsl:template>
    
</xsl:stylesheet>

Open in new window


it always helps if you added a test xml
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39596870
Here is my test xml:

{<CAP_SATUS=Pre-Audit - Facility Profile Received,
VISIT_STATUS=3,
CITY=Shenzhen,
FACILITY_ID=4002,
FACILITY_NAME=AAC Acoustic Technologies Ltd. (American Audio Company)>
,
<CAP_SATUS=Pre-Audit - Facility Profile Received,
VISIT_STATUS=3,
CITY=Shenzhen,
FACILITY_ID=4002,
FACILITY_NAME=AAC Acoustic Technologies Ltd. (American Audio Company)>
,
<CAP_SATUS=CAP Requested PAST Due on,
VISIT_STATUS=9,
CITY=Shenzhen,
FACILITY_ID=5343,
FACILITY_NAME=Volex Cable Assembly Co. Ltd.>
}

Open in new window



And here is what I am expected as the result:

[
 CAP STATUS: CAP Requested PAST Due on
  {
  
    "CAP_SATUS":"CAP Requested PAST Due on",
    "VISIT_STATUS":"9",
    "CITY":"Shenzhen",
    "FACILITY_ID":"5343",
    "FACILITY_NAME":"Volex Cable Assembly Co. Ltd."
  }]
  ,
  [
 CAP STATUS: Pre-Audit - Facility Profile Received
  {
  
    "CAP_SATUS":"Pre-Audit - Facility Profile Received",
    "VISIT_STATUS":"3",
    "CITY":"Shenzhen",
    "FACILITY_ID":"4002",
    "FACILITY_NAME":"AAC Acoustic Technologies Ltd. (American Audio Company)"
  }
  ,
   {
  
    "CAP_SATUS":"Pre-Audit - Facility Profile Received",
    "VISIT_STATUS":"3",
    "CITY":"Shanghai",
    "FACILITY_ID":"5296",
    "FACILITY_NAME":"Universal Scientific Industrial Co. Ltd."
  }
  ]

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39596893
that source is NOT XML,
but I think the XSLT will do that for you.
Have you tested?
0
Major Serverless Shift

Comparison of major players like AWS, Microsoft Azure, IBM Bluemix, and Google Cloud Platform

 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39596895
Yes but not working ...

Thats a sys_refcursor output from Oracle.
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39596903
"not working" does not tell me much

was your original XSLT working? The one you posted?

XSLT requires XML as source, so there needs to be XML somewhere in your process
(could be a DOM object created from the stuff you show, serialise that DOM then)
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39596911
Yes the one i posted was working fine..

And my input file was :

{<CAP_SATUS=Pre-Audit - Facility Profile Received,
VISIT_STATUS=3,
CITY=Shenzhen,
FACILITY_ID=4002,
FACILITY_NAME=AAC Acoustic Technologies Ltd. (American Audio Company)>
,
<CAP_SATUS=Pre-Audit - Facility Profile Received,
VISIT_STATUS=3,
CITY=Shenzhen,
FACILITY_ID=4002,
FACILITY_NAME=AAC Acoustic Technologies Ltd. (American Audio Company)>
,
<CAP_SATUS=CAP Requested PAST Due on,
VISIT_STATUS=9,
CITY=Shenzhen,
FACILITY_ID=5343,
FACILITY_NAME=Volex Cable Assembly Co. Ltd.>
}

Open in new window


By using below xslt :

 lxsl := '<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>

<xsl:key name="group1"
           match="row"
           use="CAP_SATUS"/>


<xsl:template match="/">
<xsl:for-each select="/ROWSET/*">
  [
 CAP STATUS: <xsl:value-of select="CAP_SATUS"/>
  {
  <xsl:for-each select="./*">
    "<xsl:value-of select="name()"/>":"<xsl:value-of select="text()"/>"<xsl:choose>
      <xsl:when test="position()!= last()">,</xsl:when>
    </xsl:choose>
   </xsl:for-each>
  }]
  <xsl:choose>
      <xsl:when test="position() != last()">,</xsl:when>
    </xsl:choose>
   </xsl:for-each>
</xsl:template></xsl:stylesheet>';

Open in new window


I am getting the below output:

  [
 CAP STATUS: CAP Requested PAST Due on
  {
  
    "CAP_SATUS":"CAP Requested PAST Due on",
    "VISIT_STATUS":"9",
    "CITY":"Shenzhen",
    "FACILITY_ID":"5343",
    "FACILITY_NAME":"Volex Cable Assembly Co. Ltd."
  }]
  ,
  [
 CAP STATUS: Pre-Audit - Facility Profile Received
  {
  
    "CAP_SATUS":"Pre-Audit - Facility Profile Received",
    "VISIT_STATUS":"3",
    "CITY":"Shenzhen",
    "FACILITY_ID":"4002",
    "FACILITY_NAME":"AAC Acoustic Technologies Ltd. (American Audio Company)"
  }]
  ,
  [
 CAP STATUS: Pre-Audit - Facility Profile Received
  {
  
    "CAP_SATUS":"Pre-Audit - Facility Profile Received",
    "VISIT_STATUS":"3",
    "CITY":"Shanghai",
    "FACILITY_ID":"5296",
    "FACILITY_NAME":"Universal Scientific Industrial Co. Ltd."
  }]
  ,
  [
 CAP STATUS: Pre-Audit - Facility Profile Received
  {
  
    "CAP_SATUS":"Pre-Audit - Facility Profile Received",
    "VISIT_STATUS":"3",
    "CITY":"Shenzhen",
    "FACILITY_ID":"4002",
    "FACILITY_NAME":"AAC Acoustic Technologies Ltd. (American Audio Company)"
  }]
  ,
  [
 CAP STATUS: Pre-Audit - Facility Profile Received
  {
  
    "CAP_SATUS":"Pre-Audit - Facility Profile Received",
    "VISIT_STATUS":"3",
    "CITY":"Lutterworth",
    "FACILITY_ID":"5287",
    "FACILITY_NAME":"Unipart Technology Logistics"
  }]
  

Open in new window



Where I am looking for grouping it ...this is where I need some help.
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39596917
Strange, I hacked together some test XML based on your original XML like things,
and it works with my XSLT
Other than doing some clean up, essentialy the only difference between your XSLT and mine is that it does the grouping

Can you explain what you mean with "not working"
"not working" does not help when debugging without XML
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39596934
When I try to compile my oracle function it gives me this error :

Compilation errors for FUNCTION CAP.GET_JSON_FNC_VAL

Error: PLS-00103: Encountered the symbol "BY" when expecting one of the following:
       
          * & = - + ; < / > at in is mod remainder not rem
          <an exponent (**)> <> or != or ~= >= <= <> and or like like2
          like4 likec between || multiset member submultiset
Line: 48
Text: <xsl:for-each select="*[generate-id() = generate-id(key('by-cap', CAP_SATUS )[1])]">

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39596952
you see, that is something I can work with

the way you call the XSLT is likely embedded in a query, and the processor is choquing on the word "by"
no problem, it is a name, so you can safely remove the "by-" from the key name (twice in the stylesheet)
so make 'by-cap' become 'cap' and try again
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39597143
I am using as like below and my function got compiled but when I run my function I am not getting any records:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="html"/>
    
    <xsl:key name="by-cap" match="*" use="CAP_SATUS"/>
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key("cap", CAP_SATUS )[1])]">
            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            [
            CAP STATUS: <xsl:value-of select="CAP_SATUS"/>
            <xsl:apply-templates select="key("cap", CAP_SATUS )" mode="grouped-cap"/>
            ]
        </xsl:for-each>
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-cap">
        <xsl:if test="not(position() = 1)">
            <xsl:text>,</xsl:text>
        </xsl:if>
        {
        <xsl:apply-templates select="*" mode="inner"/>
        }
    </xsl:template>

    <xsl:template match="*" mode="inner">
        <xsl:if test="not(position() = 1)">
            <xsl:text>,</xsl:text>
        </xsl:if>
        <xsl:text>"</xsl:text>
        <xsl:value-of select="name()"/>
        <xsl:text>":"</xsl:text>
        <xsl:value-of select="text()"/>
        <xsl:text>"</xsl:text>
    </xsl:template>
    
</xsl:stylesheet>

Open in new window

0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39597193
I have changed from 'by-cap' to "cap" .

Still no result ...
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39597313
OK, here is the result I get on the XML I created from your three records

[
CAP STATUS: Pre-Audit - Facility Profile Received
{

"CAP_SATUS":"Pre-Audit - Facility Profile Received",
"VISIT_STATUS":"3",
"CITY":"Shenzhen",
"FACILITY_ID":"4002",
"FACILITY_NAME":"AAC Acoustic Technologies Ltd. (American Audio Company)"
}]
,
[
CAP STATUS: Pre-Audit - Facility Profile Received
{

"CAP_SATUS":"Pre-Audit - Facility Profile Received",
"VISIT_STATUS":"3",
"CITY":"Shenzhen",
"FACILITY_ID":"4002",
"FACILITY_NAME":"AAC Acoustic Technologies Ltd. (American Audio Company)"
}]
,
[
CAP STATUS: CAP Requested PAST Due on
{

"CAP_SATUS":"CAP Requested PAST Due on",
"VISIT_STATUS":"9",
"CITY":"Shenzhen",
"FACILITY_ID":"5343",
"FACILITY_NAME":"Volex Cable Assembly Co. Ltd."
}]

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39597321
It strikes me that you end up with 5 records in the result and only have 3 records in the source.
Have you looked at other processes in the query?
0
 
LVL 16

Author Comment

by:Swadhin Ray
ID: 39597342
The query can give more records but the only part is to group it by CAP status.

If I use the below XLST  then I am also getting the same result:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html"/>

<xsl:template match="/">
<xsl:for-each select="/ROWSET/*">
  [
 CAP STATUS: <xsl:value-of select="CAP_SATUS"/>
  {
  <xsl:for-each select="./*">
    "<xsl:value-of select="name()"/>":"<xsl:value-of select="text()"/>"<xsl:choose>
      <xsl:when test="position()!= last()">,</xsl:when>
    </xsl:choose>
   </xsl:for-each>
  }]
  <xsl:choose>
      <xsl:when test="position() != last()">,</xsl:when>
    </xsl:choose>
   </xsl:for-each>
</xsl:template>
</xsl:stylesheet>

Open in new window


The After compiling my function , and then when I run it I get the below result:

  [
 CAP STATUS: CAP Requested PAST Due on
  {
  
    "CAP_SATUS":"CAP Requested PAST Due on",
    "VISIT_STATUS":"9",
    "CITY":"Shenzhen",
    "FACILITY_ID":"5343",
    "FACILITY_NAME":"Volex Cable Assembly Co. Ltd."
  }]
  ,
  [
 CAP STATUS: Pre-Audit - Facility Profile Received
  {
  
    "CAP_SATUS":"Pre-Audit - Facility Profile Received",
    "VISIT_STATUS":"3",
    "CITY":"Shenzhen",
    "FACILITY_ID":"4002",
    "FACILITY_NAME":"AAC Acoustic Technologies Ltd. (American Audio Company)"
  }]
  ,
  [
 CAP STATUS: Pre-Audit - Facility Profile Received
  {
  
    "CAP_SATUS":"Pre-Audit - Facility Profile Received",
    "VISIT_STATUS":"3",
    "CITY":"Shanghai",
    "FACILITY_ID":"5296",
    "FACILITY_NAME":"Universal Scientific Industrial Co. Ltd."
  }]
  ,
  [
 CAP STATUS: Pre-Audit - Facility Profile Received
  {
  
    "CAP_SATUS":"Pre-Audit - Facility Profile Received",
    "VISIT_STATUS":"3",
    "CITY":"Shenzhen",
    "FACILITY_ID":"4002",
    "FACILITY_NAME":"AAC Acoustic Technologies Ltd. (American Audio Company)"
  }]
  ,
  [
 CAP STATUS: Pre-Audit - Facility Profile Received
  {
  
    "CAP_SATUS":"Pre-Audit - Facility Profile Received",
    "VISIT_STATUS":"3",
    "CITY":"Lutterworth",
    "FACILITY_ID":"5287",
    "FACILITY_NAME":"Unipart Technology Logistics"
  }]
  

Open in new window



But when I try to use the XSLT provided by you and changing the name from 'by-cap' to "cap" 3 times , my function for compiled but I am not able to get any result ...
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39597386
[
CAP STATUS: Pre-Audit - Facility Profile Received
{
"CAP_SATUS":"Pre-Audit - Facility Profile Received",
"VISIT_STATUS":"3",
"CITY":"Shenzhen",
"FACILITY_ID":"4002",
"FACILITY_NAME":"AAC Acoustic Technologies Ltd. (American Audio Company)"
}
,
{
"CAP_SATUS":"Pre-Audit - Facility Profile Received",
"VISIT_STATUS":"3",
"CITY":"Shenzhen",
"FACILITY_ID":"4002",
"FACILITY_NAME":"AAC Acoustic Technologies Ltd. (American Audio Company)"
}

]
,
[
CAP STATUS: CAP Requested PAST Due on
{
"CAP_SATUS":"CAP Requested PAST Due on",
"VISIT_STATUS":"9",
"CITY":"Shenzhen",
"FACILITY_ID":"5343",
"FACILITY_NAME":"Volex Cable Assembly Co. Ltd."
}

]

Open in new window


is what I get when I  run my XSLT against the fabricated XML

I think that is what you need, isn't it?
0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 2000 total points
ID: 39597397
There is one thing I noted...

you are wrapping the XSLT in a string (not best practice)
so maybe the processor chokes on the apostrophes in the code (yours has none)

try this

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="html"/>
    
    <xsl:key name="cap" match="*" use="CAP_SATUS"/>
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;cap&apos;, CAP_SATUS )[1])]">
            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            [
            CAP STATUS: <xsl:value-of select="CAP_SATUS"/>
            <xsl:apply-templates select="key(&apos;cap&apos;, CAP_SATUS )" mode="grouped-cap"/>
            ]
        </xsl:for-each>
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-cap">
        <xsl:if test="not(position() = 1)">
            <xsl:text>,</xsl:text>
        </xsl:if>
        {
        <xsl:apply-templates select="*" mode="inner"/>
        }
    </xsl:template>

    <xsl:template match="*" mode="inner">
        <xsl:if test="not(position() = 1)">
            <xsl:text>,</xsl:text>
            <xsl:text>&#10;</xsl:text>
        </xsl:if>
        <xsl:text>"</xsl:text>
        <xsl:value-of select="name()"/>
        <xsl:text>":"</xsl:text>
        <xsl:value-of select="text()"/>
        <xsl:text>"</xsl:text>
    </xsl:template>
    
</xsl:stylesheet>

Open in new window

0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
ID: 39597428
Superb ... Thanks a ton ...
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 39597659
welcome
0

Featured Post

A new era in Cloud training has arrived.

A day that will go down in Cloud history.. But are you ready for it? Will you accept this Cloud challenge?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question