收藏点以备后用数据库将数据导入导出xml文件

时间：2010-12-30 来源：xupei

导入 xml　文件
DECLARE @idoc intDECLARE @doc varchar(1000)--sample XML documentSET @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;