I have worked with this area a lot, and have the scars to prove it... (all mental)!
Anyway, the sql adapter metadata generator automatically generates the insert schema that it uses to call stored procs.
You will notice that all parameters are generated as attributes with simple data types, and if you are using the xml data type the attribute gets created as a string. The attribute can not contain xml. This means to insert xml using the sql adapter you must to convert your xml data to string which means you will need to escape out the reserved characters such as < > " '.
This is a non trivial task.
There are various horrible ways of doing this,
you can write some terribly complicated xslt as can be seen here.
create your insert message using a .net object based on the generated schema using xsd.exe, then assign your xml string to the property in th e.net object and let the serializer do the escaping for you (this is a very memory hungry method and not suitable for large xml files), see my article here
You should note that if the document you are trying to insert is huge (35 mb +) you may run into memory issues with teh sql adapter. I have encountered this and it was a trauma to work around.
The following blog helped me a great deal, read through to part 3 where there is a link to a custom adapter that removes this problem to a large degree.
http://grounding.co.za/blogs/romiko/archive/2008/03/26/biztalk-2006-default-sql-adapter-updategrams-part-1.aspx
