Solved

grouping and sub-grouping in xslt

Posted on 2013-11-08
25
277 Views
Last Modified: 2013-11-10
Hello Experts,

I have the below xslt :

'<?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="report_type" match="*" use="REPORT_TYPE"/>
    
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;report_type&apos;, REPORT_TYPE )[1])]">
            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            
            " <xsl:value-of select="REPORT_TYPE"/>" :[
            <xsl:apply-templates select="key(&apos;report_type&apos;, REPORT_TYPE )" mode="grouped-report_type"/>
            
            
            
            
            
            ]
        </xsl:for-each>
        }
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-report_type">
        <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



Where Now I am doing one label of grouping only on report type .

Now all the records under then I want to make more label of sub grouping ..

My original XML is as below:

My original XML have other columns as below which I am looking for sub grouping it :
auditor_company (should be on label 2)
auditor_name (should be on label 3)
is_lead (should be on label 3)
Assignments ((should be on label 4)


How can I make it in the XSLT stylesheet ?
0
Comment
Question by:Swadhin Ray
  • 15
  • 10
25 Comments
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
Here is my original XML :

{<REPORT_TYPE=TODAY,VSIT_STATUS=Audit In Progress,VISIT_STATUS_ID=4,VISIT_ID=V1-5387-2013-10,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=null,PID=null,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5387,FACILITY_NAME=Zeniya Aluminum Engineering Ltd.,CITY=Dongguan,STATE_PROVINCE=Guangdong,COUNTRY=China,FACILITY_ADDRESS=The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China,FACILITY_LOCAL_NAME=null,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=112806,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100061,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=31-OCT-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=NOV 08,2013,FINAL_REPORT_DEADLINE=NOV 12,2013,SHOW_DATE=OCT 31 - NOV 01,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Swadhin Ray,IS_LEAD=0,ASSIGNMENTS=Facility>,<REPORT_TYPE=TODAY,VSIT_STATUS=Audit In Progress,VISIT_STATUS_ID=4,VISIT_ID=V1-5387-2013-10,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=null,PID=null,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5387,FACILITY_NAME=Zeniya Aluminum Engineering Ltd.,CITY=Dongguan,STATE_PROVINCE=Guangdong,COUNTRY=China,FACILITY_ADDRESS=The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China,FACILITY_LOCAL_NAME=null,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=112806,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100061,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=31-OCT-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=NOV 08,2013,FINAL_REPORT_DEADLINE=NOV 12,2013,SHOW_DATE=OCT 31 - NOV 01,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Praveen Kayetha,IS_LEAD=1,ASSIGNMENTS=Facility>,<REPORT_TYPE=TODAY,VSIT_STATUS=Audit In Progress,VISIT_STATUS_ID=4,VISIT_ID=V1-5387-2013-10,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=null,PID=null,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5387,FACILITY_NAME=Zeniya Aluminum Engineering Ltd.,CITY=Dongguan,STATE_PROVINCE=Guangdong,COUNTRY=China,FACILITY_ADDRESS=The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China,FACILITY_LOCAL_NAME=null,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=112806,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100061,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=31-OCT-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=NOV 08,2013,FINAL_REPORT_DEADLINE=NOV 12,2013,SHOW_DATE=OCT 31 - NOV 01,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Praveen Kayetha,IS_LEAD=1,ASSIGNMENTS=Labor & Human Rights>,<REPORT_TYPE=TODAY,VSIT_STATUS=Audit In Progress,VISIT_STATUS_ID=4,VISIT_ID=V1-5387-2013-10,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=null,PID=null,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5387,FACILITY_NAME=Zeniya Aluminum Engineering Ltd.,CITY=Dongguan,STATE_PROVINCE=Guangdong,COUNTRY=China,FACILITY_ADDRESS=The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China,FACILITY_LOCAL_NAME=null,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=112806,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100061,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=31-OCT-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=NOV 08,2013,FINAL_REPORT_DEADLINE=NOV 12,2013,SHOW_DATE=OCT 31 - NOV 01,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Swadhin Ray,IS_LEAD=0,ASSIGNMENTS=Labor & Human Rights>,<REPORT_TYPE=FINAL,VSIT_STATUS=Pending TPA Submission,VISIT_STATUS_ID=5,VISIT_ID=V1-5362-2013-10,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=null,PID=null,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5362,FACILITY_NAME=Wintek Corp.,CITY=Taichung,STATE_PROVINCE=Taichung,COUNTRY=Taiwan,FACILITY_ADDRESS=No 9,Kung-Yeh 7 Road, Industrial Zone, 407,FACILITY_LOCAL_NAME=null,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=112966,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100067,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=01-NOV-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=NOV 15,2013,FINAL_REPORT_DEADLINE=NOV 19,2013,SHOW_DATE=NOV 01 - NOV 08,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Praveen Kayetha,IS_LEAD=1,ASSIGNMENTS=Labor & Human Rights>,<REPORT_TYPE=FINAL,VSIT_STATUS=Pending TPA Submission,VISIT_STATUS_ID=5,VISIT_ID=V1-5362-2013-10,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=null,PID=null,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5362,FACILITY_NAME=Wintek Corp.,CITY=Taichung,STATE_PROVINCE=Taichung,COUNTRY=Taiwan,FACILITY_ADDRESS=No 9,Kung-Yeh 7 Road, Industrial Zone, 407,FACILITY_LOCAL_NAME=null,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=112966,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100067,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=01-NOV-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=NOV 15,2013,FINAL_REPORT_DEADLINE=NOV 19,2013,SHOW_DATE=NOV 01 - NOV 08,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Praveen Kayetha,IS_LEAD=1,ASSIGNMENTS=Facility>,<REPORT_TYPE=FINAL,VSIT_STATUS=Pending TPA Submission,VISIT_STATUS_ID=5,VISIT_ID=V1-5346-2013-11,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=101652,PID=122075,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5346,FACILITY_NAME=Volt,CITY=Austin,STATE_PROVINCE=Texas,COUNTRY=USA,FACILITY_ADDRESS=Onsite @ Apple, 12545 Riata Vista Circle (Bldg. #3 & Satellites),FACILITY_LOCAL_NAME=null,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=122073,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100067,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=07-NOV-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=NOV 21,2013,FINAL_REPORT_DEADLINE=NOV 25,2013,SHOW_DATE=NOV 07 - NOV 14,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Praveen Kayetha,IS_LEAD=1,ASSIGNMENTS=Labor & Human Rights>,<REPORT_TYPE=FINAL,VSIT_STATUS=Pending TPA Submission,VISIT_STATUS_ID=5,VISIT_ID=V1-5346-2013-11,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=101652,PID=122075,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5346,FACILITY_NAME=Volt,CITY=Austin,STATE_PROVINCE=Texas,COUNTRY=USA,FACILITY_ADDRESS=Onsite @ Apple, 12545 Riata Vista Circle (Bldg. #3 & Satellites),FACILITY_LOCAL_NAME=null,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=122073,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100067,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=07-NOV-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=NOV 21,2013,FINAL_REPORT_DEADLINE=NOV 25,2013,SHOW_DATE=NOV 07 - NOV 14,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Praveen Kayetha,IS_LEAD=1,ASSIGNMENTS=Facility>,<REPORT_TYPE=FINAL,VSIT_STATUS=Pending TPA Submission,VISIT_STATUS_ID=5,VISIT_ID=V1-5396-2013-10,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=null,PID=null,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5396,FACILITY_NAME=Taiwan Semiconductor Manufacturing Co. Ltd.,CITY=Hsinchu,STATE_PROVINCE=Hsinchu,COUNTRY=Taiwan,FACILITY_ADDRESS=Fab 12, No. 8, Li-Hsin Rd. VI, Hsinchu Science Park,FACILITY_LOCAL_NAME=¿¿¿¿¿¿¿¿¿¿¿¿¿¿,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=113336,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100067,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=31-OCT-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=NOV 07,2013,FINAL_REPORT_DEADLINE=NOV 11,2013,SHOW_DATE=OCT 31 - OCT 31,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Praveen Kayetha,IS_LEAD=1,ASSIGNMENTS=Facility>,<REPORT_TYPE=FINAL,VSIT_STATUS=Pending TPA Submission,VISIT_STATUS_ID=5,VISIT_ID=V1-5556-2013-11,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=null,PID=null,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5556,FACILITY_NAME=Rohm Apollo Co., Ltd (Yukuhashi Plant),CITY=Kukuhashi,STATE_PROVINCE=Fukuoka,COUNTRY=Japan,FACILITY_ADDRESS=837-1 Hatakeda Aza Inadou Ooaza,FACILITY_LOCAL_NAME=¿¿¿·¿¿¿¿¿¿¿,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=117966,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100067,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=04-NOV-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=DEC 04,2013,FINAL_REPORT_DEADLINE=DEC 06,2013,SHOW_DATE=NOV 04 - NOV 27,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Praveen Kayetha,IS_LEAD=1,ASSIGNMENTS=Facility>,<REPORT_TYPE=UPCOMING,VSIT_STATUS=Pre-Audit - Facility Profile Requested,VISIT_STATUS_ID=2,VISIT_ID=V1-5353-2013-10,USER_ID=100050,APP_ASSINMENT_ID=null,METRIC_ID=null,PID=null,INSTANCE_ID=null,IS_OFFLINE=null,FACILITY_ID=5353,FACILITY_NAME=Western Digital Corp.,CITY=Khlong Luang,STATE_PROVINCE=Pathum Thani,COUNTRY=Thailand,FACILITY_ADDRESS=60/90/2 Navanakorn Industrial Estate Zone 3, Phaholythin Road, Moo 19 Klongnueang, Klongnuang, Pathumthani 12120 Thailand,FACILITY_LOCAL_NAME=null,FACILITY_LOCAL_ADDRESS=null,LONGITUDE=null,LATITUDE=null,VISIT_EDIT_FLAG=D,VS_EDIT_FLAG=D,VS_PID=110733,VS_METRIC_ID=101661,ONSITE_LEAD=100067,APPLE_LEAD=100067,APPLE_VERIFICATION_LEAD=100043,SR_PROGRAM_MANAGER=100042,START_DATE=31-OCT-13,PROTOCOL_NAME=01 Nag ,TPA_DATE=NOV 08,2013,FINAL_REPORT_DEADLINE=NOV 12,2013,SHOW_DATE=OCT 31 - NOV 01,2013,AUDIT_COMPANY=Golder,TPA_AUDITOR=Praveen Kayetha,IS_LEAD=1,ASSIGNMENTS=Facility>,}

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
Hi sloboray,

I basically ignored your previous question because the XML you send as the XML source does not even vaguely relates to a real XML document.

I will push you some theory, the ball is in your camp unless you manage to get us real XML to test (I see you managed to do muenchian grouping on the highest level, wht you need is four times of the same)

The best way to get sub grouping done is through compound keys

If you have one key for the main grouping
<xsl:key name="report_type" match="*" use="REPORT_TYPE"/>
have a next one
<xsl:key name="report_type_lab2" match="*" use="concat(REPORT_TYPE, '-', auditor_company )"/>

and add keys per sub level and add fields to the concatenation
make sure you keep the discriminating value of a higher level in a variable, so you could add that variable to the key call
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
can you please provide for a sample for auditor company for the next label ....

adding to my xlst stylesheet ?

with this :

<xsl:key name="report_type_lab2" match="*" use="concat(REPORT_TYPE, '-', auditor_company )"/>
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
Even if i pass the same xml to my xslt it converts into json format .. which is fine for me ...
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
... but not for me, it prevents me from testing...

so, untested code snippet

   <xsl:key name="report_type" match="*" use="REPORT_TYPE"/>
    <xsl:key name="report_type_lab2" match="*" use="concat(REPORT_TYPE, '-', auditor_company )"/>     
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;report_type&apos;, REPORT_TYPE )[1])]">
            <xsl:variable name="rtype" select="REPORT_TYPE"/>
            <xsl:for-each select="../*[generate-id() = generate-id(key(&apos;report_type_lab2&apos;, concat($rtype, '-', auditor_company ) )[1])]">
                ....
                
            

