Solved

XLST grouping

Posted on 2013-10-24
18
192 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Database Generating XML using query 21 60
Help with Syntax 9 57
.net and XML report from SCCM 5 81
SQL Stored Procedure Speed Improvements 6 55
The Problem How to write an Xquery that works like a SQL outer join, providing placeholders for absent data on the outer side?  I give a bit more background at the end. The situation expressed as relational data Let’s work through this.  I’ve …
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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