Solved

XLST grouping

Posted on 2013-10-24
18
188 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now