xml文件如何写入sql数据库

sqlserver2005分解并导入xml文件[@more@]

1.

一次性导入：

declare

@idoc

int

declare

@doc

xml

select

@doc=bulkcolumn

from

openrowset(bulk

n'e:mstarindustrycodes.xml',

single_blob)

as

x

exec

sp_xml_preparedocument

@idoc

output,

@doc

select

*

into

tmp_tab

from

openxml

(@idoc,

'/root/record'/',2)

with

(

industrycode

varchar(10)

,industryglobalid

varchar(10)

,industryname

varchar(100)

,sectorcode

varchar(10)

,sectorglobalid

varchar(10)

,sectorname

varchar(100)

,supersectorcode

varchar(10)

,supersectorname

varchar(100)

,groupcode

varchar(10)

,groupname

varchar(100)

,countryid

varchar(3)

)

exec

sp_xml_removedocument

@idoc

select

*

from

tmp_tab

2.

先导入到表中varchar(max)列，然后再用openxml解析，读出。

--

使用single_clob参数，tmp_raw中字段为varcahr(max)类型

select

*

into

tmp_raw

from

openrowset(bulk

n'e:mstarindustrycodes.xml',

single_clob)

as

x

declare

@idoc

int

declare

@doc

xml

select

@doc

=

bulkcolumn

from

tmp_raw

exec

sp_xml_preparedocument

@idoc

output,

@doc

select

top

10

*

from

openxml

(@idoc,

'/root/record',

1)

with

(

industrycode

varchar(10)

,industryglobalid

varchar(10)

,industryname

varchar(100)

,sectorcode

varchar(10)

,sectorglobalid

varchar(10)

,sectorname

varchar(100)

,supersectorcode

varchar(10)

,supersectorname

varchar(100)

,groupcode

varchar(10)

,groupname

varchar(100)

,countryid

varchar(3)

)

exec

sp_xml_removedocument

@idoc

var tmpquery2:tadoquery

tmpfile:string

tmptable:tadotable

begin

if application.MessageBox( '导入答案将会清空以前的考生答案，确定是否继续？ ', '提醒 ',MB_OKCANCEL or MB_ICONWARNING)=mrok then

begin

IF BSopen.Execute then

try

try

begin

tmpfile:=bsopen.FileName

tmptable:=tadotable.Create(self)

tmpquery2:=tadoquery.Create (self)

tmpquery2.Connection :=dm.connect

tmptable.Connection :=dm.connect

tmptable.TableName:= 'exam_answer '

with tmpquery2 do

begin

close

sql.Clear

sql.Add( 'delete * from exam_answer ')

execsql

end

tmptable.LoadFromFile(tmpfile)

tmptable.First

while not tmptable.Eof do

begin

with tmpquery2 do

begin

close

sql.Clear

sql.Add( 'insert into exam_answer(kh,xm,sj_id,sjts,tk_id,stbh,tx,daa,dab,dac,dad,dae,daf,tkda,df) values(:kh,:xm,:sj_id,:sjts,:tk_id,:stbh,:tx,:daa,:dab,:dac,:dad,:dae,:daf,:tkda,:df) ')

parameters.ParamByName( 'kh ').Value :=tmptable.fieldbyname( 'kh ').AsString

parameters.ParamByName( 'xm ').Value :=tmptable.fieldbyname( 'xm ').AsString

parameters.ParamByName( 'sj_id ').Value :=tmptable.fieldbyname( 'sj_id ').AsString

parameters.ParamByName( 'tx ').Value :=tmptable.fieldbyname( 'tx ').AsString

parameters.ParamByName( 'sjts ').Value :=tmptable.fieldbyname( 'sjts ').AsString

parameters.ParamByName( 'tk_id ').Value :=tmptable.fieldbyname( 'tk_id ').AsString

parameters.ParamByName( 'stbh ').Value :=tmptable.fieldbyname( 'stbh ').AsString

parameters.ParamByName( 'daa ').Value :=tmptable.fieldbyname( 'daa ').AsBoolean

parameters.ParamByName( 'dab ').Value :=tmptable.fieldbyname( 'dab ').asboolean

parameters.ParamByName( 'dac ').Value :=tmptable.fieldbyname( 'dac ').AsBoolean

parameters.ParamByName( 'dad ').Value :=tmptable.fieldbyname( 'dad ').AsBoolean

parameters.ParamByName( 'dae ').Value :=tmptable.fieldbyname( 'dae ').AsBoolean

parameters.ParamByName( 'daf ').Value :=tmptable.fieldbyname( 'daf ').AsBoolean

parameters.ParamByName( 'tkda ').Value :=tmptable.fieldbyname( 'tkda ').AsString

parameters.ParamByName( 'df ').Value :=tmptable.fieldbyname( 'df ').AsString

execsql

end

tmptable.next

end

showmessage( '导入完成，累死了... ')

end

finally

tmptable.Free

tmpquery2.Free

end

except

showmessage( '导入未完成。：( ')

end

end

end

如果单纯的LOAD出来，它是不直接写到后台数据表的。分析下源码就知道它LOAD之前先CLOSE了数据表。只有一条一条增加到后台数据表了。

---