1 / 34

XML and Oracle 8i : A How-To Guide for PL/SQL Users NoCOUG , 14 th Nov’00, 1.00 pm

XML and Oracle 8i : A How-To Guide for PL/SQL Users NoCOUG , 14 th Nov’00, 1.00 pm. Eashwar Iyer. Goals and Objectives. Understand the basics of XML, DTD and XSL Look at some of the relevant tools offered by Oracle Understand the basic usage of these tools with the help of examples

onaona
Download Presentation

XML and Oracle 8i : A How-To Guide for PL/SQL Users NoCOUG , 14 th Nov’00, 1.00 pm

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. XML and Oracle 8i : A How-To Guide for PL/SQL UsersNoCOUG, 14th Nov’00, 1.00 pm Eashwar Iyer

  2. Goals and Objectives • Understand the basics of XML, DTD and XSL • Look at some of the relevant tools offered by Oracle • Understand the basic usage of these tools with the help of examples • See the examples in action

  3. Some Basics Revisited • XML - eXtensible Markup Language It describes data in an easily readable format but without any indication of how the data is to be displayed (as in HTML). It is a database-neutral and device-neutral format; data marked up in XML can be targeted at different devices using eXtensible Style Language (XSL).

  4. Some Basics Revisited Example of XML <?xml version="1.0"?> <?xml-stylesheet type="text/xsl" href=“example_1.xsl”?> <Houserules> <Houserule id="1"> <WakeUp> 05.00 hrs </WakeUp> <HitBed> 00.00 hrs </HitBed> </Houserule> </Houserules>

  5. Some Basics Revisited • DTD – Document Type Definition Is a set of rules or grammar that is defined by us to construct our own XML rules. In other words, a DTD provides the rules that define the elements and structure of our new language.

  6. Some Basics Revisited Example of DTD <Houserules> <Houserule> <WakeUp> </WakeUp> <HitBed> </HitBed> </Houserule> </Houserules> Note: The actual DTD syntax is covered in later slides

  7. Some Basics Revisited • XSL – eXtensible Style Language At its most basic, XSL provides a capability similar to a "mail merge." The style sheet contains a template of the desired result structure, and identifies data in the source document (XML) to insert into this template.

  8. Some Basics Revisited Some Basics Revisited <?xml version='1.0'?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"> <xsl:template match="/"> <HTML> <BODY> <h1>HOUSE RULES</h1> <xsl:for-each select="Houserules/Houserule"> <b>Rule # <xsl:value-of select="@id" /> </b> <i>Wake up at : <xsl:value-of select="WakeUp" /> </i> <i>Hit the bed at : <xsl:value-of select="HitBed" /> </i> </xsl:for-each> </BODY> </HTML> </xsl:template> </xsl:stylesheet> Example of XSL

  9. Some Basics Revisited Output of the example in a browser

  10. XML in Oracle • Oracle8i with JServer (version used for the examples in this presentation is 8.1.6) • Oracle XML Developer’s Kit (XDK) (among the many tools available in the XDK, the following are used for the examples here) • Oracle XML Parser for PL/SQL • Oracle XSU (XML SQL Utility) • XMLGEN Package

  11. A Round-Trip Example What do we want to achieve? To enjoy all the benefits provided by the Oracle tools, the least we should be able to do, to get started, are: • Read data from the database and convert them into an XML document. • Output the XML documents in the appropriate device (we will restrict ourselves to displaying the output in a browser). • Read XML document and insert the data contained in it into the table in the database.

  12. A Round-Trip Example • Lets consider a Zip code table with the • following structure: Column Name Data Type Width State_Abbreviation Character 2 ZipCode Character 5 Zip_Code_Extn Character 4 City Varchar2 50

  13. FirstCutDTD • <!ELEMENT mappings (state_abbreviation, zipcode, zip_code_extn, city)> • <!ELEMENT state_abbreviation (#PCDATA)> • <!ELEMENT zipcode (#PCDATA)> • <!ELEMENT zip_code_extn (#PCDATA)> • <!ELEMENT city (#PCDATA)> <!ELEMENT Zipcodes (mappings)+>

  14. Extending the DTD • Among the many keywords available for defining • DTDs, let’s look at the “Attribute” keyword. <!ELEMENT Zipcodes (mappings)+> <!ELEMENT mappings (state_abbreviation, zipcode, zip_code_extn, city)> <!ELEMENT state_abbreviation (#PCDATA)> <!ATTLIST state_abbreviation state (AL | AK | AZ | AR | AS | CA | CO | CT) #REQUIRED > <!ELEMENT zipcode (#PCDATA)> <!ATTLIST zipcode zipcode CDATA #REQUIRED > <!ELEMENT zip_code_extn (#PCDATA)> <!ELEMENT city (#PCDATA)> <!ATTLIST city city CDATA #REQUIRED >

  15. XSL for the XML/DTD <?xml version='1.0'?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl"> <xsl:template match="/"> <HTML> <BODY> <TABLE BORDER="2"> <TR> <TD>Zipcode</TD> <TD>Zip Code Extn</TD> <TD>City</TD> <TD>State Abbreviation</TD> </TR> Contd.......

  16. XSL for the XML/DTD • <xsl:for-each select="Zipcodes/mappings"> • <TR> • <TD><xsl:value-of select="ZIPCODE"/></TD> • <TD><xsl:value-of select="ZIP_CODE_EXTN"/></TD> • <TD><xsl:value-of select="CITY"/></TD> • <TD><xsl:value-of • select="STATE_ABBREVIATION"/></TD> • </TR> • </xsl:for-each> • </TABLE> • </BODY> • </HTML> • </xsl:template> • </xsl:stylesheet>

  17. D E M O N S T R A T I O N Implementing the example in Oracle

  18. Code: To Create an XML Doc from a Table in the Database XML DOCUMENT PL/SQL CODE DATA DISPLAY IN BROWSER

  19. Code: To Create an XML Doc from a Table in the Database • declare • xmlString CLOB := null; • amount integer:= 1000; • position integer := 1; • charString varchar2(1000); • fileHandle UTL_FILE.FILE_TYPE; •  begin • --we want the result document root to be "Zipcodes" • --to follow our DTD structure • xmlgen.setRowsetTag('Zipcodes'); • --we want the row element to be named "mappings" to follow our • --DTD structure Contd.......

  20. Code: To Create an XML Doc froma Table in the Database • xmlgen.setRowTag('mappings'); • --open the file in "write" mode • fileHandle := utl_file.fopen('d:\test','XML_FOR_ZIPCODES.XML', 'w'); • --set the ERROR tag to be ERROR_RESULTS • xmlgen.setErrorTag('ERROR_RESULT'); • --set the id attribute in the ROW element to be Record - so that it • --shows the number of records fetched • xmlgen.setRowIdAttrName('Record'); • --do not use the null indicator to indicate nullness • xmlgen.useNullAttributeIndicator(false); • Contd.......

  21. Code: To Create an XML Doc from a Table in the Database • --attach the stylesheet to the result document • xmlgen.setStyleSheet('XSL_FOR_ZIPCODES.XSL'); • --this gets the XML out - the 0 indicates no DTD in the generated • --XML document. A value of 1 will provide a DTD description in • --the XML document • xmlString := xmlgen.getXML('select * from scott.zipcodes',0); • --now open the lob data. • dbms_lob.open(xmlString, dbms_lob.lob_readonly); • loop • -- read the lob data • dbms_lob.read(xmlString,amount,position,charString); • Contd.......

  22. Code: To Create an XML Doc from a Table in the Database • utl_file.put_line(fileHandle, charString); • position := position + amount; • end loop; •   exception • when no_data_found then • -- end of fetch, free the lob • dbms_lob.close(xmlString); • dbms_lob.freetemporary(xmlString); • xmlgen.resetOptions; • utl_file.fclose(fileHandle); • when others then • xmlgen.resetOptions; • end;

  23. The XML Document in a Browser

  24. Code: To Create an XML Doc “on the fly” from a Table in the Database • create or replace procedure db2xml2browser is • ------- • ------- • xmlgen.setStyleSheet('\MYDIR\XSL_FOR_ZIPCODES.XSL'); • ------- • ------- • htp.prn(ltrim(charString,' ')); • ------- • -------

  25. The XML Document in a Browser invoked via iAS

  26. Code: To Read Data from an XML Doc into a Table in the Database DATA PL/SQL CODE XML DOCUMENT

  27. Code: To Read Data from an XML Doc into a Table in the Database • declare • charString varchar2(80); • finalStr varchar2(4000) := null; • rowsp integer; • v_FileHandle UTL_FILE.FILE_TYPE; •  begin • -- the name of the table as specified in our DTD • xmlgen.setRowsetTag('Zipcodes'); • -- the name of the data set as specified in our DTD • xmlgen.setRowTag('mappings'); • Contd.......

  28. Code: To Read Data from an XML Doc into a Table in the Database • -- for getting the output on the screen • dbms_output.enable(1000000); • -- open the XML document in read only mode • v_FileHandle := utl_file.fopen('d:\test','XML_NEW_CITIES.XML', 'r'); • loop • begin • utl_file.get_line(v_FileHandle, charString); • exception • when no_data_found then • utl_file.fclose(v_FileHandle); • exit; • end; • Contd.......

  29. Code: To Read Data from an XML Doc into a Table in the Database • dbms_output.put_line(charString); • if finalStr is not null then • finalStr := finalStr || charString; • else • finalStr := charString; • end if; • end loop; • -- for inserting the XML data into the table • rowsp := xmlgen.insertXML('scott.zipcodes',finalStr); •   dbms_output.put_line('Insert Done '||to_char(rowsp)); • xmlgen.resetOptions; • end;

  30. The Sample XML Doc • <?xml version = '1.0'?> • <Zipcodes> • <mappings Record="4"> • <STATE_ABBREVIATION>CA</STATE_ABBREVIATION> • <ZIPCODE>94301</ZIPCODE> • <CITY>Palo Alto</CITY> • </mappings> • <mappings Record="5"> • <STATE_ABBREVIATION>CO</STATE_ABBREVIATION> • <ZIPCODE>80323</ZIPCODE> • <ZIP_CODE_EXTN>9277</ZIP_CODE_EXTN> • <CITY>Boulder</CITY> • </mappings> • </Zipcodes>

  31. New Records in the Table • SQL> select * from zipcodes; • ST ZIPCO ZIP_ CITY • -- ----- ---- ------- ------------- • CA 95123 6111 San Jose • CA 95234 Sunnyvale • AK 72701 Fayetteville • CA 94301 Palo Alto • CO 80323 9277 Boulder

  32. Conclusion • We saw the definition of XML, DTD and XSL. • We acquainted ourselves with the names of some • of the Oracle Tools available for handling XML. • We saw these tools in action with some examples. • The topics covered in this session are building • blocks for the complex, real life requirements of • enterprises. • The available Oracle tools are extremely powerful • and geared to handle all these complexities.

  33. Q & Q U E S T I O N S A N S W E R S A

  34. About Quovera, Redwood Shores, CA, is a Full Service Provider (FSP) that delivers e-business solutions in e-time designed for dot-com, Fortune 1000 and high-growth companies. With over five years proven experience, Quovera provides a single source for Business Modeling, Integrated e-Services, ASP and Strategic Business Process Outsourcing. http://www.quovera.com For the Presentation and White Paper download, visit http://www.quovera.com/forum/index.html NoCOUG 2000, 14th Nov’00, 1.00pm

More Related