sql server bcp xml data


Hi 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

By : user55474


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]
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;
        if (xml == null || xml.IsNull || xml.Value.Length == 0) {
          sqlpipe.Send("Cannot write empty content to file : \n\t"

        if (File.Exists(sFile.Value) & bOverwrite.IsFalse) {
          sqlpipe.Send("File already exists : \n\t"+sFile.Value);

        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 "
			+" KB to : \n\t"
        catch (Exception ex) 
          sqlpipe.Send("Error as '"
			+"' during file write : \n\t"
          sqlpipe.Send("Stack trace : \n"+ex.StackTrace);
          if (fs != null) {
      catch (Exception ex)
        sqlpipe.Send("Error writing to file : \n\t"
By : FreddyB

When you've specified the NameValueSectionHandler as the type attribute for a section and call to Configuration.GetSection(string), you will receive a DefaultSection instance as the return type.

string SectionInformation.SectionInformation.GetRawXml() is the key in this case to get into your data.

I answered another similar question with a valid way to do it using System.Configuration that you can reference to get all the details and a code snippet. NameValueSectionHandler can i use this section type for writing back to the app

By : JJS

better to just use .addClass even if you have 1 or more. More maintainable and readable.

If you really have the urge to do multiple css props then use

NB Any css props with a hyphen need to be quoted.

   'font-size' : '10px',
   'width' : '30px',
   'height' : '10px'

I've placed the quotes so no one will need to clarify that, and the code will be 100% functional.

By : redsquare

This video can help you solving your question :)
By: admin