Open in new window

0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
This is my actual oracle function where I am trying to convert the xml (which is a sys_refcursor  from another oracle function )to json :

create or replace 
FUNCTION     get_site_json(ip_rfc   IN VARCHAR2
                             ,ip_uname IN VARCHAR2) RETURN CLOB AS
        
                lhtmloutput   xmltype;
                lxsl          LONG;
                lxmldata      xmltype;
                lcontext      dbms_xmlgen.ctxhandle;
                l_ret_clob    CLOB;
                l_header_clob CLOB;
                l_header_data VARCHAR2(100);
                L_IP_RFC      SYS_REFCURSOR;
                l_exec_comm   VARCHAR2(4000);
        BEGIN
        
             l_exec_comm := 'SELECT ' || ip_rfc || ' from dual';
           
        
                EXECUTE IMMEDIATE l_exec_comm
                        INTO l_ip_rfc;
        
                l_header_clob := '
  {
      "metadata": {
                "USER_TYPE":"';
                l_header_data := get_user_type(ip_uname) || '"
      },';
                l_header_clob := l_header_clob || l_header_data;
                EXECUTE IMMEDIATE l_exec_comm
                      INTO l_ip_rfc;
                lcontext := dbms_xmlgen.newcontext(l_ip_rfc);
                dbms_xmlgen.setnullhandling(lcontext
                                           ,1);
                lxmldata := dbms_xmlgen.getxmltype(lcontext
                                                  ,DBMS_XMLGEN.NONE);
               -- CLOSE l_ip_rfc;
                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="report_type" match="*" use="REPORT_TYPE"/>
    <xsl:key name="report_type_lab2" match="*" use="concat(REPORT_TYPE, '-', auditor_company )"/>
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;report_type&apos;, REPORT_TYPE )[1])]">
            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            
            " <xsl:value-of select="REPORT_TYPE"/>" :[
            <xsl:apply-templates select="key(&apos;report_type&apos;, REPORT_TYPE )" mode="grouped-report_type"/>
            
            
            
            
            
            ]
        </xsl:for-each>
        }
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-report_type">
        <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>';
        
                lhtmloutput := lxmldata.transform(xmltype(lxsl));
                l_ret_clob  := lhtmloutput.getclobval();
                l_ret_clob  := REPLACE(l_ret_clob
                                      ,'_x0020_'
                                      ,' ');
        l_header_clob:= CONCAT(l_header_clob, l_ret_clob) ;
              
 RETURN l_header_clob;
        EXCEPTION
                WHEN OTHERS THEN
                        dbms_output.put_line(SQLERRM);
                        dbms_output.put_line(dbms_utility.format_error_backtrace);
                        RETURN NULL;
        end get_site_json;

Open in new window


I added the code but now I am getting null when I run my function but without :

 <xsl:key name="report_type_lab2" match="*" use="concat(REPORT_TYPE, '-', auditor_company )"/>

Open in new window


my function works fine but only for 1 level
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
The error you are getting is likely because I did not replace the apostrophes with &apos;
you would better make the XSLT external instead of wrapping it in a string
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
I got the XML format from my base query hope this helps:

