Geeks With Blogs
Sean Carpenter Thoughts on Development

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.

Posted on Thursday, October 20, 2005 7:04 AM SQL Server | Back to top


Comments on this post: Inserting Base64 Data, Part 2

Comments are closed.
Comments have been closed on this topic.
Copyright © Sean Carpenter | Powered by: GeeksWithBlogs.net