I agree; one table, but that isn't what I am suggesting.

Suppose your xml for 5000 offices is:

<xml><office ... >...</office><!-- lots and lots in here --><office
...>...</office></xml>

What I am saying is that rather than dealing with this as a single
bulk, you bulk copy into a table where each row is a different
office - rather than a single CLOB of all the offices in one row. The
advantage of this is that each record describes a single entity, and
is easy to manage.

If you are the data-source, you can do whatever you like. I've checked
my code, and my current IDataReader implementation contains too much
project-specific junk to be easily postable - so I've tried to
illustrate from your xml taking your requirement to edit the xml on
the fly into account (although you'd normally start the XmlReader from
a Stream; the StringReader is just for quick demonstration); the only
bit you'd really need to change is the XmlDataReader.DoRead() method;
the point being that you can hand this IDataReader to SqlBulkCopy, and
it will use it as the data source. Note that at any point in time we
only have the data for a single office in memory.

The SqlBulkCopy class can be used to copy this into a staging table
(the ctor sets it up for a single column, but that can be changed);
once at the database, you can use all the SQL2005 methods to move that
into a relational model, or just leave as xml. Note that you can do
all sorts of fun things like persisted calculated columns on xml data,
so it isn't always (depending on scenario) necessary to make it
relational at all.

The other approach you can take is to use the same SqlBulkCopy model,
but to forget about xml at the database, and simple use XmlReader /
XmlDocument to parse each office into columns (using SetValues()
inside DoRead() to update the current row each time). This only works
if the schema stays simple; if you start getting multi-valued, it gets
very tricky to do this efficiently. My assumption (when working with
xml) is always worst-case (i.e. assume the data might get more
complex), which is why I have suggested working with xml first, and
suggested parsing to columns second. But either would work.

Marc

(long code post)

using System;
using System.Data;
using System.Xml;
using System.IO;
using System.Diagnostics;

static class Program {
   const string xml = @"<offices>
<office>
 <OfficeName>officename1</OfficeName>
 <Address>
  <Street>street</Street>
  <Town>town</Town>
  <PostCode>postcode</PostCode>
 </Address>
 <field1>xxxx</field1>
 <field10>xxxx</field10>
</office><office>
 <OfficeName>officename2</OfficeName>
 <Address>
  <Street>street</Street>
  <Town>town</Town>
  <PostCode>postcode</PostCode>
 </Address>
 <field1>xxxx</field1>
 <field10>xxxx</field10>
</office><office>
 <OfficeName>officename3</OfficeName>
 <Address>
  <Street>street</Street>
  <Town>town</Town>
  <PostCode>postcode</PostCode>
 </Address>
 <field1>xxxx</field1>
 <field10>xxxx</field10>
</office><!-- comment test--><office>
 <OfficeName>officename4</OfficeName>
 <Address>
  <Street>street</Street>
  <Town>town</Town>
  <PostCode>postcode</PostCode>
 </Address>
 <field1>xxxx</field1>
 <field10>xxxx</field10>
</office></offices>";
   static void Main() {
       using (IDataReader reader = GetReader()) {
           while (reader.Read()) {
               Trace.WriteLine(reader.GetValue(0));
           }
       }
   }
   static IDataReader GetReader() {
       return new XmlDataReader(XmlReader.Create(new
StringReader(xml)));
   }
}

