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
I am aware there is a converter and have also tried the following using SQL:
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?
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
(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.)