如何使用sqlloader导入xml文件到Oracle数据库

在Oracle表数据和xml文件间相互导入导出的PL/SQL 过程 

学校里要求做个题，题目要求是写出将已知格式xml文件中的数据导入Oracle数据库、把数据库中的数据导出成xml文件的过程。

已知格式xml文件如下：

<?xml version="1.0"?>

<PEOPLE>

    <PERSON PERSONID="E01">

        <NAME>Tony Blair</NAME>

        <ADDRESS>10 Downing Street, London, UK</ADDRESS>

        <TEL>(061) 98765</TEL><FAX>(061) 98768</FAX>

        <EMAIL>blair@everywhere.com</EMAIL>

    </PERSON>

    <PERSON PERSONID="E02">

        <NAME>Bill Clinton</NAME>

        <ADDRESS>White House, USA</ADDRESS>

        <TEL>(001) 6400 98765</TEL><FAX>(001) 6400 98769</FAX>

        <EMAIL>bill@everywhere.com</EMAIL>

    </PERSON>

    <PERSON PERSONID="E03">

        <NAME>Tom Cruise</NAME>

        <ADDRESS>57 Jumbo Street, New York, USA</ADDRESS>

        <TEL>(001) 4500 67859</TEL><FAX>(001) 4500 67895</FAX>

        <EMAIL>cruise@everywhere.com</EMAIL>

    </PERSON>

    <PERSON PERSONID="E04">

        <NAME>Linda Goodman</NAME>

        <ADDRESS>78 Crax Lane, London, UK</ADDRESS>

        <TEL>(061) 54 56789</TEL><FAX>(061) 54 56772</FAX>

        <EMAIL>linda@everywhere.com</EMAIL>

    </PERSON>

</PEOPLE>

于是写出过程如下：

--建表

CREATE TABLE PEOPLE

(

   PERSONID VARCHAR2(10) PRIMARY KEY,

   NAME VARCHAR2(20),

   ADDRESS VARCHAR2(60),

   TEL  VARCHAR2(20),

   FAX  VARCHAR2(20),

   EMAIL VARCHAR2(40)

)

--从xml文件载入数据保存到数据库的过程 

create or replace procedure xml2db(xmlfile varchar2) as

  p xmlparser.Parser

  doc xmldom.DOMDocument

  n xmldom.DOMNode

  nl xmldom.DOMNodeList

  len number

  --根据PERSON结点生成插入SQL语句的过程 

  function insertsql(node xmldom.DOMNode) return varchar2

  is

    n xmldom.DOMNode

    nl xmldom.DOMNodeList

    len number

    nnm xmldom.DOMNamedNodeMap

    isql varchar2(200)

  begin

    --取得PERSON结点所有属性 

    nnm := xmldom.getAttributes(node)

    isql := 'insert into people values('

    isql := isql || ''''

    --取得所有属性中的第一个属性，即"PERSONID"，并拼接到SQL语句中 

    isql := isql || xmldom.getNodeValue(xmldom.item(nnm,0))

    isql := isql || ''''

    --取得PERSON结点下的所有结点，准备遍历 

    nl := xmldom.getChildNodes(node)

    len := xmldom.getLength(nl)

    for i in 0..len-1 loop

      --取出第i个结点 

      n := xmldom.item(nl,i)

      isql := isql || ','

      isql := isql || ''''

      --将结点的文本值取出并拼接到SQL语句中

      isql := isql || xmldom.getNodeValue(xmldom.getFirstChild(n))

      isql := isql || ''''

    end loop

    isql := isql || ')'

    return isql

  end insertsql

begin

  p := xmlparser.newParser

  xmlparser.parse(p,xmlfile)

  --转换xml文件成DOM对像 

  doc := xmlparser.getDocument(p)

  xmlparser.freeParser(p)

  --取出所有PERSON元素 

  nl := xmldom.getElementsByTagName(doc,'PERSON')

  len := xmldom.getLength(nl)

  --清空people表的内容 

  delete from people

  for i in 0..len-1 loop

    --取出第i个PERSON元素 

    n := xmldom.item(nl,i)

    --执行插入该PERSON元素所用的SQL语句 

    execute immediate insertsql(n)

  end loop

  commit

  xmldom.freeDocument(doc)

