1 / 35

XML

XML. Veena Singh. CS 8630, Summer 2004 . Transferring Data between DBMSs. I am going to demonstrate two different ways to transfer data between DBMSs using XML. 1) First way with Procedures (MS Access 2002, Oracle 9i) 2) Second way with XMLSPY. Different ways to use XML.

zabrina
Download Presentation

XML

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 Veena Singh CS 8630, Summer 2004 Transferring Data between DBMSs

  2. I am going to demonstrate two different ways to transfer data between DBMSs using XML. 1) First way with Procedures (MS Access 2002, Oracle 9i) 2) Second way with XMLSPY Different ways to use XML

  3. Save in Access as XML

  4. Export XML Select Options • You need the Data (XML) file and the Schema of the data (data definition, similar to DDL) to transfer data. • You need to select the Presentation of your data (XSL) also if you want to display data with a web browser. • This will create a xml and a xsd file.

  5. Sample MS Access XML file • <?xml version = "1.0" encoding = "UTF-8"?> • <dataroot> • <employee> • <eID>456</eID> • <eLast>Last456</eLast> • <eFirst>First456</eFirst> • <eTitle>Software Engineer</eTitle> • <eSalary>45456</eSalary> • </employee> • <employee> • <eID>789</eID> • <eLast>Last789</eLast> • <eFirst>First789</eFirst> • <eTitle>Database Administrator</eTitle> • <eSalary>78789</eSalary> • </employee> • . • . • . • <employee> • <eID>999</eID> • <eLast>Last999</eLast> • <eFirst>First999</eFirst> • <eTitle>Manager</eTitle> • <eSalary>100999</eSalary> • </employee> • </dataroot> • Code snippet for Access XML for employee table

  6. MS Access XML Schema • <?xml version="1.0" encoding="UTF-8"?> • <xsd:schema xmlns:xsd="http://www.w3.org/2000/10/XMLSchema" xmlns:od="urn:schemas-microsoft-com:officedata"> • <xsd:element name="dataroot"> • <xsd:complexType> • <xsd:choice maxOccurs="unbounded"> • <xsd:element ref="employee"/> • </xsd:choice> • </xsd:complexType> • </xsd:element> • <xsd:element name="employee"> • <xsd:annotation> • <xsd:appinfo> • <od:index index-name="PrimaryKey" index-key="eID " primary="yes" unique="yes" clustered="no"/> • <od:index index-name="eID" index-key="eID " primary="no" unique="no" clustered="no"/> • <od:index index-name="eTitle" index-key="eTitle " primary="no" unique="no" clustered="no"/> • </xsd:appinfo> • </xsd:annotation> • <xsd:complexType> • <xsd:sequence> • <xsd:element name="eID" od:jetType="text" od:sqlSType="nvarchar" od:nonNullable="yes"> • <xsd:simpleType> • <xsd:restriction base="xsd:string"> • <xsd:maxLength value="3"/> • </xsd:restriction> • </xsd:simpleType> • </xsd:element> • . • . • . • </xsd:sequence> • </xsd:complexType> • </xsd:element> • </xsd:schema> • Code snippet of XSD format used by Access

  7. MS Access XSL File • <?xml version = "1.0" encoding = "UTF-8"?> • <xsl:transform xmlns:xsl = "http://www.w3.org/1999/XSL/Transform" version = "1.0"> • <xsl:template match = "dataroot"> • <xsl:element name = "ROWSET"> • <xsl:apply-templates select = "child::*"/> • </xsl:element> • </xsl:template> • <xsl:template match = "child::*"> • <xsl:element name = "ROW"> • <xsl:attribute name = "num"> • <xsl:value-of select = "position()"/> • </xsl:attribute> • <xsl:for-each select="child::*"> • <xsl:copy-of select = "."/> • </xsl:for-each> • </xsl:element> • </xsl:template> • </xsl:transform> • Code snippet of XSL file

  8. Conversion To input the XML file from Access to Oracle, run the XML file output from Access through TIBCO XML Transform, XMLSPY, or some other conversion software. This is required because the XML format in Access is different than the XML format used by Oracle.

  9. XML Format used by Oracle • <?xml version = "1.0" encoding = "UTF-8"?> • <ROWSET> • <ROW num = "1"> • <eID>456</eID> • <eLast>Last456</eLast> • <eFirst>First456</eFirst> • <eTitle>Software Engineer</eTitle> • <eSalary>45456</eSalary> • </ROW> • . • . • <ROW num = "19"> • <eID>999</eID> • <eLast>Last999</eLast> • <eFirst>First999</eFirst> • <eTitle>Manager</eTitle> • <eSalary>100999</eSalary> • </ROW> • </ROWSET> • XML data given out after conversion

  10. Steps to input into Oracle To input the XML data into Oracle, follow these steps: • Create a table with the given name in Oracle(e.g. We will be creating an ‘employee’ table.). • Create procedure insProc. • Run the procedure insProc with the Oracle XML data.

  11. Create Table & Procedures • CREATE TABLE Employee ( • eID VARCHAR(11), • eLast VARCHAR(20), • eFirst VARCHAR(20), • eTitle VARCHAR(20), • eSalary FLOAT • ); • -- insert XML data into table • --proc insProc • create or replace procedure insProc(xmlDoc IN CLOB, tableName IN VARCHAR2) is • insCtx DBMS_XMLSave.ctxType; • rows number; • begin • insCtx := DBMS_XMLSave.newContext(tableName); -- get the context handle • rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc); -- this inserts the document • DBMS_XMLSave.closeContext(insCtx); -- this closes the handle • end; • / • -- The following example uses default XML mapping. • -- print result • CREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is • xmlstr varchar2(32767); • line varchar2(2000); • begin • xmlstr := dbms_lob.SUBSTR(result,32767); • loop • exit when xmlstr is null; • line := substr(xmlstr,1,instr(xmlstr,chr(10))-1); • dbms_output.put_line('| '||line); • xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1); • end loop; • end; • /

  12. Run the procedures • -- to run the procedures • declare • v_doc CLOB; • begin • v_doc := '<?xml version="1.0"?>' || Chr(10) || ' • <ROWSET> • <ROW num = "1"> • <eID>456</eID> • <eLast>Last456</eLast> • <eFirst>First456</eFirst> • <eTitle>Software Engineer</eTitle> • <eSalary>45456</eSalary> • </ROW> • . • . • </ROWSET> • '; • --printClobOut(v_doc); • insProc(v_doc, 'scott.employee'); • -- queryCtx := DBMS_XMLQuery.newContext('select * from employee' ); • end; • / • Code snippet to input XML data into Oracle

  13. Export XML data from Oracle • -- The following example uses default XML mapping. • -- print result • CREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is • xmlstr varchar2(32767); • line varchar2(2000); • begin • xmlstr := dbms_lob.SUBSTR(result,32767); • loop • exit when xmlstr is null; • line := substr(xmlstr,1,instr(xmlstr,chr(10))-1); • dbms_output.put_line('| '||line); • xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1); • end loop; • end; • / • -- generate XML for query result using default mapping • declare • queryCtx DBMS_XMLquery.ctxType; • result CLOB; • begin • -- set up the query context...! • queryCtx := DBMS_XMLQuery.newContext('select * from student); • -- get the result..! • result := DBMS_XMLQuery.getXML(queryCtx); • -- Now you can use the result to put it in tables/send as messages.. • printClobOut(result); • DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle.. • end; • / • SQL code to run export data out of Oracle

  14. Oracle XML file The procedure on the previous slide will print the XML data out on the SQL Plus terminal. Copy and paste the data into a text file and save it as an XML file.

  15. Sample Oracle XML data • <?xml version = "1.0" encoding = "UTF-8"?> • <ROWSET> • <ROW num="1"> • <PID>111-11-1111</PID> • <DOB>6/26/1973 0:0:0</DOB> • <FIRSTNAME>Ying</FIRSTNAME> • <LASTNAME>Ma</LASTNAME> • <STATUS>Graduate</STATUS> • <MAJOR>CSE</MAJOR> • </ROW> • <ROW num="2">"> • <MAJOR>NUR</MAJOR> • </ROW> • . • . • . • /ROWSET> • Code snippet of XML data given out by Oracle

  16. Converting To convert the Oracle XML into Access XML run the XML file through TIBCO XML Transform, XMLSPY, or some other conversion software. Save the XML output file after the software has converted the XML file into another XML format.

  17. Sample output after conversion • <?xml version = "1.0" encoding = "UTF-8"?> • <dataroot> • <student> • <pID>111-11-1111</pID> • <DateOfBirth>6/26/1973 0:0:0</DateOfBirth> • <FirstName>Ying</FirstName> • <LastName>Ma</LastName> • <status>Graduate</status> • <major>CSE</major> • </student> • <student> • <pID>111-22-2222</pID> • <DateOfBirth>5/15/1982 0:0:0</DateOfBirth> • <FirstName>Ana</FirstName> • <LastName>White</LastName> • <status>Freshman</status> • <major>NUR</major> • </student> • . • . • </dataroot> • Code snippet of XML data given out after transformation

  18. Importing into Access To import the XML data into Access, go to the MS Access menu option  File\Get External Data\Import. Import the XML file. Access will create a table for the XML data imported. If there is a table already existing with the same name, it will ask if the data needs to be appended to the existing table or if the data needs to replace the existing table with the new data.

  19. XMLSPY

  20. Pick A Source Database

  21. Login

  22. Click Choose database table

  23. Pick a table

  24. Click Import

  25. XML data from Oracle

  26. Exporting

  27. Click Export to database

  28. Choose an option

  29. Where to save it

  30. Saving as MS Access file

  31. Export Completed

  32. Users table is now in MS Access

  33. Table in MS Access

  34. Summary There are different methods to get the XML data across to different databases. • Two methods were shown in this presentation. • There are other software packages like XMLSPY that will transfer the data from one DBMS to another.

  35. REFERENCES Internet (from science.kennesaw.edu/~mguimara/8630/07-06-04-Tuesday-B.ppt). World Wide Web (from www.eas.asu.edu/~advdb/). World Wide Web (from www.tibco.com). World Wide Web (from www.xml.com). World Wide Web (from www.xml.org ). World Wide Web (from www.xmlspy.com).

More Related