Lauren Buxton Lauren Buxton - 1 month ago 9
SQL Question

Can you generate xml feeds in ssms with images/pictures

I am designing my database to hold records of user profiles.

These user profiles have the standard: title, name, biography and each have a picture which I need save in the table also

Title
Name
Biography
Image

I need to generate an XML output for the web developer to use in the website, is it possible to send this image in the XML or will he need these picture in a location he can access?

Hope this makes sense.

Thanks, Lauren

Answer

Don't know if it is this what you need, but SQL-Server's FOR XML PATH() will encode a binary value as Base64 implicitly:

SELECT 'The Title' AS [@Title]
      ,'The Name' AS [@Name]
      ,0x1234567890ABCDEF AS [Base64EncodedBinary]
FOR XML PATH('Test');

The result

<Test Title="The Title" Name="The Name">
  <Base64EncodedBinary>EjRWeJCrze8=</Base64EncodedBinary>
</Test>

This Base64-string can be used as <img>-source directly...

UPDATE

This would need SQL-Server 2008+. Earlier version 2005 encoded binaries to a HEX-string implicitly...