end xml2db

/

--将数据库中的数据导出成xml文件的过程 

create or replace procedure db2xml(xmlfile varchar2) as

  doc xmldom.DOMDocument

  ret xmldom.DOMNode

  peoplenode xmldom.DOMNode

  --遍历整个people表的游标 

 -------------

  --将people表中一行记录转换为元素

  --并插入到DOM文档对像根结点PEOPLE下的过程 

  procedure addperson(doc xmldom.DOMDocument,people xmldom.DOMNode,

                      v_pid varchar2,v_name varchar2,v_addr varchar2,

                      v_tel varchar2,v_fax varchar2,v_email varchar2)

  is

    personelem xmldom.DOMElement

    personnode xmldom.DOMNode

    itemelem xmldom.DOMElement

    itemnode xmldom.DOMNode

    text xmldom.DOMText

  begin

    --创建PERSON结点 

    personelem := xmldom.createElement(doc,'PERSON')

    --设置PERSONID属性 

    xmldom.setAttribute(personelem,'PERSONID',v_pid)

    personnode := xmldom.appendChild(peoplenode,xmldom.makeNode(personelem))

    --向PERSON结点中添加NAME元素

    itemelem := xmldom.createElement(doc,'NAME')

    --  将NAME结点添加到PERSON结点中

    itemnode := xmldom.appendChild(personnode,xmldom.makeNode(itemelem))

    --  创建文本结点

    text := xmldom.createTextNode(doc,v_name)

    --  将文本结点添加到NAME结点下，以构成完整NAME元素

    itemnode := xmldom.appendChild(itemnode,xmldom.makeNode(text))

    --向PERSON结点中添加ADDRESS元素

    itemelem := xmldom.createElement(doc,'ADDRESS')

    itemnode := xmldom.appendChild(personnode,xmldom.makeNode(itemelem))

    text := xmldom.createTextNode(doc,v_addr)

    itemnode := xmldom.appendChild(itemnode,xmldom.makeNode(text))

    --向PERSON结点中添加TEL元素

    itemelem := xmldom.createElement(doc,'TEL')

    itemnode := xmldom.appendChild(personnode,xmldom.makeNode(itemelem))

    text := xmldom.createTextNode(doc,v_tel)

    itemnode := xmldom.appendChild(itemnode,xmldom.makeNode(text))

    --向PERSON结点中添加FAX元素

    itemelem := xmldom.createElement(doc,'FAX')

    itemnode := xmldom.appendChild(personnode,xmldom.makeNode(itemelem))

    text := xmldom.createTextNode(doc,v_fax)

    itemnode := xmldom.appendChild(itemnode,xmldom.makeNode(text))

    --向PERSON结点中添加EMAIL元素

    itemelem := xmldom.createElement(doc,'EMAIL')

    itemnode := xmldom.appendChild(personnode,xmldom.makeNode(itemelem))

    text := xmldom.createTextNode(doc,v_email)

    itemnode := xmldom.appendChild(itemnode,xmldom.makeNode(text))

  end addperson

begin

  --创建一个新DOM文档对像 

  doc := xmldom.newDOMDocument

  --为文档添加根结点PEOPLE

  peoplenode := xmldom.makeNode(xmldom.createElement(doc,'PEOPLE'))

  ret := xmldom.appendChild(xmldom.makeNode(doc),peoplenode)

  --使用游标遍历people中的每行，生成每一行对应的PERSON元素并添加到PEOPLE根结点中

  for v_row in cur_people loop

    addperson(doc,peoplenode,v_row.personid,v_row.name,

              v_row.address,v_row.tel,v_row.fax,v_row.email)

  end loop

  --将结果写入指定文件 

  xmldom.writeToFile(doc,xmlfile)

  xmldom.freeDocument(doc)

end db2xml

import javax.servlet.*

