Forum Discussion

Lyssillic's avatar
Lyssillic
Icon for Helper I rankHelper I
6 years ago
Solved

Display images from SQL Server database DirectQuery model

I have a SQL Server database with a table that holds pictures in Binary format that I would like to show in a table in a report. I can only use the DirectQuery model, and I have not found a way to ma...
Need Help
  • Lyssillic's avatar
    Lyssillic
    6 years ago

    I was able to solve it with a SQL Server query by casting the image to VARBINARY(MAX), and then casting it to XML, and changing the value to VARCHAR(MAX). After that, I concatenated it with 'data:image/jpeg;base64, ', and was able to change the Data Category to ImageUrl. 

     

    Here is the query:

     

    SELECT
    CONCAT('data:image/jpeg;base64, ', CAST('' as XML).value('xs:base64Binary(sql:column("BinaryPicture"))', 'VARCHAR(MAX)')) AS Picture
FROM (
    SELECT
        CAST(lmePicture AS VARBINARY(MAX)) AS BinaryPicture
    FROM
        Employees
) AS A

     

     

Lyssillic's avatar
Lyssillic
Icon for Helper I rankHelper I
6 years ago

I was able to solve it with a SQL Server query by casting the image to VARBINARY(MAX), and then casting it to XML, and changing the value to VARCHAR(MAX). After that, I concatenated it with 'data:image/jpeg;base64, ', and was able to change the Data Category to ImageUrl. 

 

Here is the query:

 

SELECT
    CONCAT('data:image/jpeg;base64, ', CAST('' as XML).value('xs:base64Binary(sql:column("BinaryPicture"))', 'VARCHAR(MAX)')) AS Picture
FROM (
    SELECT
        CAST(lmePicture AS VARBINARY(MAX)) AS BinaryPicture
    FROM
        Employees
) AS A