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

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.”

  • Call a stored procedure in multi scalar table-valued function?
  • Implement Lucene on Existing .NET / SQL Server stack with multiple webservers
  • Recursive sum in tree structure
  • Read XML node attributes without using node name in SQL Server query
  • How to add minutes in DateTime value from SQL Server to C#
  • Return count using raw query, using Entity Framework and MVC
  • 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?

  • SQL Server to .Net type conversions
  • SQL Server Numeric with Identity not converting to Auto Number in MS Access using linked tables
  • Why is Entity Framework passing a parameter as DECIMAL(5,0) for a column defined with NUMERIC(19)?
  • mssql convert varchar to float
  • Conversion error while converting varchar to smallint
  • SQL Server 2016 timestamp data type
  • One Solution collect form web for “SQL Server/Dynamics CRM: How to read document body from Annotation Base using SQL only”

    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.)

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.