import javax.servlet.http.*

import java.io.*

import java.util.*

import javax.xml.parsers.*

import javax.xml.transform.*

import org.w3c.dom.*

import org.xml.sax.*

import javax.xml.transform.dom.DOMSource

import javax.xml.transform.stream.StreamResult

public class CreateXMLForEnterServelet extends HttpServlet {

private static final String CONTENT_TYPE = "text/htmlcharset=GBK"

//Initialize global variables

public void init() throws ServletException {

}

//Process the HTTP Get request

public void doGet(HttpServletRequest request, HttpServletResponse response) throws

ServletException, IOException {

response.setContentType(CONTENT_TYPE)

PrintWriter out = response.getWriter()

//获取传递过来的值

String databaseType = request.getParameter("select")

String userName = request.getParameter("userName")

String userPassword = request.getParameter("userPassword")

String dbName = request.getParameter("dbName")

String dbSouse = request.getParameter("dbSouces")

int port = Integer.parseInt(request.getParameter("portNum"))

Document doc

Element root

try

{

DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance()

DocumentBuilder db = dbf.newDocumentBuilder()

String path = request.getRealPath("/")

File file = new File( path + "/" + "DBInfo.xml")

if(!file.exists())

{

doc = db.newDocument()

root = doc.createElement("DBInfo")

doc.appendChild(root)

}

else

{

doc = db.parse(file)

root = doc.getDocumentElement()

}

//添加子节点

Element rowNode = doc.createElement(databaseType)

root.appendChild(rowNode)

Node node = doc.createElement("Driver")

Node nodestr = doc.createElement("ConnectString")

Node serverName = doc.createElement("ServerName")

Node nodeName = doc.createElement("UserName")

Node nodePWD = doc.createElement("PassWord")

Node nodePort = doc.createElement("Port")

Node nodeDB = doc.createElement("DataBase")

rowNode.appendChild(node)

rowNode.appendChild(nodestr)

rowNode.appendChild(serverName)

rowNode.appendChild(nodeDB)

rowNode.appendChild(nodeName)

rowNode.appendChild(nodePWD)

rowNode.appendChild(nodePort)

String nodeValue//Driver

String nodeConStr//连接字符串

if(databaseType.equals("ORACLE"))

{

nodeValue = "oracle.jdbc.driver.OracleDriver"

nodeConStr = "jdbc:oracle:thin"

}

else

{

nodeValue = "com.microsoft.jdbc.sqlserver.SQLServerDriver"

nodeConStr = "jdbc:microsoft:sqlserver"

}

Text text = doc.createTextNode(nodeValue)

Text textstr = doc.createTextNode(nodeConStr)

Text textName = doc.createTextNode(userName)

Text textPWD = doc.createTextNode(userPassword)

Text textServer = doc.createTextNode(dbName)

Text textPort = doc.createTextNode(String.valueOf(port))

Text textDB = doc.createTextNode(dbSouse)

node.appendChild(text)

nodestr.appendChild(textstr)

nodeName.appendChild(textName)

nodePWD.appendChild(textPWD)

serverName.appendChild(textServer)

nodePort.appendChild(textPort)

nodeDB.appendChild(textDB)

DOMSource doms = new DOMSource(doc)

StreamResult sr = null

TransformerFactory tff = TransformerFactory.newInstance()

Transformer tf = tff.newTransformer()

sr = new StreamResult(file)

tf.transform(doms,sr)

out.println("<h1>Create XMLFile Is Ok!!</h1>")

request.getRequestDispatcher("index.jsp").forward(request,response)

}

catch(Exception ex)

{

out.println("<h1>生成 XML 配置文件时出现错误：</h1><br/><h4>"+ex+"</h4>")

out.println("<br/><br/>")

out.print("<a href='index.jsp'>【点这里回到首页】</a>")

}

out.close()

}

//Process the HTTP Post request

public void doPost(HttpServletRequest request, HttpServletResponse response) throws

ServletException, IOException {

doGet(request, response)

}

//Clean up resources

public void destroy() {

}

}

---