<?xml version='1.0'  encoding='MacRoman' ?>
<RESULTS>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[TODAY]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Audit In Progress]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[4]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5387-2013-10]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5387]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Zeniya Aluminum Engineering Ltd.]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Dongguan]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Guangdong]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[China]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[112806]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100061]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[31-OCT-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[NOV 08,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[NOV 12,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[OCT 31 - NOV 01,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Swadhin Ray]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[0]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Facility]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[TODAY]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Audit In Progress]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[4]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5387-2013-10]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5387]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Zeniya Aluminum Engineering Ltd.]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Dongguan]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Guangdong]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[China]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[112806]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100061]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[31-OCT-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[NOV 08,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[NOV 12,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[OCT 31 - NOV 01,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Praveen Kayetha]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Facility]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[TODAY]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Audit In Progress]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[4]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5387-2013-10]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5387]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Zeniya Aluminum Engineering Ltd.]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Dongguan]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Guangdong]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[China]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[112806]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100061]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[31-OCT-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[NOV 08,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[NOV 12,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[OCT 31 - NOV 01,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Praveen Kayetha]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Labor & Human Rights]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[TODAY]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Audit In Progress]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[4]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5387-2013-10]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5387]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Zeniya Aluminum Engineering Ltd.]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Dongguan]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Guangdong]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[China]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[112806]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100061]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[31-OCT-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[NOV 08,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[NOV 12,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[OCT 31 - NOV 01,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Swadhin Ray]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[0]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Labor & Human Rights]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[FINAL]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Pending TPA Submission]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[5]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5362-2013-10]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5362]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Wintek Corp.]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Taichung]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Taichung]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[Taiwan]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[No 9,Kung-Yeh 7 Road, Industrial Zone, 407]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[112966]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[01-NOV-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[NOV 15,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[NOV 19,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[NOV 01 - NOV 08,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Praveen Kayetha]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Labor & Human Rights]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[FINAL]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Pending TPA Submission]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[5]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5362-2013-10]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5362]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Wintek Corp.]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Taichung]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Taichung]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[Taiwan]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[No 9,Kung-Yeh 7 Road, Industrial Zone, 407]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[112966]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[01-NOV-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[NOV 15,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[NOV 19,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[NOV 01 - NOV 08,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Praveen Kayetha]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Facility]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[FINAL]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Pending TPA Submission]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[5]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5346-2013-11]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[101652]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[122075]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5346]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Volt]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Austin]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Texas]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[USA]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[Onsite @ Apple, 12545 Riata Vista Circle (Bldg. #3 & Satellites)]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[122073]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[07-NOV-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[NOV 21,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[NOV 25,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[NOV 07 - NOV 14,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Praveen Kayetha]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Labor & Human Rights]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[FINAL]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Pending TPA Submission]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[5]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5346-2013-11]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[101652]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[122075]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5346]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Volt]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Austin]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Texas]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[USA]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[Onsite @ Apple, 12545 Riata Vista Circle (Bldg. #3 & Satellites)]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[122073]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[07-NOV-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[NOV 21,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[NOV 25,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[NOV 07 - NOV 14,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Praveen Kayetha]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Facility]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[FINAL]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Pending TPA Submission]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[5]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5396-2013-10]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5396]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Taiwan Semiconductor Manufacturing Co. Ltd.]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Hsinchu]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Hsinchu]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[Taiwan]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[Fab 12, No. 8, Li-Hsin Rd. VI, Hsinchu Science Park]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[¿¿¿¿¿¿¿¿¿¿¿¿¿¿]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[113336]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[31-OCT-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[NOV 07,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[NOV 11,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[OCT 31 - OCT 31,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Praveen Kayetha]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Facility]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[FINAL]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Pending TPA Submission]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[5]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5556-2013-11]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5556]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Rohm Apollo Co., Ltd (Yukuhashi Plant)]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Kukuhashi]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Fukuoka]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[Japan]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[837-1 Hatakeda Aza Inadou Ooaza]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[¿¿¿·¿¿¿¿¿¿¿]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[117966]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[04-NOV-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[DEC 04,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[DEC 06,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[NOV 04 - NOV 27,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Praveen Kayetha]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Facility]]></COLUMN>
	</ROW>
	<ROW>
		<COLUMN NAME="REPORT_TYPE"><![CDATA[UPCOMING]]></COLUMN>
		<COLUMN NAME="VSIT_STATUS"><![CDATA[Pre-Audit - Facility Profile Requested]]></COLUMN>
		<COLUMN NAME="VISIT_STATUS_ID"><![CDATA[2]]></COLUMN>
		<COLUMN NAME="VISIT_ID"><![CDATA[V1-5353-2013-10]]></COLUMN>
		<COLUMN NAME="USER_ID"><![CDATA[100050]]></COLUMN>
		<COLUMN NAME="APP_ASSINMENT_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="METRIC_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="PID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="INSTANCE_ID"><![CDATA[]]></COLUMN>
		<COLUMN NAME="IS_OFFLINE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_ID"><![CDATA[5353]]></COLUMN>
		<COLUMN NAME="FACILITY_NAME"><![CDATA[Western Digital Corp.]]></COLUMN>
		<COLUMN NAME="CITY"><![CDATA[Khlong Luang]]></COLUMN>
		<COLUMN NAME="STATE_PROVINCE"><![CDATA[Pathum Thani]]></COLUMN>
		<COLUMN NAME="COUNTRY"><![CDATA[Thailand]]></COLUMN>
		<COLUMN NAME="FACILITY_ADDRESS"><![CDATA[60/90/2 Navanakorn Industrial Estate Zone 3, Phaholythin Road, Moo 19 Klongnueang, Klongnuang, Pathumthani 12120 Thailand]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_NAME"><![CDATA[]]></COLUMN>
		<COLUMN NAME="FACILITY_LOCAL_ADDRESS"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LONGITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="LATITUDE"><![CDATA[]]></COLUMN>
		<COLUMN NAME="VISIT_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_EDIT_FLAG"><![CDATA[D]]></COLUMN>
		<COLUMN NAME="VS_PID"><![CDATA[110733]]></COLUMN>
		<COLUMN NAME="VS_METRIC_ID"><![CDATA[101661]]></COLUMN>
		<COLUMN NAME="ONSITE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_LEAD"><![CDATA[100067]]></COLUMN>
		<COLUMN NAME="APPLE_VERIFICATION_LEAD"><![CDATA[100043]]></COLUMN>
		<COLUMN NAME="SR_PROGRAM_MANAGER"><![CDATA[100042]]></COLUMN>
		<COLUMN NAME="START_DATE"><![CDATA[31-OCT-13]]></COLUMN>
		<COLUMN NAME="PROTOCOL_NAME"><![CDATA[01 Nag ]]></COLUMN>
		<COLUMN NAME="TPA_DATE"><![CDATA[NOV 08,2013]]></COLUMN>
		<COLUMN NAME="FINAL_REPORT_DEADLINE"><![CDATA[NOV 12,2013]]></COLUMN>
		<COLUMN NAME="SHOW_DATE"><![CDATA[OCT 31 - NOV 01,2013]]></COLUMN>
		<COLUMN NAME="AUDIT_COMPANY"><![CDATA[Golder]]></COLUMN>
		<COLUMN NAME="TPA_AUDITOR"><![CDATA[Praveen Kayetha]]></COLUMN>
		<COLUMN NAME="IS_LEAD"><![CDATA[1]]></COLUMN>
		<COLUMN NAME="ASSIGNMENTS"><![CDATA[Facility]]></COLUMN>
	</row>
</RESULTS>

Open in new window

0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
I applied the code provide but not working :

<?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="report_type" match="*" use="REPORT_TYPE"/>
    <xsl:key name="report_type_lab2" match="*" use="concat(REPORT_TYPE, '-', auditor_company )"/>     
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;report_type&apos;, REPORT_TYPE )[1])]">
            <xsl:variable name="rtype" select="REPORT_TYPE"/>
            <xsl:for-each select="../*[generate-id() = generate-id(key(&apos;report_type_lab2&apos;, concat($rtype, '-', auditor_company ) )[1])]">
            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            
            " <xsl:value-of select="REPORT_TYPE"/>" :[
            <xsl:apply-templates select="key(&apos;report_type&apos;, REPORT_TYPE )" mode="grouped-report_type"/>
            
            
            
            
            
            ]
        </xsl:for-each>
        }
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-report_type">
        <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 Comment

by:Swadhin Ray
Comment Utility
I was looking for the output somthing like as below:

            " TODAY" :[
            
        {
        "REPORT_TYPE":"TODAY",
"VSIT_STATUS":"Audit In Progress",
"VISIT_STATUS_ID":"4",
"VISIT_ID":"V1-5420-2013-10",
"USER_ID":"100098",
"APP_ASSINMENT_ID":"",
"METRIC_ID":"",
"PID":"",
"INSTANCE_ID":"",
"IS_OFFLINE":"",
"FACILITY_ID":"5420",
"FACILITY_NAME":"Delta Group- Delta Electronics Co. Ltd.",
"CITY":"Chenzhou",
"STATE_PROVINCE":"Hunan",
"COUNTRY":"China",
"FACILITY_ADDRESS":"Chen Zhou Export Processing Zone, Bai Lu Tang Town, Su Xian District",
"FACILITY_LOCAL_NAME":"¿¿¿¿(¿¿)¿¿¿¿",
"FACILITY_LOCAL_ADDRESS":"",
"LONGITUDE":"",
"LATITUDE":"",
"VS_PID":"111308",
"VS_METRIC_ID":"101661",
"ONSITE_LEAD":"100060",
"APPLE_LEAD":"100061",
"APPLE_VERIFICATION_LEAD":"100043",
"SR_PROGRAM_MANAGER":"100037",
"START_DATE":"30-OCT-13",
"PROTOCOL_NAME":"Save test",
"TPA_DATE":"NOV 07,2013",
"FINAL_REPORT_DEADLINE":"NOV 11,2013",
"SHOW_DATE":"OCT 30 - OCT 31,2013",

 "TPA" : 
                [{
"AUDIT_COMPANY":"Golder",
"AUDITOR" : [{
"TPA_AUDITOR":"Jasmin SGM",
"IS_LEAD":"1",
"AUD_ASSIGNMENTS": [{
"ASSIGNMENTS":"Facility",
"AUDITOR_ASSIGNMENTS":"F-0"
}]
}]
}]
}
]

Open in new window

0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
XSLT is not valid, you did not close the outer xsl:for-each
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
This is what I have modified it :

<?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="report_type" match="*" use="REPORT_TYPE"/>
    <xsl:key name="report_type_lab2" match="*" use="concat(REPORT_TYPE, '-', auditor_company )"/>     
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;report_type&apos;, REPORT_TYPE )[1])]">
            <xsl:variable name="rtype" select="REPORT_TYPE"/>
         
            <xsl:for-each select="../*[generate-id() = generate-id(key(&apos;report_type_lab2&apos;, concat($rtype, '-', auditor_company ) )[1])]">
            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
         
            </xsl:if>
            
            " <xsl:value-of select="REPORT_TYPE"/>" :[
            <xsl:apply-templates select="key(&apos;report_type&apos;, REPORT_TYPE )" mode="grouped-report_type"/>
            ]
        </xsl:for-each>
        </xsl:for-each>
        }
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-report_type">
        <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



But this is still not working
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
The XSLT will not work on this data because the element names are not REPORT_TYPE
but COLUMN having attribute is NAME="REPORT_TYPE"

That basically means that your original XSLT does not work either
or the XML is different from waht you claim it to be

pardon me, but I want to be sure before I jump into development
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
Do you have oracle data base ? so that I can share the table and its data and when you run oracle data base then the way I am getting the data ...
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
does the original XSLT work?
If it does, the XML is different but I can derive it

No, I have no Oracle
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
>> does the original XSLT work?

Yes it does...
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
Then this is your XML source (for future reference)

