cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Lyssillic
Helper I
Helper I

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 make this happen, because I can't change the data type from Binary to Text since it is not supported in the DirectQuery model.

 

Is there a sufficient way to write a SQL Server query or different way to change the Binary to Text to display in the report?

1 ACCEPTED SOLUTION
Lyssillic
Helper I
Helper I

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

 

 

View solution in original post

2 REPLIES 2
Lyssillic
Helper I
Helper I

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

 

 

View solution in original post

v-shex-msft
Community Support
Community Support

Hi @Lyssillic,

I'd like to suggest you check the below blog to know more about how to analyze binary format image files.
Since you are in direct query mode, I'd like to suggest you write a t-sql query to add a custom column to convert binary to base64 text and add the prefix so that power bi can recognized these strings as image.

Storing Images in a PowerBI/Analysis Services Data Models 

Use the BINARY BASE64 Option 

Transact-SQL: Convert VARBINARY to Base64 String and vice versa 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.