批量装载XML文件到SQL SERVER 2K

2022-07-15 80酷酷网 80kuku.com

　　server|xml

出于项目的需求，要从一个服务器的SQL导出数据然后导入到另一SQL服务器，项目要求不能作简单的数据同步，所以想采用XML数据格式处理。网上看到很多文章都是大同小异，说的不是很详细，例子也全，基本都看不大懂。现在还有俩个问题：

1、datetime类型字段有没有更好的处理方法？

2、主键重复问题，不忽略而是替换怎么处理？有什么参数嘛？

以下是偶的代码，共三部分：形成SCHEMA文件、从SQL SERVER创建XML数据、导入XML到SQL SERVER

include文件tablename内容：

<%
const tableName = "userinfo"
const connString = "provider=SQLOLEDB.1;data source=.;UID=sa;PWD=;database=reporthzej"
%>

形成SCHEMA文件：

<%
Dim sFileName
sFileName = Server.MapPath(".") & "\Schema\"&tableName&".xml"

Dim conn
'数据库连接对象
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = connString
conn.provider = "SQLXMLOLEDB.3.0"
conn.open
' conn.open "provider=SQLXMLOLEDB.3.0;data provider=SQLOLEDB;data source=jcbdell;initial catalog=lwgtest;user id=sajt;password=sajt;"
' Conn.CursorLocation = 3 'adUseClient

Dim cmd
'创建命令对象
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = conn
' cmd.Properties("ClientSideXML") = "True"

'生成映射文件的SQL语句
cmd.CommandText = "Select top 0 * from "&tableName&" for xml auto, xmldata"

  Dim stmXMLout
  Set stmXMLout = CreateObject("ADODB.Stream")
  stmXMLout.Open
  cmd.Properties("Output Stream").Value = stmXMLout
  cmd.Properties("xml root") = "root"

cmd.Execute , , 1024 'adExecuteStream

  stmXMLout.Position = 0
'  stmXMLout.Charset = "utf-8"
'  stmXMLout.ReadText(adReadAll)

  Set Fs = Server.CreateObject("Scripting.FileSystemObject")
  '把生成的XML文件存入到Schema目录下，文件名为表名，扩展名为.XML
  IF Fs.FileExists(sFileName) Then
   Fs.DeleteFile sFileName, True
  END IF

stmXMLout.SaveToFile(sFileName)

  '载入生成的XML文件
  Set xmlDoc = Server.CreateObject("MSXML2.DOMDocument")
  xmlDoc.async = "false"

xmlDoc.load(sFileName)

  '对Schema元素增加属性
  Set objNodeList = xmlDoc.getElementsByTagName("Schema")
  call objNodeList.item(0).setAttribute("xmlns:sql","urn:schemas-microsoft-com:xml-sql")

  '对ElementType元素增加属性
  Set objNodeList = xmlDoc.getElementsByTagName("ElementType")
  call objNodeList.item(0).setAttribute("sql:relation",tableName)

  '保存XML文件
  xmlDoc.Save(sFileName)

Set xlmDoc = Nothing
Set stmXMLout = Nothing
conn.Close
Set conn = Nothing

%>
  <script language="javascript">
   alert("影射文件已重新生成！ ");
  </script>

从SQL SERVER创建XML数据：

<%
'生成XML文件
Dim conn
'数据库连接对象
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = connString
conn.provider = "SQLXMLOLEDB.3.0"
conn.open

Set FsSer = Server.CreateObject("scripting.filesystemobject")

  Dim cmdSer
  Set cmdSer = CreateObject("ADODB.Command")
  Set cmdSer.ActiveConnection = conn

  Dim stmXMLoutSer
  Set stmXMLoutSer = CreateObject("ADODB.Stream")

  stmXMLoutSer.Open
  cmdSer.Properties("Output Stream") = stmXMLoutSer
  'cmdSer.CommandText = "select * from "&tableName&" For XML Auto"
  cmdSer.CommandText = "select UName, passwd,deptno, DeptName , DeptServer,DeptDatabase,BZ, convert(nvarchar(80), CreateTime, 120) as CreateTime , convert(nvarchar(80), RegTime, 120) as RegTime,ParComCode,ParComName,ParComServer,ParComDatabase,MustParCom,LXR,Tel,Email,           HandTel,nProp,nReg,ParComDataServer,DeptDataServer,FtpUser,FTPPass from userinfo for xml auto" '有日期时间型字段的都要如此处理
  cmdSer.Properties("xml root")= "ROOT"

  cmdSer.Execute , , 1024 'adExecuteStream

  Call DealError(conn)

stmXMLoutSer.Position = 0

FileName = "d:\test\"&tableName&".xml"

  IF FsSer.FileExists(FileName) Then
   FsSer.DeleteFile FileName,True
  END IF

  stmXMLoutSer.SaveToFile(FileName)

  Set conn = Nothing
  Set cmdSer = Nothing
  Set stmXMLoutSer = Nothing
  Set FsSer = Nothing
Response.write "已生成XML文件"
Response.End

'错误处理，显示数据连接的错误信息
Sub DealError(Conn)
  If Conn.Errors.Count > 0 Then
   Response.Write "<table align=center>"
   Response.Write "<tr><td align=center><font face=宋体 size=2 color=red>系统共发生了"&Conn.Errors.Count&"个错误！</font>< td></tr>"
   For ErrorId = 0 To Conn.Errors.Count - 1

    Response.Write "<tr><td align=center><font face=宋体 size=2 color=red>错误"&ErrorId&"："&Conn.Errors(ErrorId). Description&"</font><td></tr>"
   Next
   Response.write "</table>"
  End If
     End Sub
%>

导入XML到SQL SERVER:

<%
'存储XML文件的数据

  '首先创建XML文件批量装载的对象
  set objBLSer = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
  objBLSer.ConnectionString= connString

  '发生错误时错误日志的路径及文件名称
  objBLSer.ErrorLogFile = Server.MapPath(".") & "\Error.xml"
  '忽略主健重复错误信息
  objBLSer.IgnoreDuplicateKeys = True
  '检查约束
  objBLSer.CheckConstraints = True

  '执行（映射文件、数据文件）
  SchemaFile = "d:\test\Schema\"&tableName&".xml"
  DataFile = "d:\test\"&tableName&".xml"
  response.write SchemaFile & "
"
  response.write DataFile & "
"
  objBLSer.Execute SchemaFile, DataFile

  set objBLSer=Nothing
%>

以上方法速度相当快！如果项目不受约束可以应用的话。