Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Sum and Count xpath function with Oracle XMLDB

Sum and Count xpath function with Oracle XMLDB

From: will <william_hulse_at_hotmail.com>
Date: 26 Jan 2005 01:37:27 -0800
Message-ID: <283ad5ec.0501260137.191c1e85@posting.google.com>


All
I am currently looking into xslt transformations using oracle xmldb xmlTransform function. More specifically i am interested in using the xpath sum and count expressions within the stylesheet.

I have created the following stylesheet and xml document and tested it successfully in xmlSpy, giving the desired results. Moving the two documents onto the db and running it using xmlTransform gives different results..

Here are the specifics..

--Register schema..

declare
xmlschema varchar2(10000) :=
'<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:da="http://www.oracle.com/date_a.xsd" xmlns:xdb="http://xmlns.oracle.com/xdb"
targetNamespace="http://www.oracle.com/date_a.xsd">
<xs:element name="calendar">
<xs:annotation>
<xs:documentation>Comment describing your root
element</xs:documentation>
</xs:annotation>
<xs:complexType>
<xs:sequence>
<xs:element name="date" maxOccurs="unbounded">
<xs:complexType>
<xs:attribute name="value" type="xs:date" use="required"/>
<xs:attribute name="ac_num" use="required">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="50"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="name" type="xs:integer" use="optional"/>
<xs:attribute name="cost1" type="xs:integer" use="optional"/>
<xs:attribute name="cost2" type="xs:integer" use="optional"/>
<xs:attribute name="cost3" type="xs:integer" use="optional"/>
<xs:attribute name="cost4" type="xs:integer" use="optional"/>
<xs:attribute name="cost5" type="xs:integer" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>';

begin
dbms_xmlschema.registerSchema
(
schemaURL => 'http://www.oracle.com/date_a.xsd', schemaDoc => xmlschema
);
end;

create table xml_calendar
(msg_id number PRIMARY KEY NOT NULL
,xml_date sys.XMLTYPE
)
xmltype column xml_date
ELEMENT "http://www.oracle.com/date_a.xsd#calendar" ;

insert into xml_calendar
values (2, xmltype ('
<da:calendar xmlns:da="http://www.oracle.com/date_a.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.oracle.com/date_a.xsd http://www.oracle.com/date_a.xsd">
<!--date value="2004-03-32"/-->
<date value="2004-12-20" ac_num="1234-0987" name="1234"/>
<date value="2004-12-20" ac_num="" cost4="58" cost5="10"/>
<date value="2004-12-20" ac_num="" cost2="2596" cost4="589"
cost5="10"/>
<date value="2004-12-20" ac_num="" cost1="123" cost2="2596"
cost3="958" cost4="58" cost5="10"/>
<date value="2004-12-20" ac_num="" cost1="123" cost3="98"
cost4="589"/>
<date value="2004-12-20" ac_num="" cost2="2596" cost3="958"
cost4="589" cost5="10"/>
<date value="2004-12-20" ac_num="" cost1="123" cost2="2596"
cost3="958" cost4="589"/>
<date value="2004-12-20" ac_num="" cost1="123" cost2="256" cost3="988"
cost4="1" cost5="10"/>
</da:calendar>'))

create table stylesheet_tab
(stylesheet_id number, xsl_doc xmltype);

insert into stylesheet_tab
values (1,
xmltype('<?xml version="1.0" encoding="UTF-16"?>
<xsl:stylesheet version="2.0"

xmlns:da="http://www.oracle.com/date_a.xsd"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:fo="http://www.w3.org/1999/XSL/Format"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:fn="http://www.w3.org/2004/07/xpath-functions"
xmlns:xdt="http://www.w3.org/2004/07/xpath-datatypes">

<xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/>
<xsl:template match="/">
<xsl:element name="agg-test">
<xsl:call-template name="sum"/>
<xsl:call-template name="count"/>
</xsl:element>
</xsl:template>
<xsl:template name="count">
<xsl:variable name="v_cost1">
<xsl:value-of select="count(//date/@cost1)"/>
</xsl:variable>
<xsl:variable name="v_cost2">
<xsl:value-of select="count(//date/@cost2)"/>
</xsl:variable>
<xsl:variable name="v_cost3">
<xsl:value-of select="count(//date/@cost3)"/>
</xsl:variable>
<xsl:variable name="v_cost4">
<xsl:value-of select="count(//date/@cost4)"/>
</xsl:variable>
<xsl:variable name="v_cost5">
<xsl:value-of select="count(//date/@cost5)"/>
</xsl:variable>
<xsl:element name=" summary">
<xsl:attribute name=" count_cost1" select="$v_cost1"/>
<xsl:attribute name=" count_cost2" select="$v_cost2"/>
<xsl:attribute name=" count_cost3" select="$v_cost3"/>
<xsl:attribute name=" count_cost4" select="$v_cost4"/>
<xsl:attribute name=" count_cost5" select="$v_cost5"/>
</xsl:element>
</xsl:template>
<xsl:template name="sum">
<xsl:variable name="v_cost1">
<xsl:value-of select="sum(//date/@cost1)"/>
</xsl:variable>
<xsl:variable name="v_cost2">
<xsl:value-of select="sum(//date/@cost2)"/>
</xsl:variable>
<xsl:variable name="v_cost3">
<xsl:value-of select="sum(//date/@cost3)"/>
</xsl:variable>
<xsl:variable name="v_cost4">
<xsl:value-of select="sum(//date/@cost4)"/>
</xsl:variable>
<xsl:variable name="v_cost5">
<xsl:value-of select="sum(//date/@cost5)"/>
</xsl:variable>
<xsl:element name=" summary">
<xsl:attribute name=" sum_cost1" select="$v_cost1"/>
<xsl:attribute name=" sum_cost2" select="$v_cost2"/>
<xsl:attribute name=" sum_cost3" select="$v_cost3"/>
<xsl:attribute name=" sum_cost4" select="$v_cost4"/>
<xsl:attribute name=" sum_cost5" select="$v_cost5"/>
</xsl:element>
</xsl:template>
</xsl:stylesheet>'));

select XMLTransform(xml_c.XML_DATE,
(select st_tab.XSL_DOC
from stylesheet_tab st_tab
where stylesheet_id = 1)).getStringVal() AS result
from xml_calendar xml_c

<agg-test>
< summary =""/>
< summary =""/>
</agg-test>

This is not the desired results as xpath aggregation functions seem to be ignored
XMLSpy produces the following..

<agg-test>
< summary sum_cost1="492" sum_cost2="10640" sum_cost3="3960"
sum_cost4="2473" sum_cost5="50"/>
< summary count_cost1="4" count_cost2="5" count_cost3="5"
count_cost4="7" count_cost5="5"/>
</agg-test>

Does Oracle support theses xpath expressions from within a stylesheet or is there some approved workaround for this ?

Any help would be greatly appreciated..

Regards

Will Received on Wed Jan 26 2005 - 03:37:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US