Passing XML To An XML Datatype Stored Procedure Parameter Using WCF-SQL Adapter

Sometimes a collection or complex data structure needs to be delivered to a SQL database for storage.  Strongly typed stored procedure schemas do not make it apparent what is required to pass XML data type parameter values (they are presented as xs:string types).

What you need to do is pass the XML parameter content as a CDATA section.  The CDATA section is stripped by the adapter/binding and inserted as required.

Your BizTalk map needs to compose the request document using some XSL-T like this (where ContentParam is the parameter name and //SrcContent is a parent element containing a well-formed XML structure):

<ContentParam xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
    <xsl:variable name="CDATABegin" select="string('&lt;![CDATA[')" />
    <xsl:variable name="CDATAEnd" select="string(']]&gt;')" />
    <xsl:value-of select="$CDATABegin" disable-output-escaping="yes"/>
    <xsl:copy-of select="//SrcContent/@*" />
    <xsl:copy-of select="//SrcContent/*" />
    <xsl:value-of select="$CDATAEnd" disable-output-escaping="yes"/>
</ContentParam>