Solved

XLST grouping

Posted on 2013-10-24
18
191 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Parsing the XML data to SQL Server 4 67
Detect file exist or not 3 181
Convert XML to excel12book 5 33
Migration from SQL server to oracle (XML input) 4 28
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.
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

821 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