ＳＱＬ与ＸＭＬ交互

１、ＳＱＬＳＥＲＶＥＲ导入ＸＭＬ

 String sConnection = "Data Source=127.0.0.1;Initial Catalog=testdb;User ID=sa;PassWord=newman2007";
            SqlConnection mySqlConnection = new SqlConnection(sConnection);
            SqlCommand mySqlCommand = new SqlCommand("select   *   from   ecsjsjhtxx   FOR   XML   AUTO   ", mySqlConnection);
            mySqlCommand.CommandTimeout = 15;
            mySqlConnection.Open(); 
          
            SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(" select   *   from   ecsjsjhtxx ", sConnection);
            DataSet myDataSet2 = new DataSet();
            mySqlDataAdapter.Fill(myDataSet2);

            //myDataSet1.WriteXml("ecSjsjhtxxNew1.xml");
            myDataSet2.WriteXml("d://ecSjsjhtxxNewfdsafsda444444444442.xml",XmlWriteMode.WriteSchema    );

２、ＸＭＬ导入ＳＱＬＳＥＲＶＥＲ

 

--Up_HyConvertDataToDbFromXML 'd:/ecsjsjhtxx.xml'
alter PROCEDURE Up_HyConvertDataToDbFromXML
 @v_FileName varchar(20)--XML文件名及路径
as
BEGIN   
        set nocount on
     if not exists (select   *   from   sysobjects   where   id   =   object_id('DrTbFrmXml'))  
  begin  
    create  table DrTbFrmXml(doc  varchar(8000))  
  end  
  --1、读取XML
  exec ('bulk   insert   DrTbFrmXml   from   '''+@v_FileName+''''   ) 

  delete from DrTbFrmXml where (doc is null ) or (left(ltrim(doc),5)='<?xml')
  
  if  exists (select   *   from   sysobjects   where   id   =   object_id('XmlTemp'))  
  begin  
     drop table XmlTemp
  end
  
  --2、产生流水号
  select idx=Identity(int,1,1),* into XmlTemp from DrTbFrmXml
  --3、遍历读取数据，并写入正式表
  
  declare @v_idx  int
  declare  @v_Doc   Varchar(8000)  
  declare  @v_DocA   Varchar(8000)  
  declare @idoc int
  declare curList cursor
  for select idx from temp where idx<10000
  open curList
  fetch next from curList into @v_idx
  while @@fetch_status=0
  begin
     set @v_Doc='<?xml   version="1.0"   encoding="GB2312"?>   '
     select @v_Doc=@v_Doc+doc from temp where Idx=@v_idx 
     EXEC   sp_xml_preparedocument  @idoc OUTPUT , @v_Doc ;
             INSERT INTO ecsjsjhtxx   SELECT * FROM  OPENXML (@idoc, N'/ecsjsjhtxx')  WITH ecsjsjhtxx                            
     EXEC sp_xml_removedocument @idoc
     fetch next from curList into @v_idx 
  end
  close curList
  deallocate curList
        set nocount off
END