sql server bcp xml data

I have a table which has a column which is of type xml.
I have to extract data from this table and load the data into another environment.
i am using bcp to extract and laod the target table but there are some special characters that is causing some issues when i bcp them into the target table. are there any workarounds

thanks
Ben

  • Difference between RDBMS and ORDBMS
  • List of unique fields
  • How to write an ADODB.RecordSet (made from an Excel Range) to a SQL Server temporary table
  • How to (left/right)join two tables?
  • SQL Server - find people between date range excluding the year
  • Execute a sql string in sql server
  • One Solution collect form web for “sql server bcp xml data”

    A custom CLR-SP provided me with the best solution. Now I can write XML-typed data directly to a file from TSQL, provided the SQL service account has permission to the file. This allows the simple syntax:

    exec dbo.clr_xml2file @xml, @path, @bool_overwrite
    

    The SP:

    CREATE PROCEDURE [dbo].[clr_xml2file]
        @xml [xml],
        @file [nvarchar](max),
        @overwrite [bit]
    WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [CLR_FileIO].[FreddyB.FileIO].[Xml2File]
    

    The C# for the CLR DLL:

    using System;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using System.IO;
    using System.Security.Principal;
    using System.Text;
    using System.Xml;
    using System.Xml.XPath;
    using Microsoft.SqlServer.Server;
    
    namespace FreddyB
    {  
      public class FileIO
      {
        public static void Xml2File(
          SqlXml xml, 
          SqlString sFile, 
          SqlBoolean bOverwrite
        ) {
    
          SqlPipe sqlpipe = SqlContext.Pipe;
          try
          {
            if (xml == null || xml.IsNull || xml.Value.Length == 0) {
              sqlpipe.Send("Cannot write empty content to file : \n\t"
                +sFile.Value);
              return;
            }
    
            if (File.Exists(sFile.Value) & bOverwrite.IsFalse) {
              sqlpipe.Send("File already exists : \n\t"+sFile.Value);
              return;
            }
    
            int iFileSize = 0;
            FileStream fs = null;
            try {
              byte[] ba = Encoding.UTF8.GetBytes(xml.Value);
              iFileSize = ba.Length;
    
              fs = new FileStream(sFile.Value, FileMode.Create, FileAccess.Write);
              fs.Write(ba, 0, ba.Length);
    
              sqlpipe.Send("Wrote "
                +String.Format("{0:0,0.0}",iFileSize/1024)
                +" KB to : \n\t"
                +sFile.Value);
            }
            catch (Exception ex) 
            {
              sqlpipe.Send("Error as '"
                +WindowsIdentity.GetCurrent().Name
                +"' during file write : \n\t"
                +ex.Message);
              sqlpipe.Send("Stack trace : \n"+ex.StackTrace);
            }
            finally
            {
              if (fs != null) {
                fs.Close();
              }
            }
          }
          catch (Exception ex)
          {
            sqlpipe.Send("Error writing to file : \n\t"
              +ex.Message);
          }
        }
      }
    }
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.