BizTalk Utilities CV ,   Jobs ,   Code library
 
Home Page


Add/Edit your code items
Search the code library
Browse for the code library

SQL XML

SQL XML

SQL XML Contents

N:N relationships with DBMS_XMLQuery Package
DBMS_XMLQuery package bind values to produce more than one xml file
Download data from a database in XML
Inserting XML file into a table using DBMS_XMLSave Package
Insert XML file into a table using ADO
How to call out to a COM component's method through XSLT
How to select DISTINCT items from XML via XSLT
How to convert Word (RTF) documents to XML for auto publication
Creating SQL Statements with XSLT
SQL straight to XML w/ transform
History Of XML
History Of XML And What Is XML.
Order Automation
You enjoy while you are away and let your machine do the job for you.
SQL Server 2000 User Defined Functions - A Powerful concept
XML/XSLT Maker
Generic ADO recordset to HTML table using XML / XSL
How to display the first three nodes of a XML file with XmlDocument?
What is the usage of the XmlDataDocument?
 
 

<< SEOSystem.XML >>

By Pedro Gil
First Posted 12/26/2001
Times viewed 210

Applying a XSLT stylesheet using the DBMS_XMLQuery package

Summary Applying a XSLT stylesheet using the DBMS_XMLQuery package

I'm using Oracle 8.1.7 and the XDK 9.0.1.1.0A for PL/SQL

You can get the latest version of the XDK at OTN

http://otn.oracle.com/software/tech/xml/xdk_plsql/content.html

You have lots of procedures to customize your output, but even so there is the possibility of applying a XSLT stylesheet to further customize this output.

There is two ways you can specify the XSLT stylesheet, this make the possibility of making the transformation over at the client side or at the server side.

Client side transformation:

This is done by adding the xml-stylesheet processing instruction to the result XML.

<?xml-stylesheet type=text/xsl href=SomeStylesheet.xsl?>

CREATE OR REPLACE procedure TXQ_XsltHeader is

queryCtx DBMS_XMLquery.ctxType;

result CLOB;

begin

queryCtx := DBMS_XMLQuery.newContext('select * from customer');

DBMS_XMLQuery.setrowsettag(queryctx,'customers');

DBMS_XMLQuery.setrowtag(queryctx,'customer');

--Set the xml-stylesheet PI

DBMS_XMLQUERY.setStylesheetHeader(queryCtx,'D:\test\tt.xslt','text/xsl');

result := DBMS_XMLQuery.getXML(queryCtx);

DBMS_XMLQuery.closeContext(queryCtx);

Clob2Table(result);

end;

For more information see this recommendation:

http://www.w3.org/TR/xml-stylesheet/

Offcourse that this will to much depend on the client having the correct XSLT processor, to be able to handle the transformation, and offcourse access to the refered stylesheet.

Server side transformation:

This enables to make the transformation at the server side, so you will not have to worry about client capabilities, I'm not here making any discussion of what is the best aproach (client vs server), just showing that you can make both with the XDK for PL/SQL.

In the following sample I'm transforming a Oracle Table in a HTML table

PL/SQL

CREATE OR REPLACE procedure ServerSideXSLT is

queryCtx DBMS_XMLquery.ctxType;

result CLOB;

begin

queryCtx := DBMS_XMLQuery.newContext('select * from customer');

--Defines the case of the tags to be created (0-as Is,1-lower,2-upper)

DBMS_XMLQUERY.setTagCase(queryCtx,2);

--Makes the Transformation according to the XSLT supplyed

DBMS_XMLQUERY.setXSLT(queryCtx,'D:\SamplesTmp\ smpHTMLtbl.xsl');

result := DBMS_XMLQuery.getXML(queryCtx);

DBMS_XMLQuery.closeContext(queryCtx);

Clob2Table(result);

end;

XSLT (smpHTMLtbl.xsl)

>

<?xml version=1.0?>

<xsl:stylesheet version=1.0 xmlns:xsl=http://www.w3.org/1999/XSL/Transform>