<?xml version="1.0" encoding="UTF-8"?><ROWSET>
	<ROW>
		<REPORT_TYPE>TODAY</REPORT_TYPE>
		<VSIT_STATUS>Audit In Progress</VSIT_STATUS>
		<VISIT_STATUS_ID>4</VISIT_STATUS_ID>
		<VISIT_ID>V1-5387-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5387</FACILITY_ID>
		<FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
		<CITY>Dongguan</CITY>
		<STATE_PROVINCE>Guangdong</STATE_PROVINCE>
		<COUNTRY>China</COUNTRY>
		<FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112806</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100061</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 08,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Swadhin Ray</TPA_AUDITOR>
		<IS_LEAD>0</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>TODAY</REPORT_TYPE>
		<VSIT_STATUS>Audit In Progress</VSIT_STATUS>
		<VISIT_STATUS_ID>4</VISIT_STATUS_ID>
		<VISIT_ID>V1-5387-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5387</FACILITY_ID>
		<FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
		<CITY>Dongguan</CITY>
		<STATE_PROVINCE>Guangdong</STATE_PROVINCE>
		<COUNTRY>China</COUNTRY>
		<FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112806</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100061</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 08,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>TODAY</REPORT_TYPE>
		<VSIT_STATUS>Audit In Progress</VSIT_STATUS>
		<VISIT_STATUS_ID>4</VISIT_STATUS_ID>
		<VISIT_ID>V1-5387-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5387</FACILITY_ID>
		<FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
		<CITY>Dongguan</CITY>
		<STATE_PROVINCE>Guangdong</STATE_PROVINCE>
		<COUNTRY>China</COUNTRY>
		<FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112806</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100061</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 08,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>TODAY</REPORT_TYPE>
		<VSIT_STATUS>Audit In Progress</VSIT_STATUS>
		<VISIT_STATUS_ID>4</VISIT_STATUS_ID>
		<VISIT_ID>V1-5387-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5387</FACILITY_ID>
		<FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
		<CITY>Dongguan</CITY>
		<STATE_PROVINCE>Guangdong</STATE_PROVINCE>
		<COUNTRY>China</COUNTRY>
		<FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112806</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100061</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 08,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Swadhin Ray</TPA_AUDITOR>
		<IS_LEAD>0</IS_LEAD>
		<ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5362-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5362</FACILITY_ID>
		<FACILITY_NAME>Wintek Corp.</FACILITY_NAME>
		<CITY>Taichung</CITY>
		<STATE_PROVINCE>Taichung</STATE_PROVINCE>
		<COUNTRY>Taiwan</COUNTRY>
		<FACILITY_ADDRESS>No 9,Kung-Yeh 7 Road, Industrial Zone, 407</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112966</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>01-NOV-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 15,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 19,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>NOV 01 - NOV 08,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5362-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5362</FACILITY_ID>
		<FACILITY_NAME>Wintek Corp.</FACILITY_NAME>
		<CITY>Taichung</CITY>
		<STATE_PROVINCE>Taichung</STATE_PROVINCE>
		<COUNTRY>Taiwan</COUNTRY>
		<FACILITY_ADDRESS>No 9,Kung-Yeh 7 Road, Industrial Zone, 407</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112966</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>01-NOV-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 15,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 19,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>NOV 01 - NOV 08,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5346-2013-11</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID>101652</METRIC_ID>
		<PID>122075</PID>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5346</FACILITY_ID>
		<FACILITY_NAME>Volt</FACILITY_NAME>
		<CITY>Austin</CITY>
		<STATE_PROVINCE>Texas</STATE_PROVINCE>
		<COUNTRY>USA</COUNTRY>
		<FACILITY_ADDRESS>Onsite @ Apple, 12545 Riata Vista Circle (Bldg. #3 &amp; Satellites)</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>122073</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>07-NOV-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 21,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 25,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>NOV 07 - NOV 14,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5346-2013-11</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID>101652</METRIC_ID>
		<PID>122075</PID>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5346</FACILITY_ID>
		<FACILITY_NAME>Volt</FACILITY_NAME>
		<CITY>Austin</CITY>
		<STATE_PROVINCE>Texas</STATE_PROVINCE>
		<COUNTRY>USA</COUNTRY>
		<FACILITY_ADDRESS>Onsite @ Apple, 12545 Riata Vista Circle (Bldg. #3 &amp; Satellites)</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>122073</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>07-NOV-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 21,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 25,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>NOV 07 - NOV 14,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5396-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5396</FACILITY_ID>
		<FACILITY_NAME>Taiwan Semiconductor Manufacturing Co. Ltd.</FACILITY_NAME>
		<CITY>Hsinchu</CITY>
		<STATE_PROVINCE>Hsinchu</STATE_PROVINCE>
		<COUNTRY>Taiwan</COUNTRY>
		<FACILITY_ADDRESS>Fab 12, No. 8, Li-Hsin Rd. VI, Hsinchu Science Park</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME>¿¿¿¿¿¿¿¿¿¿¿¿¿¿</FACILITY_LOCAL_NAME>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>113336</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 07,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 11,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - OCT 31,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5556-2013-11</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5556</FACILITY_ID>
		<FACILITY_NAME>Rohm Apollo Co., Ltd (Yukuhashi Plant)</FACILITY_NAME>
		<CITY>Kukuhashi</CITY>
		<STATE_PROVINCE>Fukuoka</STATE_PROVINCE>
		<COUNTRY>Japan</COUNTRY>
		<FACILITY_ADDRESS>837-1 Hatakeda Aza Inadou Ooaza</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME>¿¿¿·¿¿¿¿¿¿¿</FACILITY_LOCAL_NAME>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>117966</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>04-NOV-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>DEC 04,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>DEC 06,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>NOV 04 - NOV 27,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>UPCOMING</REPORT_TYPE>
		<VSIT_STATUS>Pre-Audit - Facility Profile Requested</VSIT_STATUS>
		<VISIT_STATUS_ID>2</VISIT_STATUS_ID>
		<VISIT_ID>V1-5353-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5353</FACILITY_ID>
		<FACILITY_NAME>Western Digital Corp.</FACILITY_NAME>
		<CITY>Khlong Luang</CITY>
		<STATE_PROVINCE>Pathum Thani</STATE_PROVINCE>
		<COUNTRY>Thailand</COUNTRY>
		<FACILITY_ADDRESS>60/90/2 Navanakorn Industrial Estate Zone 3, Phaholythin Road, Moo 19 Klongnueang, Klongnuang, Pathumthani 12120 Thailand</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>110733</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 08,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
</ROWSET>

Open in new window

0
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 total points
Comment Utility
on the same dataset, this one works
(you might need to restructure the { and [ etc... depending on what you need

and it seems you were a bit fast with your "it does not work"
the only thing that needed changing was uppercasing the element names

<?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="report_type" match="*" use="REPORT_TYPE"/>
    <xsl:key name="report_type_audit_cy" match="*" use="concat(REPORT_TYPE, &apos;-&apos;, AUDIT_COMPANY)"/>
    
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;report_type&apos;, REPORT_TYPE )[1])]">
            <xsl:variable name="RT" select="REPORT_TYPE"></xsl:variable>
            <xsl:for-each select="../*[generate-id() = generate-id(key(&apos;report_type_audit_cy&apos;, concat($RT, &apos;-&apos;, AUDIT_COMPANY) )[1])]">
                


            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            
            " <xsl:value-of select="REPORT_TYPE"/>" :[
                <xsl:apply-templates select="key(&apos;report_type_audit_cy&apos;, concat($RT, &apos;-&apos;, AUDIT_COMPANY) )" mode="grouped-report_type"/>
            
            
            
            
            
            ]
            </xsl:for-each>
        </xsl:for-each>
        }
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-report_type">
        <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 Comment

by:Swadhin Ray
Comment Utility
Ok Still facing issue and getting null ....

Here is my function to generate XML :

create or replace FUNCTION     get_xml_data(ip_uname IN varchar2) RETURN XMLTYPE AS
                l_xmltype XMLTYPE;
             begin

               l_xmltype := dbms_xmlgen.getxmltype('SELECT *
                                           FROM SAMPLE_TABLE'
                                       );

 --dbms_output.put_line(l_xmltype.getClobVal);
                RETURN l_xmltype;
        EXCEPTION
                WHEN OTHERS THEN
                        dbms_output.put_line(SQLERRM);
                        dbms_output.put_line(dbms_utility.format_error_backtrace);
                        RETURN NULL;
        END get_xml_data;

Open in new window


SQL> select get_xml_data('1234') from dual;

XML Output will be as below:

<ROWSET>
 <ROW>
  <REPORT_TYPE>TODAY</REPORT_TYPE>
  <VSIT_STATUS>Audit In Progress</VSIT_STATUS>
  <VISIT_STATUS_ID>4</VISIT_STATUS_ID>
  <VISIT_ID>V1-5387-2013-10</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <FACILITY_ID>5387</FACILITY_ID>
  <FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
  <CITY>Dongguan</CITY>
  <STATE_PROVINCE>Guangdong</STATE_PROVINCE>
  <COUNTRY>China</COUNTRY>
  <FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>112806</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100061</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>31-OCT-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>NOV 08,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Swadhin Ray</TPA_AUDITOR>
  <IS_LEAD>0</IS_LEAD>
  <ASSIGNMENTS>Facility</ASSIGNMENTS>
 </ROW>
 <ROW>
  <REPORT_TYPE>TODAY</REPORT_TYPE>
  <VSIT_STATUS>Audit In Progress</VSIT_STATUS>
  <VISIT_STATUS_ID>4</VISIT_STATUS_ID>
  <VISIT_ID>V1-5387-2013-10</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <FACILITY_ID>5387</FACILITY_ID>
  <FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
  <CITY>Dongguan</CITY>
  <STATE_PROVINCE>Guangdong</STATE_PROVINCE>
  <COUNTRY>China</COUNTRY>
  <FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>112806</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100061</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>31-OCT-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>NOV 08,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
  <IS_LEAD>1</IS_LEAD>
  <ASSIGNMENTS>Facility</ASSIGNMENTS>
 </ROW>
 <ROW>
  <REPORT_TYPE>TODAY</REPORT_TYPE>
  <VSIT_STATUS>Audit In Progress</VSIT_STATUS>
  <VISIT_STATUS_ID>4</VISIT_STATUS_ID>
  <VISIT_ID>V1-5387-2013-10</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <FACILITY_ID>5387</FACILITY_ID>
  <FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
  <CITY>Dongguan</CITY>
  <STATE_PROVINCE>Guangdong</STATE_PROVINCE>
  <COUNTRY>China</COUNTRY>
  <FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>112806</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100061</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>31-OCT-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>NOV 08,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
  <IS_LEAD>1</IS_LEAD>
  <ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
 </ROW>
 <ROW>
  <REPORT_TYPE>TODAY</REPORT_TYPE>
  <VSIT_STATUS>Audit In Progress</VSIT_STATUS>
  <VISIT_STATUS_ID>4</VISIT_STATUS_ID>
  <VISIT_ID>V1-5387-2013-10</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <FACILITY_ID>5387</FACILITY_ID>
  <FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
  <CITY>Dongguan</CITY>
  <STATE_PROVINCE>Guangdong</STATE_PROVINCE>
  <COUNTRY>China</COUNTRY>
  <FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>112806</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100061</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>31-OCT-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>NOV 08,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Swadhin Ray</TPA_AUDITOR>
  <IS_LEAD>0</IS_LEAD>
  <ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
 </ROW>
 <ROW>
  <REPORT_TYPE>FINAL</REPORT_TYPE>
  <VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
  <VISIT_STATUS_ID>5</VISIT_STATUS_ID>
  <VISIT_ID>V1-5362-2013-10</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <FACILITY_ID>5362</FACILITY_ID>
  <FACILITY_NAME>Wintek Corp.</FACILITY_NAME>
  <CITY>Taichung</CITY>
  <STATE_PROVINCE>Taichung</STATE_PROVINCE>
  <COUNTRY>Taiwan</COUNTRY>
  <FACILITY_ADDRESS>No 9,Kung-Yeh 7 Road, Industrial Zone, 407</FACILITY_ADDRESS>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>112966</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100067</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>01-NOV-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>NOV 15,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>NOV 19,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>NOV 01 - NOV 08,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
  <IS_LEAD>1</IS_LEAD>
  <ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
 </ROW>
 <ROW>
  <REPORT_TYPE>FINAL</REPORT_TYPE>
  <VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
  <VISIT_STATUS_ID>5</VISIT_STATUS_ID>
  <VISIT_ID>V1-5362-2013-10</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <FACILITY_ID>5362</FACILITY_ID>
  <FACILITY_NAME>Wintek Corp.</FACILITY_NAME>
  <CITY>Taichung</CITY>
  <STATE_PROVINCE>Taichung</STATE_PROVINCE>
  <COUNTRY>Taiwan</COUNTRY>
  <FACILITY_ADDRESS>No 9,Kung-Yeh 7 Road, Industrial Zone, 407</FACILITY_ADDRESS>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>112966</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100067</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>01-NOV-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>NOV 15,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>NOV 19,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>NOV 01 - NOV 08,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
  <IS_LEAD>1</IS_LEAD>
  <ASSIGNMENTS>Facility</ASSIGNMENTS>
 </ROW>
 <ROW>
  <REPORT_TYPE>FINAL</REPORT_TYPE>
  <VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
  <VISIT_STATUS_ID>5</VISIT_STATUS_ID>
  <VISIT_ID>V1-5346-2013-11</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <METRIC_ID>101652</METRIC_ID>
  <PID>122075</PID>
  <FACILITY_ID>5346</FACILITY_ID>
  <FACILITY_NAME>Volt</FACILITY_NAME>
  <CITY>Austin</CITY>
  <STATE_PROVINCE>Texas</STATE_PROVINCE>
  <COUNTRY>USA</COUNTRY>
  <FACILITY_ADDRESS>Onsite @ Apple, 12545 Riata Vista Circle (Bldg. #3 &amp; Satellites)</FACILITY_ADDRESS>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>122073</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100067</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>07-NOV-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>NOV 21,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>NOV 25,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>NOV 07 - NOV 14,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
  <IS_LEAD>1</IS_LEAD>
  <ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
 </ROW>
 <ROW>
  <REPORT_TYPE>FINAL</REPORT_TYPE>
  <VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
  <VISIT_STATUS_ID>5</VISIT_STATUS_ID>
  <VISIT_ID>V1-5346-2013-11</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <METRIC_ID>101652</METRIC_ID>
  <PID>122075</PID>
  <FACILITY_ID>5346</FACILITY_ID>
  <FACILITY_NAME>Volt</FACILITY_NAME>
  <CITY>Austin</CITY>
  <STATE_PROVINCE>Texas</STATE_PROVINCE>
  <COUNTRY>USA</COUNTRY>
  <FACILITY_ADDRESS>Onsite @ Apple, 12545 Riata Vista Circle (Bldg. #3 &amp; Satellites)</FACILITY_ADDRESS>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>122073</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100067</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>07-NOV-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>NOV 21,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>NOV 25,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>NOV 07 - NOV 14,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
  <IS_LEAD>1</IS_LEAD>
  <ASSIGNMENTS>Facility</ASSIGNMENTS>
 </ROW>
 <ROW>
  <REPORT_TYPE>FINAL</REPORT_TYPE>
  <VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
  <VISIT_STATUS_ID>5</VISIT_STATUS_ID>
  <VISIT_ID>V1-5396-2013-10</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <FACILITY_ID>5396</FACILITY_ID>
  <FACILITY_NAME>Taiwan Semiconductor Manufacturing Co. Ltd.</FACILITY_NAME>
  <CITY>Hsinchu</CITY>
  <STATE_PROVINCE>Hsinchu</STATE_PROVINCE>
  <COUNTRY>Taiwan</COUNTRY>
  <FACILITY_ADDRESS>Fab 12, No. 8, Li-Hsin Rd. VI, Hsinchu Science Park</FACILITY_ADDRESS>
  <FACILITY_LOCAL_NAME>??????????????</FACILITY_LOCAL_NAME>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>113336</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100067</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>31-OCT-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>NOV 07,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>NOV 11,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>OCT 31 - OCT 31,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
  <IS_LEAD>1</IS_LEAD>
  <ASSIGNMENTS>Facility</ASSIGNMENTS>
 </ROW>
 <ROW>
  <REPORT_TYPE>FINAL</REPORT_TYPE>
  <VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
  <VISIT_STATUS_ID>5</VISIT_STATUS_ID>
  <VISIT_ID>V1-5556-2013-11</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <FACILITY_ID>5556</FACILITY_ID>
  <FACILITY_NAME>Rohm Apollo Co., Ltd (Yukuhashi Plant)</FACILITY_NAME>
  <CITY>Kukuhashi</CITY>
  <STATE_PROVINCE>Fukuoka</STATE_PROVINCE>
  <COUNTRY>Japan</COUNTRY>
  <FACILITY_ADDRESS>837-1 Hatakeda Aza Inadou Ooaza</FACILITY_ADDRESS>
  <FACILITY_LOCAL_NAME>???·???????</FACILITY_LOCAL_NAME>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>117966</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100067</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>04-NOV-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>DEC 04,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>DEC 06,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>NOV 04 - NOV 27,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
  <IS_LEAD>1</IS_LEAD>
  <ASSIGNMENTS>Facility</ASSIGNMENTS>
 </ROW>
 <ROW>
  <REPORT_TYPE>UPCOMING</REPORT_TYPE>
  <VSIT_STATUS>Pre-Audit - Facility Profile Requested</VSIT_STATUS>
  <VISIT_STATUS_ID>2</VISIT_STATUS_ID>
  <VISIT_ID>V1-5353-2013-10</VISIT_ID>
  <USER_ID>100050</USER_ID>
  <FACILITY_ID>5353</FACILITY_ID>
  <FACILITY_NAME>Western Digital Corp.</FACILITY_NAME>
  <CITY>Khlong Luang</CITY>
  <STATE_PROVINCE>Pathum Thani</STATE_PROVINCE>
  <COUNTRY>Thailand</COUNTRY>
  <FACILITY_ADDRESS>60/90/2 Navanakorn Industrial Estate Zone 3, Phaholythin Road, Moo 19 Klongnueang, Klongnuang, Pathumthani 12120 Thailand</FACILITY_ADDRESS>
  <VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
  <VS_EDIT_FLAG>D</VS_EDIT_FLAG>
  <VS_PID>110733</VS_PID>
  <VS_METRIC_ID>101661</VS_METRIC_ID>
  <ONSITE_LEAD>100067</ONSITE_LEAD>
  <APPLE_LEAD>100067</APPLE_LEAD>
  <APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
  <SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
  <START_DATE>31-OCT-13</START_DATE>
  <PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
  <TPA_DATE>NOV 08,2013</TPA_DATE>
  <FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
  <SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
  <AUDIT_COMPANY>Golder</AUDIT_COMPANY>
  <TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
  <IS_LEAD>1</IS_LEAD>
  <ASSIGNMENTS>Facility</ASSIGNMENTS>
 </ROW>
</ROWSET>

Open in new window


Now I created the generate JSON function with the XSLT style sheet provide :

create or replace
FUNCTION     get_json(ip_rfc   IN VARCHAR2
                             ,ip_uname IN VARCHAR2) RETURN CLOB AS
        
                lhtmloutput   xmltype;
                lxsl          LONG;
                lxmldata      xmltype;
                lcontext      dbms_xmlgen.ctxhandle;
                l_ret_clob    CLOB;
                l_header_clob CLOB;
                l_header_data VARCHAR2(100);
                L_IP_RFC      SYS_REFCURSOR;
                l_exec_comm   VARCHAR2(4000);
        BEGIN
        
             l_exec_comm := 'SELECT ' || ip_rfc || ' from dual';
           
        
                EXECUTE IMMEDIATE l_exec_comm
                        INTO l_ip_rfc;
        
                l_header_clob := '
  {
      "metadata": {
                "USER_TYPE":"';
                --l_header_data := get_user_type(ip_uname) || '"
                l_header_data := 'My_U_TYPE' || '"
      },';
                l_header_clob := l_header_clob || l_header_data;
                EXECUTE IMMEDIATE l_exec_comm
                      INTO l_ip_rfc;
                lcontext := dbms_xmlgen.newcontext(l_ip_rfc);
                dbms_xmlgen.setnullhandling(lcontext
                                           ,1);
                lxmldata := dbms_xmlgen.getxmltype(lcontext
                                                  ,DBMS_XMLGEN.NONE);
               -- CLOSE l_ip_rfc;
                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="report_type" match="*" use="REPORT_TYPE"/>
    <xsl:key name="report_type_audit_cy" match="*" use="concat(REPORT_TYPE, &apos;-&apos;, AUDIT_COMPANY)"/>
    
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;report_type&apos;, REPORT_TYPE )[1])]">
            <xsl:variable name="RT" select="REPORT_TYPE"></xsl:variable>
            <xsl:for-each select="../*[generate-id() = generate-id(key(&apos;report_type_audit_cy&apos;, concat($RT, &apos;-&apos;, AUDIT_COMPANY) )[1])]">
                


            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            
            " <xsl:value-of select="REPORT_TYPE"/>" :[
                <xsl:apply-templates select="key(&apos;report_type_audit_cy&apos;, concat($RT, &apos;-&apos;, AUDIT_COMPANY) )" mode="grouped-report_type"/>
            
            
            
            
            
            ]
            </xsl:for-each>
        </xsl:for-each>
        }
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-report_type">
        <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>';
        
                lhtmloutput := lxmldata.transform(xmltype(lxsl));
                l_ret_clob  := lhtmloutput.getclobval();
                l_ret_clob  := REPLACE(l_ret_clob
                                      ,'_x0020_'
                                      ,' ');
        l_header_clob:= CONCAT(l_header_clob, l_ret_clob) ;
              
 RETURN l_header_clob;
        EXCEPTION
                WHEN OTHERS THEN
                        dbms_output.put_line(SQLERRM);
                        dbms_output.put_line(dbms_utility.format_error_backtrace);
                        RETURN NULL;
        end get_json;

