Add Data like that

Create Table xmldata(xml_elements varchar(1000))
INSERT INTO XMLDATA 
SELECT CHAR(239)+CHAR(187)+CHAR(191)+ '<?xml version="1.0" encoding="utf-8"?>' + '<Companies>
  <Company Name="ABC" Location="Amsterdam" Owner="XYZ"  Type="Departmental Stores" Estbshd="29-10-1992"  NumberofEmployees="1000+">
    <Branch Office= "Abc1" type="Mid"  status= "A1" Registrarion="ABC38" Location="Paris">
      <SubBranch name ="BEU" type="Local" Staff="35"  customer="ARDGJE.">
      </SubBranch>
    </Branch>
    <Branch Office= "Abc3" type="Mid"  status= "B2" Registrarion="C38" Location="London">
      <SubBranch name ="AMT" type="Local" Staff="22"  customer="JET">
      </SubBranch>
    </Branch>
  </Company>
</Companies>'

and verify it by this link

http://social.msdn.microsoft.com/Forums/en/csharpgeneral/thread/8094b5bb-7ce0-488e-8b14-de8fcfbeeece

Hi Mitesh,

Thanks so much for your response and your time.

But unfortunately, it didnt work, the file came up with three boxes at the front of the file but on notepad++ still had Encoding as UTF without BOM. ���

Thanks again. Regards, DCS

Hi Mitesh,

Inserting CHAR(239)+CHAR(187)+CHAR(191) into the XMLData didnt work but then I created a VB.Net application using the link you provided. That worked.

Thanks a lot .

Cheers, DCS.

for temporary solution export all file using bcp in single folder without adding CHAR(239)+CHAR(187)+CHAR(191)

for example

Create Table xmldata(xml_elements varchar(1000))
INSERT INTO XMLDATA 
SELECT '<?xml version="1.0" encoding="utf-8"?>' + '<Companies>
  <Company Name="ABC" Location="Amsterdam" Owner="XYZ"  Type="Departmental Stores" Estbshd="29-10-1992"  NumberofEmployees="1000+">
    <Branch Office= "Abc1" type="Mid"  status= "A1" Registrarion="ABC38" Location="Paris">
      <SubBranch name ="BEU" type="Local" Staff="35"  customer="ARDGJE.">
      </SubBranch>
    </Branch>
    <Branch Office= "Abc3" type="Mid"  status= "B2" Registrarion="C38" Location="London">
      <SubBranch name ="AMT" type="Local" Staff="22"  customer="JET">
      </SubBranch>
    </Branch>
  </Company>
</Companies>'

after exporting all file in one folder let sau folder name tempbcp use below logic to append CHAR(239)+CHAR(187)+CHAR(191)

you have to loop all files in folder.

   private void btnAppendBOM_Click(object sender, EventArgs e)
    {
        // read the text into a byte array  
        string fname = "";
        bool bomPresent;
        string[] filePaths = Directory.GetFiles(@"c:\tempbcp\","*.txt");
        MessageBox.Show("Total file : " + filePaths.GetLength(0).ToString());
        foreach (string filepath in filePaths)
        {
            bomPresent = false;
            using (FileStream fs = new FileStream(filepath, FileMode.Open))
            {

                byte[] buffer = new byte[fs.Length];
                fs.Read(buffer, 0, buffer.Length);

                // check if BOM already present  
                if (buffer.Length >= 3)
                {
                    if (buffer[0] == (byte)0xEF
                        && buffer[1] == (byte)0xBB
                        && buffer[2] == (byte)0xBF)
                    {
                        bomPresent = true;
                    }
                }
                byte[] outBuffer;
                if (!bomPresent)
                {
                    // BOM not present - create the new byte array  
                    outBuffer = new byte[buffer.Length + 3];

                    // add the BOM  
                    outBuffer[0] = (byte)0xEF;
                    outBuffer[1] = (byte)0xBB;
                    outBuffer[2] = (byte)0xBF;

                    // copy the buffer  
                    Array.Copy(buffer, 0, outBuffer, 3, buffer.Length);
                }
                else
                    outBuffer = new byte[buffer.Length];

                // save the file again  
                fname = Path.GetFileName(filepath);
                using (FileStream outStream = new FileStream(@"C:\TempExport\" + fname, FileMode.OpenOrCreate))
                {
                    outStream.Write(outBuffer, 0, outBuffer.Length);
                    outStream.Close();
                }
            }
        }
        MessageBox.Show("BOM added successcully");
    }

to verify BOM in TempExport folder write below code

  private void btnVerifyBOM_Click(object sender, EventArgs e)
    {
        string fname = "";    
        string[] filePaths = Directory.GetFiles(@"c:\TempExport\","*.txt");
        MessageBox.Show("Total file : " + filePaths.GetLength(0).ToString());

        foreach (string filepath in filePaths)
        {
            fname = Path.GetFileName(filepath);
            using (FileStream fs = new FileStream(filepath, FileMode.Open))
            {
                byte[] buffer = new byte[fs.Length];
                fs.Read(buffer, 0, buffer.Length);

                // check if BOM already present  
                if (buffer.Length >= 3)
                {
                    if (buffer[0] == (byte)0xEF
                        && buffer[1] == (byte)0xBB
                        && buffer[2] == (byte)0xBF)
                    {
                        MessageBox.Show("BOM present in file : " + fname);
                    }
                    else
                    {
                        MessageBox.Show("Not present BOM in file : " + fname);
                    }
                }
                else
                {
                    MessageBox.Show("Not present BOM in file : " + fname);
                    continue;
                }
            }
        }
    }

Hi Mitesh,

Many Thanks for all your help..I urgently needed this coding and you had guided me in the right direction.

Thanks again for your valuable time. Regards, dcs.