<xsl:output method=html omit-xml-declaration=yes/>

<xsl:template match=/>

<HTML><BODY>

<TABLE border='1'>

<xsl:apply-templates select=ROWSET/ROW/>

</TABLE>

</BODY></HTML>

</xsl:template>

<xsl:template match = ROW>

<xsl:if test=position() = 1>

<!--Create table Header -->

<TR>

<xsl:apply-templates select=* mode=header/>

</TR>

</xsl:if>

<TR>

<xsl:if test=position() mod 2>

<xsl:attribute name=bgcolor>lightyellow</xsl:attribute>

</xsl:if>

<xsl:apply-templates select=* mode=body/>

</TR>

</xsl:template>

<xsl:template match = ROWSET/ROW/* mode=body>

<TD><xsl:value-of select='.'/></TD>

</xsl:template>

<xsl:template match = ROWSET/ROW/* mode=header>

<TD><xsl:value-of select='name(.)'/></TD>

</xsl:template>

</xsl:stylesheet>

Output HTML

<HTML>

<BODY>

<TABLE border=1>

<TR>

<TD>CUSTID</TD>

<TD>NAME</TD>

<TD>ADDRESS</TD>

<TD>CITY</TD>

<TD>STATE</TD>

<TD>ZIP</TD>

<TD>AREA</TD>

<TD>PHONE</TD>

<TD>REPID</TD>

<TD>CREDITLIMIT</TD>

<TD>DATECUST</TD>

</TR>

<TR bgcolor=lightyellow>

<TD>100</TD>

<TD>JOCKSPORTS</TD>

<TD>345 VIEWRIDGE</TD>

<TD>BELMONT</TD>

<TD>CA</TD>

<TD>96711</TD>

<TD>415</TD>

<TD>598-6609</TD>

<TD>7844</TD>

<TD>5000</TD>

<TD>12/11/2001 0:26:1</TD>

</TR>

<TR>

<TD>101</TD>

<TD>TKB SPORT SHOP</TD>

<TD>490 BOLI RD.</TD>

<TD>REDWOOD CITY</TD>

<TD>CA</TD>

<TD>94061</TD>

<TD>415</TD>

<TD>368-1223</TD>

<TD>7521</TD>

<TD>10000</TD>

<TD>12/11/2001 0:26:1</TD>

</TR>

<TR bgcolor=lightyellow>

<TD>102</TD>

<TD>VOLLYRITE</TD>

<TD>9722 HAMILTON</TD>

<TD>BURLINGAME</TD>

<TD>CA</TD>

<TD>95133</TD>

<TD>415</TD>

<TD>644-3341</TD>

<TD>7654</TD>

<TD>7000</TD>

<TD>12/11/2001 0:26:1</TD>

</TR>

<TR>

<TD>103</TD>

<TD>JUST TENNIS</TD>

<TD>HILLVIEW MALL</TD>

<TD>BURLINGAME</TD>

<TD>CA</TD>

<TD>97544</TD>

<TD>415</TD>

<TD>677-9312</TD>

<TD>7521</TD>

<TD>3000</TD>

<TD>12/11/2001 0:26:1</TD>

</TR>

<TR bgcolor=lightyellow>

<TD>104</TD>

<TD>EVERY MOUNTAIN</TD>

<TD>574 SURRY RD.</TD>

<TD>CUPERTINO</TD>

<TD>CA</TD>

<TD>93301</TD>

<TD>408</TD>

<TD>996-2323</TD>

<TD>7499</TD>

<TD>10000</TD>

<TD>12/11/2001 0:26:1</TD>

</TR>

</TABLE>

</BODY>

Pedro Gil

Additional information

Rate this article on a scale of 1 to 10 (0 votes, average 0)

Your vote :  

<< SEOSystem.XML >>



Leave a comment for this article
Your name
Your email (optional)
Your comment
Optional: Upload an attachment
Enter the code shown:

 
 

    Email TopXML