Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Sum and Count xpath function with Oracle XMLDB
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:call-template name="sum"/>
<xsl:output method="xml" omit-xml-declaration="yes" indent="yes"/>
<xsl:template match="/">
<xsl:element name="agg-test">
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