class XmlDataReader : SimpleDataReader {
   private XmlReader reader;
   readonly int targetDepth;
   public XmlDataReader(XmlReader reader) : base(
       new string[] {"Xml"},
       new Type[] {typeof(string)}) {

       if (reader == null) throw new ArgumentNullException("reader");
       this.reader = reader;
       reader.MoveToContent();
       targetDepth = reader.Depth + 1;

   }
   protected override void DoClose() {
       if(reader!=null) reader.Close();
       reader = null;
   }
   protected override bool DoRead() {
       while (reader.Read() && reader.Depth == targetDepth) {
           if (reader.NodeType == XmlNodeType.Element) {
               string xml;
               using (XmlReader subReader = reader.ReadSubtree()) {
                   XmlDocument doc = new XmlDocument();
                   doc.Load(subReader);
                   doc.DocumentElement.SelectSingleNode("field1").InnerText
+= " *";
                   xml = doc.OuterXml;
               }
               SetValues(xml);
               return true;
           }
       }
       return false;
   }

}

   public abstract class SimpleDataReader : IDataReader {
       readonly string[] columnNames;
       readonly Type[] columnTypes;
       readonly object[] values;

       protected SimpleDataReader(string[] names, Type[] types) {
           if (names == null) throw new
ArgumentNullException("names");
           if (types == null) throw new
ArgumentNullException("types");
           if (names.Length != types.Length) throw new
ArgumentException("Names / types lengths must match");

           columnNames = (string[])names.Clone();
           columnTypes = (Type[])types.Clone();
           values = new object[names.Length];
       }
       protected void SetValues(params object[] values) {
           if (values == null) throw new
ArgumentNullException("values");
           if (values.Length != this.values.Length) throw new
ArgumentException("Values length is invalid");
           values.CopyTo(this.values, 0);
       }
       private bool isClosed = false;
       void IDisposable.Dispose() { Close(); }
       abstract protected void DoClose();
       public void Close() {
           try {
               DoClose();
           } finally {
               isClosed = true;
           }
       }
       public bool IsClosed { get { return isClosed; } }
       public int RecordsAffected { get { return -1; } }

       #region IDataReader Members

       public int Depth {
           get { return 0; }
       }

       public DataTable GetSchemaTable() {
           return null;
       }

       public bool NextResult() {
           Close();
           return false;
       }
       public bool Read() {
           return DoRead();
       }
       protected abstract bool DoRead();

       #endregion

       #region IDataRecord Members

       public int FieldCount {
           get { return columnNames.Length; }
       }

       public bool GetBoolean(int i) {
           return Convert.ToBoolean(GetValue(i));
       }

       public byte GetByte(int i) {
           return Convert.ToByte(GetValue(i));
       }

       public long GetBytes(int i, long fieldOffset, byte[] buffer,
int bufferoffset, int length) {
           byte[] data = (byte[])GetValue(i);
           int max = data.Length + 1 - (int)fieldOffset;
           if (length > max) length = max;
           if (length > 0) {
               Buffer.BlockCopy(data, (int)fieldOffset, buffer,
bufferoffset, length);
               return length;
           }
           return 0;
       }

       public char GetChar(int i) {
           return Convert.ToChar(GetValue(i));
       }

       public long GetChars(int i, long fieldoffset, char[] buffer,
int bufferoffset, int length) {
           string data = GetString(i);
           int max = data.Length + 1 - (int)fieldoffset;
           if (length > max) length = max;
           if (length > 0) {
               data.CopyTo((int)fieldoffset, buffer, bufferoffset,
length);
               return length;
           }
           return 0;
       }

       public IDataReader GetData(int i) {
           throw new NotSupportedException();
       }

       public string GetDataTypeName(int i) {
           return GetFieldType(i).Name;
       }

       public DateTime GetDateTime(int i) {
           return Convert.ToDateTime(GetValue(i));
       }

       public decimal GetDecimal(int i) {
           return Convert.ToDecimal(GetValue(i));
       }

       public double GetDouble(int i) {
           return Convert.ToDouble(GetValue(i));
       }

       public Type GetFieldType(int i) {
           return columnTypes[i];
       }

       public float GetFloat(int i) {
           return Convert.ToSingle(GetValue(i));
       }

       public Guid GetGuid(int i) {
           object obj = GetValue(i);
           byte[] binary = obj as byte[];
           if (binary != null) {
               return new Guid(binary);
           } else {
               return new Guid(Convert.ToString(obj));
           }
       }

       public short GetInt16(int i) {
           return Convert.ToInt16(GetValue(i));
       }

       public int GetInt32(int i) {
           return Convert.ToInt32(GetValue(i));
       }

       public long GetInt64(int i) {
           return Convert.ToInt64(GetValue(i));
       }

       public string GetName(int i) {
           return columnNames[i];
       }

       public int GetOrdinal(string name) {
           for (int i = 0; i < columnNames.Length; i++) {
               if
(StringComparer.InvariantCultureIgnoreCase.Equals(name,
columnNames[i]))
                   return i;
           }
           return -1;
       }

       public string GetString(int i) {
           return Convert.ToString(GetValue(i));
       }

       public object GetValue(int i) {
           return values[i];
       }

       public int GetValues(object[] values) {
           if (values == null) throw new
ArgumentNullException("values");
           int fields = values.Length < FieldCount ? values.Length :
FieldCount;
           Array.Copy(this.values, values, fields);
           return fields;
       }

       public bool IsDBNull(int i) {
           object obj = GetValue(i);
           return obj == null || obj is DBNull;
       }

       public object this[string name] {
           get { return this[GetOrdinal(name)]; }
       }

       public object this[int i] {
           get { return GetValue(i); }
       }

       #endregion
   }