Solved

XLST grouping

Posted on 2013-10-24
18
190 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 500 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

832 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