Images from DB in Base64

Transact-SQL
1

HI

We have a Quiey for pulling Thumnail images for reports which works fine in SSRS but to use images in Powerbi the code needs to be in Base64 ( i beleive ) There are ways to convert the columns in power bi but this invilves swithing from Direct Query which i dont want to do if i can help it .Ive scoured the internet and it seems possible to swith to the output as XML but I cannot get it working

Any help appreciated

SELECT        Products.ProductId, 
  
  
  (SELECT  TOP 1  (DocumentData )  as title
                               FROM            Documents
                               WHERE        (TableName LIKE 'Products') AND (DocumentTitle LIKE 'Thumb') AND (DocumentArchived = 0) AND (Identifier = Products.Product)
                               GROUP BY DocumentData) AS Thumbnail
                         
FROM            Products
2

Please provide sample data?

3

HI

Ive tried to create some thing but , i'm afraid i'm struggleing to supple sample data

4

Hi

Does this help???
,:grinning::grinning:

5

I have this working but the results need data:image/jpeg;base64 in front but I canot work out where to place it as All I get is 

SELECT     top 10 
  Products.ProductId,


  (SELECT    TOP 1    (DocumentData) 
                               FROM            Documents
                               WHERE        (TableName LIKE 'Products') AND (DocumentTitle LIKE 'Thumb') AND (DocumentArchived = 0) AND (Identifier = Products.Product)
                               GROUP BY DocumentData FOR XML PATH(''), BINARY BASE64) AS Thumbnail 
                         
FROM            Products INNER JOIN
                         CommissionGroups ON Products.CommissionGroup = CommissionGroups.CommissionGroup
ProductId Thumbnail
GC0220 /9j/4QBcRXhpZgAASUkqAAgAAAABAJiCAgA3AAAAGgAAAAAAAAAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAAAAAA/+wAEUR1Y2t5AAEABAAAAFAAAP/hBEhodHRwOi8vbnMuYWRvYmUuY29tL3hhcC8xLjAvADw/eHBhY2tldCBiZWdpbj
6

How about trying

'data:image/jpeg;base64/'+(DocumentData)

1 Like
7 
--providing sample data
use sqlteam
go

create table Products(ProductId varchar(50), Product varchar(50))
insert into Products
select 'GC0220', 'Flask Whiskey'

create table Documents(TableName varchar(50), DocumentTitle varchar(50), DocumentArchived  bit, Product varchar(50), DocumentData image)
insert into Documents
select 'Products', 'Product first take', 0, 'Flask Whiskey', bulkcolumn
from OPENROWSET(BULK N'C:\_Personal\sqlteam\images\flask.png', SINGLE_CLOB) AS Document
union
select 'Products', 'Product second take', 0, 'Flask Whiskey', bulkcolumn
from OPENROWSET(BULK N'C:\_Personal\sqlteam\images\flask.png', SINGLE_CLOB) AS Document


select * from Documents
select * From Products

--drop table Documents
--drop table Products

then in powerbi use the below steps to add a custom column, convert it to text then in modeling you change it its Data Category to Image URL

8

Hi , the power bi instructions work on the basis you are running the query as an import , I have over 10000 products with images so would need to import all of them for the above to work ( unless i'm missing something ) this is why i'm trying to find a way to resolve the issue at source allowing me to run it in direct query as you would in SSRS and only call what you need .

9

I'm still stuck with heading the Binary which i think is stopping this from working
image

10

ah gotcha, so you do want to use DirectQuery?

11

I really want to avoid it if I can find a way to convert at source

12

this worked for me. are your images png of jpeg, you will need to change accordingly to image type the setting data:image/png

SELECT     top 10 
  Products.ProductId,


(SELECT    TOP 1    'data:image/png;base64,'+(select DocumentData as '*' for xml path(''))
   FROM Documents
  WHERE (TableName LIKE 'Products') 
    AND (DocumentTitle LIKE 'Thumb') 
	AND (DocumentArchived = 0) 
	AND (Identifier = Products.Product)
  --GROUP BY DocumentData 
  FOR XML PATH('')
) AS Thumbnail 
                         
FROM            Products

image

13

HI , just had five minutes to look at this ,its returning NULL's in PowerBi and SQL , ive changed it to Jpeg but it appears to not be outputting

image

14

Were the original images jpeg? Could you please show us the query that is the spirce of the data?

15

Hi

The standard query we use to obtain images for SSRS is as below . The images are stored in Vibinary on the server not linked and are JPEG format . Hope that make sense

SELECT top 10
Products.ProductId,

(SELECT TOP 1 ( DocumentData)
FROM Documents
WHERE (TableName LIKE 'Products')
AND (DocumentTitle LIKE 'Thumb')
AND (DocumentArchived = 0)
AND (Identifier = Products.Product)
GROUP BY DocumentData

) AS Thumbnail

FROM Products

16

Could you please show us the actual result of ^^ that query and post it here in dml and ddl

create table #sample

insert into #sample

so that we can try it on our server and our powerbi?

17

Hi , sorry not been back for a while , the below solution appears to be working so thought I would share

SELECT
    '<img src="data:image/Jpeg;base64,' + CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    ) + '" />' Base64Encoding, LEN(bin) as filesize, ProductId
FROM (
    SELECT TOP 10 Documents.Identifier, CAST(Documents.DocumentData AS VARBINARY(MAX)) AS bin, Products.ProductId AS ProductId FROM Documents
              INNER JOIN Products ON Documents.Identifier = Products.Product
                               
              WHERE (Documents.TableName LIKE 'Products') AND (Documents.DocumentTitle LIKE 'Thumb') AND (Documents.DocumentArchived = 0)
) AS bin_sql_server_temp;