xml文件导入数据库：
导入 xml 文件 DECLARE @idoc int DECLARE @doc varchar ( 1000 ) -- sample XML document SET @doc = ' <root> <Customer cid= "C1" name="Janine" city="Issaquah"> <Order oid="O1" date="1/20/1996" amount="3.5" /> <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied </Order> </Customer> <Customer cid="C2" name="Ursula" city="Oelde" > <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue white red"> <Urgency>Important</Urgency> Happy Customer. </Order> <Order oid="O4" date="1/20/1996" amount="10000"/> </Customer></root> ' -- Create an internal representation of the XML document. EXEC sp_xml_preparedocument @idoc OUTPUT, @doc -- Execute a SELECT statement using OPENXML rowset provider. SELECT * FROM OPENXML ( @idoc , ' /root/Customer/Order ' , 1 ) WITH (oid char ( 5 ), amount float , comment ntext ' text() ' ) EXEC sp_xml_removedocument @idoc 导成xml文件 CREATE TABLE tb(id int primary key,val numeric(10,2),date datetime,name varchar(100))INSERT tb(id,val,date,name) SELECT 1,12,GETDATE(),'小梁'UNION ALL SELECT 12,29.334,'2010-09-12','兰儿';
CREATE TABLE tb1(id int primary key ,val numeric( 10 , 2 ),date datetime ) INSERT tb1(id,val,date) SELECT 1 , 12 , GETDATE () UNION ALL SELECT 12 , 29.334 , ' 2010-09-12 ' GO
DECLARE @dir varchar ( 100 ); SET @dir = ' E:\ '
DECLARE @cmd nvarchar ( 4000 ); SET @cmd = N ' DECLARE @s varchar(MAX); SET @s= '''' ; DECLARE @i int; SET @i = 1 DECLARE @c int; SET @c = (SELECT MAX(id) FROM ##tb); DECLARE @objid int,@objname sysname; WHILE @i <= @c BEGIN SELECT @objid=object_id,@objname=name FROM ##tb WHERE id=@i; SET @s = '' EXEC xp_cmdshell N '''' BCP "SELECT doc FROM ##tb WHERE ID= '' + RTRIM(@i)+ '' " queryout ' + @dir + ''' +RTRIM(@objid)+ '' _ '' +REPLACE(REPLACE(@objname, '' ] '' , '''' ), '' [ '' , '''' )+ '' .XML '' + '' -w -S.\SQLEXPRESS -T '''''' ;
EXEC(@s); SET @i = @i + 1; END DROP TABLE ##tb
' ;
EXEC sp_MsForeachTable @precommand = ' CREATE TABLE ##tb(id int identity,object_id int,name sysname,doc xml); ' , @command1 = N ' INSERT ##tb(object_id,name,doc) SELECT OBJECT_ID( '' ? '' ), '' ? '' , CAST( '' <DATAPACKET Version="2.0"><METADATA TABLENAME="?"> '' + (SELECT A.name AS [@attrname],B.name AS [@fieldtype], CASE WHEN EXISTS(SELECT * FROM sys.indexes AS C JOIN sys.index_columns AS D ON C.object_id=D.object_id AND C.index_id=D.index_id WHERE C.object_id=A.object_id AND D.column_id=A.column_id AND C.is_primary_key=1) THEN '' true '' END AS [@IS_PRIMARY_KEY], A.max_length AS [@WIDTH] FROM sys.columns AS A JOIN sys.types AS B ON A.user_type_id = B.user_type_id AND object_id=OBJECT_ID( '' ? '' ) FOR XML PATH( '' FIELD '' ),ROOT( '' FIELDS '' ))+ '' </METADATA> '' + ( SELECT * FROM ? FOR XML RAW( '' ROW '' ),ROOT( '' ROWDATA '' ) ) + '' </DATAPACKET> '' AS xml); ' , @postcommand = @cmd ;
GO DROP TABLE tb,tb1;
转载于:https://www.cnblogs.com/xupei/archive/2010/12/30/1922273.html
相关资源：Java实现XML导入不同数据库，从数据库导出数据到XML