Open in new window



Now for running the above function passing my first function would be like :

SQL> select get_json('get_xml_data(''1234'')','1234') from dual;


Now I am getting null....
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
This question is in this zone
Experts Exchange > Web Development > Web Languages/Standards > XML > grouping and sub-grouping in xslt

I can confirm that the XSLT works an returns a value
In my IDE, it returns more or less exactly what you laid out as the expected result

So I am pretty convinced that the issue lies outside the XSLT, further down the process.
The JSON that currently comes out does not validate as JSON.
So it could be that a later step in the process breaks because of invalid JSON
(the JSON that is spit out in my development environment matches what you want, but what you listed as expected result is NOT valid JSON, so that could be the issue

What I recommend is:
- download an XML development environment such as Oxygen (www.oxygenxml.com)
- test your XSLT against the XML
- twiddle the XSLT until you get valid JSOn (oxygen can validate your JSON result too)
- then put the correct XSLT in your system and see what happens
(don't forget to escape the ')

You have a system issue beyond XSLT and way beyond what I consider reasonable for a single EE question.

If you could come up with a valid JSON result for your endeavours, I could have a look on how to get there adapting teh XSLT (but oter than that... I am not going to reproduce your full pipeline including oracle)

Note that as a developer posting questions on a forum, in order to get the correct help, it is important that you break down the process yourself and try to isolate the issues
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
Hello Gertone,

Here is the output I am getting when I run using the XLST .

But you can see that I am getting the group for my report type but not for audit company.

{
"metadata":{
"VISIT_TYPE":"D"
},
" TODAY":+[4],
" FINAL":+[6],
" UPCOMING":[
{
"REPORT_TYPE":"UPCOMING",
"VSIT_STATUS":"Pre-Audit - Facility Profile Requested",
"VISIT_STATUS_ID":"2",
"VISIT_ID":"V1-5353-2013-10",
"USER_ID":"100050",
"APP_ASSINMENT_ID":"",
"METRIC_ID":"",
"PID":"",
"INSTANCE_ID":"",
"IS_OFFLINE":"",
"FACILITY_ID":"5353",
"FACILITY_NAME":"Western Digital Corp.",
"CITY":"Khlong Luang",
"STATE_PROVINCE":"Pathum Thani",
"COUNTRY":"Thailand",
"FACILITY_ADDRESS":"60/90/2 Navanakorn Industrial Estate Zone 3, Phaholythin Road, Moo 19 Klongnueang, Klongnuang, Pathumthani 12120 Thailand",
"FACILITY_LOCAL_NAME":"",
"FACILITY_LOCAL_ADDRESS":"",
"LONGITUDE":"",
"LATITUDE":"",
"VISIT_EDIT_FLAG":"D",
"VS_EDIT_FLAG":"D",
"VS_PID":"110733",
"VS_METRIC_ID":"101661",
"ONSITE_LEAD":"100067",
"APPLE_LEAD":"100067",
"APPLE_VERIFICATION_LEAD":"100043",
"SR_PROGRAM_MANAGER":"100042",
"START_DATE":"31-OCT-13",
"PROTOCOL_NAME":"01 Nag ",
"TPA_DATE":"NOV 08,2013",
"FINAL_REPORT_DEADLINE":"NOV 12,2013",
"SHOW_DATE":"OCT 31 - NOV 01,2013",
"AUDIT_COMPANY":"Golder",
"TPA_AUDITOR":"Praveen Kayetha",
"IS_LEAD":"1",
"ASSIGNMENTS":"Facility"
}
]
}

Open in new window



what I was expecting the output should be like below:

{
"metadata":{
"VISIT_TYPE":"D"
},
" TODAY":+[4],
" FINAL":+[6],
" UPCOMING":[
{
"REPORT_TYPE":"UPCOMING",
"VSIT_STATUS":"Pre-Audit - Facility Profile Requested",
"VISIT_STATUS_ID":"2",
"VISIT_ID":"V1-5353-2013-10",
"USER_ID":"100050",
"APP_ASSINMENT_ID":"",
"METRIC_ID":"",
"PID":"",
"INSTANCE_ID":"",
"IS_OFFLINE":"",
"FACILITY_ID":"5353",
"FACILITY_NAME":"Western Digital Corp.",
"CITY":"Khlong Luang",
"STATE_PROVINCE":"Pathum Thani",
"COUNTRY":"Thailand",
"FACILITY_ADDRESS":"60/90/2 Navanakorn Industrial Estate Zone 3, Phaholythin Road, Moo 19 Klongnueang, Klongnuang, Pathumthani 12120 Thailand",
"FACILITY_LOCAL_NAME":"",
"FACILITY_LOCAL_ADDRESS":"",
"LONGITUDE":"",
"LATITUDE":"",
"VISIT_EDIT_FLAG":"D",
"VS_EDIT_FLAG":"D",
"VS_PID":"110733",
"VS_METRIC_ID":"101661",
"ONSITE_LEAD":"100067",
"APPLE_LEAD":"100067",
"APPLE_VERIFICATION_LEAD":"100043",
"SR_PROGRAM_MANAGER":"100042",
"START_DATE":"31-OCT-13",
"PROTOCOL_NAME":"01 Nag ",
"TPA_DATE":"NOV 08,2013",
"FINAL_REPORT_DEADLINE":"NOV 12,2013",
"SHOW_DATE":"OCT 31 - NOV 01,2013",
"AUDIT_COMPANY":[
{
"AUDIT_COMPANY":"Golder",
"TPA_AUDITOR":[
{
"TPA_AUDITOR":"Praveen Kayetha",
"IS_LEAD":"1",
"ASSIGNMENTS":[
{
"ASSIGNMENTS":"Facility"
}
]
}
]
}
]
}
]
}

Open in new window


If you see the expected result is a valid JSON ..
0
 
LVL 16

Author Comment

by:Swadhin Ray
Comment Utility
The XLST which I am using is as below:

<?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="report_type" match="*" use="REPORT_TYPE"/>
    <xsl:key name="report_type_audit_cy" match="*" use="concat(REPORT_TYPE, &apos;-&apos;, AUDIT_COMPANY)"/>
    
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;report_type&apos;, REPORT_TYPE )[1])]">
            <xsl:variable name="RT" select="REPORT_TYPE"></xsl:variable>
            <xsl:for-each select="../*[generate-id() = generate-id(key(&apos;report_type_audit_cy&apos;, concat($RT, &apos;-&apos;, AUDIT_COMPANY) )[1])]">
            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            
           , " <xsl:value-of select="REPORT_TYPE"/>" :[
                <xsl:apply-templates select="key(&apos;report_type_audit_cy&apos;, concat($RT, &apos;-&apos;, AUDIT_COMPANY) )" mode="grouped-report_type"/>
          ]
            </xsl:for-each>
        </xsl:for-each>
        }
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-report_type">
        <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 Comment

