SQL Server/Dynamics CRM: How to read document body from Annotation Base using SQL only

Question

I am building an SSRS report in which I need to read parts of a MS Word Document body that contains some error logs. The document body is stored in the AnnotationBase in SQL Server. Per Scott Durow (MVP),

"The DocumentBody is a base64 encoded string, so you'll need to convert back to binary and save to a file."

https://community.dynamics.com/crm/f/117/t/185391

I am aware there is a converter and have also tried the following using SQL:

https://blogs.msdn.microsoft.com/sqltips/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa/

However the solution in the above link did not work.

I am aware this can easily done using C#. However, this is the only snag I have ran into building the report and would like to keep it all SQL.

Is this possible without me having to right a .NET component? If not, can this be done using FetchXML?

Answer 1

You can decode the Base64 documentbody column in SQL as shown here: Base64 encoding in SQL Server 2005 T-SQL

-- Encode the string "TestData" in Base64 to get "VGVzdERhdGE="
SELECT
    CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )   Base64Encoding
FROM (
    SELECT CAST('TestData' AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp;

-- Decode the Base64-encoded string "VGVzdERhdGE=" to get back "TestData"
SELECT 
    CAST(
        CAST(N'' AS XML).value(
            'xs:base64Binary("VGVzdERhdGE=")'
          , 'VARBINARY(MAX)'
        ) 
        AS VARCHAR(MAX)
    )   ASCIIEncoding
;

(Aside, you should avoid using the Base tables, it's not supported, use the Filtered tables instead FilteredAnnotation).

(Also aside, the Word document is structured as complex XML which may difficult to parse in SQL, so perhaps this isnt the best solution in any case.)