XML and Oracle 8 i A How-To

XML and Oracle 8 i : A How-To Guide for PL/SQL Users OOW 2000, 4 th Oct’ 00, 8. 00 am Paper #474 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 • See the examples in action

Some Basics Revisited XML - e. Xtensible 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 e. Xtensible Style Language (XSL).

Some Basics Revisited Example of XML 05. 00 hrs 00. 00 hrs

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.

Some Basics Revisited Example of DTD Note: The actual DTD syntax is covered in later slides

Some Basics Revisited XSL – e. Xtensible 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.

Some Basics Revisited Some Example of XSL Basics Revisited HOUSE RULES Rule # Wake up at : Hit the bed at :

Some Basics Revisited Output of the example in a browser

XML in Oracle • Oracle 8 i 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

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: from the database and convert Read data 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.

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

First Cut DTD

Extending the DTD Among the many keywords available for defining DTDs, let’s look at the “Attribute” keyword.

XSL for the XML/DTD xsl">

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

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

Code: To Create an XML Doc from a Table in the Database declare xml. String CLOB : = null; amount integer: = 1000; position integer : = 1; char. String varchar 2(1000); file. Handle UTL_FILE_TYPE; begin --we want the result document root to be "Zipcodes" --to follow our DTD structure xmlgen. set. Rowset. Tag('Zipcodes'); --we want the row element to be named "mappings" to follow our --DTD structure Contd. . . .

Code: To Create an XML Doc from a Table in the Database xmlgen. set. Row. Tag('mappings'); --open the file in "write" mode file. Handle : = utl_file. fopen('d: test', 'XML_FOR_ZIPCODES. XML', 'w'); --set the ERROR tag to be ERROR_RESULTS xmlgen. set. Error. Tag('ERROR_RESULT'); --set the id attribute in the ROW element to be Record - so that it --shows the number of records fetched xmlgen. set. Row. Id. Attr. Name('Record'); --do not use the null indicator to indicate nullness xmlgen. use. Null. Attribute. Indicator(false); Contd. . . .

Code: To Create an XML Doc from a Table in the Database --attach the stylesheet to the result document xmlgen. set. Style. Sheet('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 xml. String : = xmlgen. get. XML('select * from scott. zipcodes', 0); --now open the lob data. dbms_lob. open(xml. String, dbms_lob. lob_readonly); loop -- read the lob data dbms_lob. read(xml. String, amount, position, char. String); Co

Code: To Create an XML Doc from a Table in the Database utl_file. put_line(file. Handle, char. String); position : = position + amount; end loop; exception when no_data_found then -- end of fetch, free the lob dbms_lob. close(xml. String); dbms_lob. freetemporary(xml. String); xmlgen. reset. Options; utl_file. fclose(file. Handle); when others then xmlgen. reset. Options; end;

The XML Document in a Browser

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

Code: To Read Data from an XML Doc into a Table in the Database declare char. String varchar 2(80); final. Str varchar 2(4000) : = null; rowsp integer; v_File. Handle UTL_FILE_TYPE; begin -- the name of the table as specified in our DTD xmlgen. set. Rowset. Tag('Zipcodes'); -- the name of the data set as specified in our DTD xmlgen. set. Row. Tag('mappings'); ntd. . . .

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_File. Handle : = utl_file. fopen('d: test', 'XML_NEW_CITIES. XML', 'r'); loop begin utl_file. get_line(v_File. Handle, char. String); exception when no_data_found then utl_file. fclose(v_File. Handle); exit; end; Contd. . . .

Code: To Read Data from an XML Doc into a Table in the Database dbms_output. put_line(char. String); if final. Str is not null then final. Str : = final. Str || char. String; else final. Str : = char. String; end if; end loop; -- for inserting the XML data into the table rowsp : = xmlgen. insert. XML('scott. zipcodes', final. Str); dbms_output. put_line('Insert Done '||to_char(rowsp)); xmlgen. reset. Options; end;

" src="https://present5.com/presentation/13d1e2b283e2a2e04dc15ba396e488fb/image-28.jpg" alt="The Sample XML Doc " /> The Sample XML Doc CA 94301 Palo Alto CO 80323 9277 Boulder

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

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 • We saw these tools in action with some examples. XML. • 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.

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

About the Author Eashwar Iyer is a Project Manager with Millennia Vision. He has over twelve years of experience in the IT industry. He has been involved in diverse projects in the US and India and has conducted many training sessions including one on Oracle Designer at Oracle Corporation, Saudi Arabia. His work includes various software projects in Oracle, Networking Support and Training. He holds a degree in Mathematics, a post-graduate degree in Management and a professional degree in Systems Analysis and Design. eiyer@mvc. com Millennia Vision, Redwood Shores, CA, is a Full Service Provider (FSP) that delivers e-business solutions in e-time designed for dotcom, Fortune 1000 and high-growth companies. With over five years proven experience, Millennia Vision provides a single source for Business Modeling, Integrated e-Services, ASP and Strategic Business Process Outsourcing. http: //www. mvsn. com Paper #474, OOW 2000, 4 th Oct’ 00, 8. 00 am