Introduction to XML n XML stands for EXtensible

Introduction to XML n XML stands for EXtensible Markup Language n XML was designed to describe data. n XML tags are not predefined unlike HTML n XML DTD and XML Schema define rules to describe data n XML example of semi structured data

Building Blocks of XML n Elements (Tags) are the primary components of XML documents. Element FNAME nested inside <AUTHOR id = 123> element Author. Element <FNAME> JAMES</FNAME> Author with <LNAME> RUSSEL</LNAME> Attr id </AUTHOR> <!- I am comment -> n Attributes provide additional information about Elements. Values of the Attributes are set inside the Elements n Comments stats with <!- and end with ->

XML DTD n A DTD is a set of rules that allow us to specify our own set of elements and attributes. n DTD is grammar to indicate what tags are legal in XML documents. c n XML Document is valid if it has an attached DTD and document is structured according to rules defined in DTD.

DTD Example <BOOKLIST> <BOOK GENRE = “Science” FORMAT = “Hardcover”> <AUTHOR> <FIRSTNAME> RICHRD </FIRSTNAME> <LASTNAME> KARTER </LASTNAME> </AUTHOR> </BOOKS> Xml Document And Corresponding DTD <!DOCTYPE BOOKLIST[ <!ELEMENT BOOKLIST(BOOK)*> <!ELEMENT BOOK(AUTHOR)> <!ELEMENT AUTHOR(FIRSTNAME, LASTNAM E)> <!ELEMENT FIRSTNAME(#PCDATA)> <!ELEMENT>LASTNAME(#PCDATA) > <!ATTLIST BOOK GENRE (Science|Fiction)#REQUIRED> <!ATTLIST BOOK FORMAT (Paperback|Hardcover) “Paper. Back”>]>

XML Schema n Serves same purpose as database schema n Schemas are written in XML n Set of pre-defined simple types (such as string, integer) n Allows creation of user-defined complex types

XML Schema n RDBMS Schema (s_id integer, s_name string, s_status string) n XMLSchema <xs: schema> <xs: complex. Type name = “Studnet. Type”> <xs: attribute name=“id” type=“xs: string” /> <xs: element name=“Name” type=“xs: string /> <xs: element name=“Age” type=“xs: integer” /> <xs: element name=“Email” type=“xs: string” /> </xs: complex. Type> <xs: element name=“Student” type=“Student. Type” /> XML Document and Schema </xs: schema> <Students> <Student id=“p 1”> <Name>Allan</Name> <Age>62</Age> <Email>allan@abc. com </Email> </Students>

XML Query Languages n Requirement Same functionality as database query languages (such as SQL) to process Web data n Advantages n Query selective portions of the document (no need to transport entire document) n Smaller data size mean lesser communication cost

XQuery n XQuery to XML is same as SQL to RDBMS n Most databases supports XQuery n XQuery is built on XPath operators (XPath is a language that defines path expressions to locate document data)

XPath Example <Student id=“s 1”> <Name>John</Name> <Age>22</Age> <Email>jhn@xyz. com</Email> </Student> XPath: /Student[Name=“John”]/Email Extracts: <Email> element with value “jhn@xyz. com”

Oracle and XML n XML Support in Oracle XDK (XML Developer Kit) XML Parser for PL/SQL XPath XSLT

Oracle and XML n XML documents are stored as XML Type ( data type for XML ) in Oracle n Internally CLOB is used to store XML n To store XML in database create table with one XMLType column n Each row will contain one of XML records from XML document n Database Table: n Database Row : XML Document XML Record

Examples <Patients> <Patient id=“p 1”> <Name>John</Name> <Address> <Street>120 Northwestern Ave</Street> </Address> </Patient> <Patient id=“p 2”> <Name>Paul</Name> <Address> <Street>120 N. Salisbury</Street> </Address> </Patients>

Example Create table pr. Table(patient. Record XMLType); DECLARE pr. XML CLOB; BEGIN -- Store Patient Record XML in the CLOB variable pr. XML : = '<Patient id=“p 1"> <Name>John</Name> <Address> <Street>120 Northwestern Ave</Street> </Address> </Patient>‘ ; -- Now Insert this Patient Record XML into an XMLType column INSERT INTO pr. Table (patient. Record) VALUES (XMLTYPE(pr. XML)); n END; n n n n

Example TO PRINT PATIENT ID of ALL PATIENTS SELECT EXTRACT(p. patient. Record, '/Patient/@id'). get. String. Val() FROM pr. Table p; USE XPATH

Oracle JDBC n JDBC an API used for database connectivity n Creates Portable Applications n Basic Steps to develop JDBC Application n n Import JDBC classes (java. sql. *). Load JDBC drivers Connect and Interact with database Disconnect from database

Oracle JDBC n Driver. Manager provides basic services to manage set of JDBC drivers n Connection object sends queries to database server after a connection is set up n JDBC provides following three classes for sending SQL statements to server n Statement SQL statements without parameters n Prepared. Statement SQL statements to be executed multiple times with different parameters n Callable. Statement Used for stored procedures

Oracle JDBC n SQL query can be executed using any of the objects. (Statement, Prepared. Statement, Callable. Statement) n Syntax (Statement Object ) Public abstract Result. Set execute. Query(String sql) throws SQLException n Syntax (Prepared. Statement, Callable. Statement Object ) Public abstract Result. Set execute. Query() throws SQLException n Method executes SQL statement that returns Result. Set object (Result. Set maintains cursor pointing to its current row of data. )

Oracle JDBC (Example) Import java. sql. *; Import java. io; Class simple{ public static void main(String[] args) throws Exception{ Connection conn=null; try{ String con. Str = "jdbc: oracle: thin: @oracle. cs. purdue. edu: 1521: orb"; Driver. Manager. register. Driver(new oracle. jdbc. driver. Oracle. Driver()); conn = Driver. Manager. get. Connection(con. Str, ”username”, ”passwd"); Statement cursor = conn. create. Statement(); // Connection Est. Result. Set rset = stmt. execute. Query(“Select* from table_name”); while(orset. next()){ System. out. println(“Printing column name ”+orest. get. String. Val(1)); } }Catch(Class. Not. Found. Exception e){} cursor. close(); conn. close(); }