by:Swadhin Ray
Comment Utility
I tried with :

http://www.w3schools.com/xsl/tryxslt.asp?xmlfile=cdcatalog&xsltfile=cdcatalog_ex3

If you replace the "XML Code:" to as below :

<?xml version="1.0" encoding="UTF-8"?><ROWSET>
	<ROW>
		<REPORT_TYPE>TODAY</REPORT_TYPE>
		<VSIT_STATUS>Audit In Progress</VSIT_STATUS>
		<VISIT_STATUS_ID>4</VISIT_STATUS_ID>
		<VISIT_ID>V1-5387-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5387</FACILITY_ID>
		<FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
		<CITY>Dongguan</CITY>
		<STATE_PROVINCE>Guangdong</STATE_PROVINCE>
		<COUNTRY>China</COUNTRY>
		<FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112806</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100061</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 08,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Swadhin Ray</TPA_AUDITOR>
		<IS_LEAD>0</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>TODAY</REPORT_TYPE>
		<VSIT_STATUS>Audit In Progress</VSIT_STATUS>
		<VISIT_STATUS_ID>4</VISIT_STATUS_ID>
		<VISIT_ID>V1-5387-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5387</FACILITY_ID>
		<FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
		<CITY>Dongguan</CITY>
		<STATE_PROVINCE>Guangdong</STATE_PROVINCE>
		<COUNTRY>China</COUNTRY>
		<FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112806</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100061</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 08,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>TODAY</REPORT_TYPE>
		<VSIT_STATUS>Audit In Progress</VSIT_STATUS>
		<VISIT_STATUS_ID>4</VISIT_STATUS_ID>
		<VISIT_ID>V1-5387-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5387</FACILITY_ID>
		<FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
		<CITY>Dongguan</CITY>
		<STATE_PROVINCE>Guangdong</STATE_PROVINCE>
		<COUNTRY>China</COUNTRY>
		<FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112806</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100061</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 08,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>TODAY</REPORT_TYPE>
		<VSIT_STATUS>Audit In Progress</VSIT_STATUS>
		<VISIT_STATUS_ID>4</VISIT_STATUS_ID>
		<VISIT_ID>V1-5387-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5387</FACILITY_ID>
		<FACILITY_NAME>Zeniya Aluminum Engineering Ltd.</FACILITY_NAME>
		<CITY>Dongguan</CITY>
		<STATE_PROVINCE>Guangdong</STATE_PROVINCE>
		<COUNTRY>China</COUNTRY>
		<FACILITY_ADDRESS>The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112806</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100061</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 08,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Swadhin Ray</TPA_AUDITOR>
		<IS_LEAD>0</IS_LEAD>
		<ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5362-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5362</FACILITY_ID>
		<FACILITY_NAME>Wintek Corp.</FACILITY_NAME>
		<CITY>Taichung</CITY>
		<STATE_PROVINCE>Taichung</STATE_PROVINCE>
		<COUNTRY>Taiwan</COUNTRY>
		<FACILITY_ADDRESS>No 9,Kung-Yeh 7 Road, Industrial Zone, 407</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112966</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>01-NOV-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 15,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 19,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>NOV 01 - NOV 08,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5362-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5362</FACILITY_ID>
		<FACILITY_NAME>Wintek Corp.</FACILITY_NAME>
		<CITY>Taichung</CITY>
		<STATE_PROVINCE>Taichung</STATE_PROVINCE>
		<COUNTRY>Taiwan</COUNTRY>
		<FACILITY_ADDRESS>No 9,Kung-Yeh 7 Road, Industrial Zone, 407</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>112966</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>01-NOV-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 15,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 19,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>NOV 01 - NOV 08,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5346-2013-11</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID>101652</METRIC_ID>
		<PID>122075</PID>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5346</FACILITY_ID>
		<FACILITY_NAME>Volt</FACILITY_NAME>
		<CITY>Austin</CITY>
		<STATE_PROVINCE>Texas</STATE_PROVINCE>
		<COUNTRY>USA</COUNTRY>
		<FACILITY_ADDRESS>Onsite @ Apple, 12545 Riata Vista Circle (Bldg. #3 &amp; Satellites)</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>122073</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>07-NOV-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 21,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 25,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>NOV 07 - NOV 14,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Labor &amp; Human Rights</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5346-2013-11</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID>101652</METRIC_ID>
		<PID>122075</PID>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5346</FACILITY_ID>
		<FACILITY_NAME>Volt</FACILITY_NAME>
		<CITY>Austin</CITY>
		<STATE_PROVINCE>Texas</STATE_PROVINCE>
		<COUNTRY>USA</COUNTRY>
		<FACILITY_ADDRESS>Onsite @ Apple, 12545 Riata Vista Circle (Bldg. #3 &amp; Satellites)</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>122073</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>07-NOV-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 21,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 25,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>NOV 07 - NOV 14,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5396-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5396</FACILITY_ID>
		<FACILITY_NAME>Taiwan Semiconductor Manufacturing Co. Ltd.</FACILITY_NAME>
		<CITY>Hsinchu</CITY>
		<STATE_PROVINCE>Hsinchu</STATE_PROVINCE>
		<COUNTRY>Taiwan</COUNTRY>
		<FACILITY_ADDRESS>Fab 12, No. 8, Li-Hsin Rd. VI, Hsinchu Science Park</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME>¿¿¿¿¿¿¿¿¿¿¿¿¿¿</FACILITY_LOCAL_NAME>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>113336</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 07,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 11,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - OCT 31,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>FINAL</REPORT_TYPE>
		<VSIT_STATUS>Pending TPA Submission</VSIT_STATUS>
		<VISIT_STATUS_ID>5</VISIT_STATUS_ID>
		<VISIT_ID>V1-5556-2013-11</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5556</FACILITY_ID>
		<FACILITY_NAME>Rohm Apollo Co., Ltd (Yukuhashi Plant)</FACILITY_NAME>
		<CITY>Kukuhashi</CITY>
		<STATE_PROVINCE>Fukuoka</STATE_PROVINCE>
		<COUNTRY>Japan</COUNTRY>
		<FACILITY_ADDRESS>837-1 Hatakeda Aza Inadou Ooaza</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME>¿¿¿·¿¿¿¿¿¿¿</FACILITY_LOCAL_NAME>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>117966</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>04-NOV-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>DEC 04,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>DEC 06,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>NOV 04 - NOV 27,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
	<ROW>
		<REPORT_TYPE>UPCOMING</REPORT_TYPE>
		<VSIT_STATUS>Pre-Audit - Facility Profile Requested</VSIT_STATUS>
		<VISIT_STATUS_ID>2</VISIT_STATUS_ID>
		<VISIT_ID>V1-5353-2013-10</VISIT_ID>
		<USER_ID>100050</USER_ID>
		<APP_ASSINMENT_ID/>
		<METRIC_ID/>
		<PID/>
		<INSTANCE_ID/>
		<IS_OFFLINE/>
		<FACILITY_ID>5353</FACILITY_ID>
		<FACILITY_NAME>Western Digital Corp.</FACILITY_NAME>
		<CITY>Khlong Luang</CITY>
		<STATE_PROVINCE>Pathum Thani</STATE_PROVINCE>
		<COUNTRY>Thailand</COUNTRY>
		<FACILITY_ADDRESS>60/90/2 Navanakorn Industrial Estate Zone 3, Phaholythin Road, Moo 19 Klongnueang, Klongnuang, Pathumthani 12120 Thailand</FACILITY_ADDRESS>
		<FACILITY_LOCAL_NAME/>
		<FACILITY_LOCAL_ADDRESS/>
		<LONGITUDE/>
		<LATITUDE/>
		<VISIT_EDIT_FLAG>D</VISIT_EDIT_FLAG>
		<VS_EDIT_FLAG>D</VS_EDIT_FLAG>
		<VS_PID>110733</VS_PID>
		<VS_METRIC_ID>101661</VS_METRIC_ID>
		<ONSITE_LEAD>100067</ONSITE_LEAD>
		<APPLE_LEAD>100067</APPLE_LEAD>
		<APPLE_VERIFICATION_LEAD>100043</APPLE_VERIFICATION_LEAD>
		<SR_PROGRAM_MANAGER>100042</SR_PROGRAM_MANAGER>
		<START_DATE>31-OCT-13</START_DATE>
		<PROTOCOL_NAME>01 Nag </PROTOCOL_NAME>
		<TPA_DATE>NOV 08,2013</TPA_DATE>
		<FINAL_REPORT_DEADLINE>NOV 12,2013</FINAL_REPORT_DEADLINE>
		<SHOW_DATE>OCT 31 - NOV 01,2013</SHOW_DATE>
		<AUDIT_COMPANY>Golder</AUDIT_COMPANY>
		<TPA_AUDITOR>Praveen Kayetha</TPA_AUDITOR>
		<IS_LEAD>1</IS_LEAD>
		<ASSIGNMENTS>Facility</ASSIGNMENTS>
	</ROW>
</ROWSET>

Open in new window


And "XSLT Code:" as below:

<?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="report_type" match="*" use="REPORT_TYPE"/>
    <xsl:key name="report_type_audit_cy" match="*" use="concat(REPORT_TYPE, &apos;-&apos;, AUDIT_COMPANY)"/>
    
    
    <xsl:template match="/">
        <xsl:apply-templates select="ROWSET"/>
    </xsl:template>
    
    <xsl:template match="/ROWSET">
        <xsl:for-each select="*[generate-id() = generate-id(key(&apos;report_type&apos;, REPORT_TYPE )[1])]">
            <xsl:variable name="RT" select="REPORT_TYPE"></xsl:variable>
            <xsl:for-each select="../*[generate-id() = generate-id(key(&apos;report_type_audit_cy&apos;, concat($RT, &apos;-&apos;, AUDIT_COMPANY) )[1])]">
                


            <xsl:if test="not(position() = 1)">
                <xsl:text>,</xsl:text>
            </xsl:if>
            
           { " <xsl:value-of select="REPORT_TYPE"/>" :[
                <xsl:apply-templates select="key(&apos;report_type_audit_cy&apos;, concat($RT, &apos;-&apos;, AUDIT_COMPANY) )" mode="grouped-report_type"/>
            
            
            
            
            
            ]
            </xsl:for-each>
        </xsl:for-each>
        }
    </xsl:template>
    
    <xsl:template match="*" mode="grouped-report_type">
        <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


The the output will be as like below:
{ " TODAY" :[ { "REPORT_TYPE":"TODAY", "VSIT_STATUS":"Audit In Progress", "VISIT_STATUS_ID":"4", "VISIT_ID":"V1-5387-2013-10", "USER_ID":"100050", "APP_ASSINMENT_ID":"", "METRIC_ID":"", "PID":"", "INSTANCE_ID":"", "IS_OFFLINE":"", "FACILITY_ID":"5387", "FACILITY_NAME":"Zeniya Aluminum Engineering Ltd.", "CITY":"Dongguan", "STATE_PROVINCE":"Guangdong", "COUNTRY":"China", "FACILITY_ADDRESS":"The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China", "FACILITY_LOCAL_NAME":"", "FACILITY_LOCAL_ADDRESS":"", "LONGITUDE":"", "LATITUDE":"", "VISIT_EDIT_FLAG":"D", "VS_EDIT_FLAG":"D", "VS_PID":"112806", "VS_METRIC_ID":"101661", "ONSITE_LEAD":"100067", "APPLE_LEAD":"100061", "APPLE_VERIFICATION_LEAD":"100043", "SR_PROGRAM_MANAGER":"100042", "START_DATE":"31-OCT-13", "PROTOCOL_NAME":"01 Nag ", "TPA_DATE":"NOV 08,2013", "FINAL_REPORT_DEADLINE":"NOV 12,2013", "SHOW_DATE":"OCT 31 - NOV 01,2013", "AUDIT_COMPANY":"Golder", "TPA_AUDITOR":"Swadhin Ray", "IS_LEAD":"0", "ASSIGNMENTS":"Facility" } , { "REPORT_TYPE":"TODAY", "VSIT_STATUS":"Audit In Progress", "VISIT_STATUS_ID":"4", "VISIT_ID":"V1-5387-2013-10", "USER_ID":"100050", "APP_ASSINMENT_ID":"", "METRIC_ID":"", "PID":"", "INSTANCE_ID":"", "IS_OFFLINE":"", "FACILITY_ID":"5387", "FACILITY_NAME":"Zeniya Aluminum Engineering Ltd.", "CITY":"Dongguan", "STATE_PROVINCE":"Guangdong", "COUNTRY":"China", "FACILITY_ADDRESS":"The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China", "FACILITY_LOCAL_NAME":"", "FACILITY_LOCAL_ADDRESS":"", "LONGITUDE":"", "LATITUDE":"", "VISIT_EDIT_FLAG":"D", "VS_EDIT_FLAG":"D", "VS_PID":"112806", "VS_METRIC_ID":"101661", "ONSITE_LEAD":"100067", "APPLE_LEAD":"100061", "APPLE_VERIFICATION_LEAD":"100043", "SR_PROGRAM_MANAGER":"100042", "START_DATE":"31-OCT-13", "PROTOCOL_NAME":"01 Nag ", "TPA_DATE":"NOV 08,2013", "FINAL_REPORT_DEADLINE":"NOV 12,2013", "SHOW_DATE":"OCT 31 - NOV 01,2013", "AUDIT_COMPANY":"Golder", "TPA_AUDITOR":"Praveen Kayetha", "IS_LEAD":"1", "ASSIGNMENTS":"Facility" } , { "REPORT_TYPE":"TODAY", "VSIT_STATUS":"Audit In Progress", "VISIT_STATUS_ID":"4", "VISIT_ID":"V1-5387-2013-10", "USER_ID":"100050", "APP_ASSINMENT_ID":"", "METRIC_ID":"", "PID":"", "INSTANCE_ID":"", "IS_OFFLINE":"", "FACILITY_ID":"5387", "FACILITY_NAME":"Zeniya Aluminum Engineering Ltd.", "CITY":"Dongguan", "STATE_PROVINCE":"Guangdong", "COUNTRY":"China", "FACILITY_ADDRESS":"The following all share the same business license:Factory #1: No.130, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #2: No.888, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, ChinaFactory #3: No.307, Bubugao Road, Wusha Jiangbei Village, Changan Town, Dongguan City, Guangdong Province, China", "FACILITY_LOCAL_NAME":"", "FACILITY_LOCAL_ADDRESS":"", "LONGITUDE":"", "LATITUDE":"", "VISIT_EDIT_FLAG":"D", "VS_EDIT_FLAG":"D", "VS_PID":"112806", "VS_METRIC_ID":"101661", "ONSITE_LEAD":"100067", "APPLE_LEAD":"100061", "APPLE_VERIFICATION_LEAD":"100043", "SR_PROGRAM_MANAGER":"100042", "START_DATE":"31-OCT-13", "PROTOCOL_NAME":"01 Nag ", "TPA_DATE":"NOV 08,2013", "FINAL_REPORT_DEADLINE":"NOV 12,2013", "SHOW_DATE":"OCT 31 - NOV 01,2013", "AUDIT_COMPANY":"Golder", "TPA_AUDITOR":"Praveen Kayetha", "IS_LEAD":"1", "ASSIGNMENTS":"" } ] }

Open in new window


Now if you take the JSON output  and check in the online parser i.e. :

http://json.parser.online.fr/

Open in new window


The you will see it as a valid JSON but it is not building any group under Audit company ...
0
 
LVL 60

Expert Comment

by:Geert Bormans
Comment Utility
I see. If you are doing XSLT development, the w3schools example is not exactly what is most helpful for debugging. I made a suggestion for that. But how you debug and develop is your choice.

I showed you a working nested grouping code.
But to actually see the grouped results as you want them,
you need to play a little with what you put inside and outside the different nested for eaches
- outside the first for-each will appear once
- in between the for-eaches will appear one per type group
- inside the inner for-each will apper once per audit company group
- in there you could have another for-each to get all the data nodes

I did NOT develop your project, that is your work. I only showed how you need to deal with nested grouping. So, in order to get what you need, you need to do some stuff yourself.
I have a deadline by tomorrow night. So if you are expecting me to do all of that for you... you will have to wait a couple of days
0
 
LVL 16

Author Closing Comment

by:Swadhin Ray
Comment Utility
Thanks a ton for this ...
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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 …
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. 
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video discusses moving either the default database or any database to a new volume.

771 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

10 Experts available now in Live!

Get 1:1 Help Now