XMLDifferent ways to use XMLSave in Access as XMLExport XML Select OptionsSample MS Access XML fileMS Access XML SchemaMS Access XSL FileConversionXML Format used by OracleSteps to input into OracleCreate Table & ProceduresRun the proceduresExport XML data from OracleOracle XML fileSample Oracle XML dataConvertingSample output after conversionImporting into AccessXMLSPYPick A Source DatabaseLoginClick Choose database tablePick a tableClick ImportXML data from OracleExportingClick Export to databaseChoose an optionWhere to save itSaving as MS Access fileExport CompletedUsers table is now in MS AccessTable in MS AccessSummaryREFERENCESCS 8630 Database Administration, Dr. GuimaraesXML Veena SinghCS 8630, Summer 2004 Transferring Data between DBMSsCS 8630 Database Administration, Dr. GuimaraesDifferent ways to use XML•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 XMLSPYCS 8630 Database Administration, Dr. GuimaraesSave in Access as XMLCS 8630 Database Administration, Dr. GuimaraesExport 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.CS 8630 Database Administration, Dr. GuimaraesSample 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>•B•.•.•.•B•<employee>•<eID>999</eID>•<eLast>Last999</eLast>•<eFirst>First999</eFirst>•<eTitle>Manager</eTitle>•<eSalary>100999</eSalary>•</employee>•</dataroot>•B••Code snippet for Access XML for employee tableCS 8630 Database Administration, Dr. GuimaraesMS 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>•.•.•.•B•</xsd:sequence>•</xsd:complexType>•</xsd:element>•</xsd:schema>•B•Code snippet of XSD format used by AccessCS 8630 Database Administration, Dr. GuimaraesMS 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>•BB•Code snippet of XSL fileCS 8630 Database Administration, Dr. GuimaraesConversionTo 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.CS 8630 Database Administration, Dr. GuimaraesXML 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>•.•.•B•<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 conversionCS 8630 Database Administration, Dr. GuimaraesSteps to input into OracleTo 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.CS 8630 Database Administration, Dr. GuimaraesCreate Table & Procedures•CREATE TABLE Employee (•eID VARCHAR(11),•eLast VARCHAR(20),•eFirst VARCHAR(20),•eTitle VARCHAR(20),•eSalary FLOAT•);•B•-- 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;•/•BCS 8630 Database Administration, Dr. GuimaraesRun 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