One thing I forgot to mention in yesterday's post: it is possible to have SQLXML do the conversion from Base64 to binary using an updategram. When using an updategram, it is possible to specify a mapping schema, which lets you use attribute names in the updategram that are different than the column names in the table. A mapping schema also lets you specify data type conversion, one of which is Base64 to binary. Here's a brief example:
Mapping Schema
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sqlms="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="binaryTest">
<xsd:complexType>
<xsd:attribute name="someParameter" sqlms:field="someValue" type="xsd:integer" />
<xsd:attribute name="attachmentData" sqlms:field="attachmentData" type="xsd:base64Binary" sqlms:datatype="image" />
</xsd:complexType>
</xsd:element>
</xsd:schema>
Updategram
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync mapping-schema="testMap.xml">
<updg:before />
<updg:after>
<binaryTest someParameter="1" attachmentData="dGhpcyBpcyBhIHRleHQgZmlsZQ==" />
</updg:after>
</updg:sync>
</ROOT>
If this updategram is executed as a template query in a virtual directory configured for SQLXML access, a record will be inserted in the “binaryTest” table with the Base64 encoded data converted to binary.
I wasn't able to use this solution for my problem because I couldn't figure out how to get the updategram generated by BizTalk to use a mapping schema.