2

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

  • You can send the image as a [Base64](https://en.wikipedia.org/wiki/Base64) string. AS long as they are small in size. – VDWWD Nov 02 '16 at 12:34

1 Answers1

2

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...

UPDATE 2

Copy this into a file "Surprise.html" and double click it:

<html><body>
<img src="data:image/jpeg;base64,/9j/4AAQSkZJRgABAQAAAQABAAD/2wBDABgREhUSDxgVFBUbGhgdJDwnJCEhJEo1OCw8WE1cW1ZNVVNhbYt2YWeDaFNVeaV6g4+UnJ2cXnSrt6mXtYuZnJX/2wBDARobGyQgJEcnJ0eVZFVklZWVlZWVlZWVlZWVlZWVlZWVlZWVlZWVlZWVlZWVlZWVlZWVlZWVlZWVlZWVlZWVlZX/wAARCADIAa8DASIAAhEBAxEB/8QAGgAAAwEBAQEAAAAAAAAAAAAAAAECAwQFBv/EADAQAAICAQMEAQMEAgICAwAAAAABAhEDEiExBEFRYSITcZEFMoGxFKEjQlLRFcHw/8QAGAEBAQEBAQAAAAAAAAAAAAAAAAECAwT/xAAgEQEBAQEBAAIDAQEBAAAAAAAAARECIQMxEkFRIhNh/9oADAMBAAIRAxEAPwDo1WDd/YlyQOSOLop77jUnEm02DkgrXVe4vZnY9YRVjTIcr5QtVtEVpfsHKjPVbCwK1C1WyHIlsLF6vBP1Ke5DlvsyXKkRUdVO8Zwwl4OrL8onJF06M9OnDpxukWpV2M8bS3Kb8eDlXRV9xpr/AEZXsNOgrdS5Li+/LMU/BWrShExumXFvscX12nsrRvDMu6ZqM2OyEzaMjjjkV+jeM01ydZXLrl0xnsap7HLF1ZtGTX5NyudjQV/2JyXkhz8blZXP9pmmS5tJpijLtY1caopPgyi9i09tioq+ATEn2BMCuAEuwJ7AMV7BYdgCw9CsL/oB9qAV/wBCbAdhYmwbKgbB7haBsAb9he7FfkHvuwC2F7g6E/ZAPfkmQNkt+AOBybYXfJm5Ox3aObo1sTIsdgXYWRe+4XuFVqE5bolslvuyEaax6u5jq3Gpur8Bcat2Q5ePwLVtyTe4A35/0Q34G5fklhWcp7UcbyLXR0zVHBP45GJNa5dcZ8GsZI48b3/g6I7GOo6ujTYaWuw8XBsomDWS2QmnJ1uaSVWSnX3GDXHiSj7NVCK+xzT6hY40+TJ9at9m0udjcjFdjgt6ewRbgzix/qWFt8/g6Y5Y5I3HdFsxJ67sc7R0Q3Rw4Hb27noY47G+XPvxMrHH4rjazDrOoXTwlLweXD9Q6vLOoNU3waYx7PUO4pryZRfYnVN4YuSp9wiyGNkykzNOikzURadIaZNhZUXY9iL2GmA7HZNhewDsLsm9gvYCr8g2TdhYDbEJsTYF35B0Rfkd2ghvfkH7Fd2PkoH7JbKdEN36AV7ib3Bv2S3ZB5jkGrczu7C75MOrawb9mWod+yGNL7g2+TOwcgKcrYm+CLE5bhV2F+zPV6C9yK1v27FfBnr3sWv8gaaqolshy38EuQFP5PYiXSwe85U/AJ72TJ7bk10+Ob6l9NLG7TuP3KVpjxynDZ8F800ZtrpjTE2kbxZzwlRrFmUauNrkycWmzVMpfwGXJl6d5DHq8c4dNpxx55o9SMf/AMxvEmjfNZvr5zFgzS+MYun3PU6Tp5YVKLld8nZ9FpfEqOHSuNzXXWpJh4VTPTxVoPJeT6cj0Ony3Bbjis/JP25v1XoZdTiqDpnP+lfpsunyOWRtv+j2NntyNRR0c9YdTCsaryc0eDuzR1Y5I4E+xKRcXuaR47GK/wDstS2BWiY79md2PV2KjS9gvhGeoq7Aq+B3ZFhYRdhySmFgO96C7ZNi1AW2JslsTYFWPkz1Dsosd2Q2GoItshvcHIzcr5GhuW+67kykJy9kN77geXq53Hfky1BqMOjWT2En+SNQ09wrTWJyt/yQ2LURWjl/YrVkXtYWBTluJP2TdisguxJ7kib3Iqm2S5fkm/Ym9ihqTTW4cZEzJyRUZJ9xjp8fWeKzy4SbH07dtN7DlFSXKsawtU09ybMx1rVOi4yow1U+UWnuYR0KdfejSMq+5zKXOxalXL+4wx245p1ZvGqPPhkSfP3OnHkNRz6jqpGeWahFtf7DXyc3US1XGy2syesdWuTbZWHqVF1jlZnHFSab5N8PRQjLUlRI6XP268PWpyUH+5nbHImqOTF0uNvUlvfIp5NGdROk2fbhZOr47nuqPPkqm17OqOTY45Sucn7LbrEmC6ZSfYhAiK0TXkaaoi+AukVFJjTITBPggux6kZ37BP7Aa3sOzLV7GpbFRdg2TqVA3sAOSFfsT4E+AKvm2F87mTkDYGrYtRm2F7FGjbfclshy9k6mEW2TKV7ktkuVgeZKuwm65ITHq33I2rV5Gn5M3TewdyDVy8k6kxKV8ifNkVbfArQrJsC79gn5IvuGoCxN7rcjUF2wHZPIXuS3sMUpMhS325G3uQzSOjHli1UnTOlSTWzOFYMso6lB15IccsU92ZvOus+TJ66cskp7M2xu4nnwclLeztxbLYlmNTrWjk0mCm2KRNq9jONxqpUzfFk3ORM1xtphbHe8unG3ZxPNcm77i6ib06V/JxTzKHDsslrHk+3pwyrm9vZ1Y+qxpVqvyfPS6nJ2Zks+RSbvnk1OKx11y+rw/qOFz+mpIwzZNXVbPY8LpW/rKTvk9rpY/UyXzbHX8JJP9PQxwk8bceTL6OSP/VndjWiCRZuR571rzONmgTOrqsWpa49uTjJYSndKgTJtCu+Aq7HZnfsSfsitLDURfsVhGlhq2M0wuwNNWw3OzFy/AtbBjfV6By7Mx1sNbBi2wtVuTfIWBTkTq8ie/ohtIqKk7Jb/AAJy9kye4U3J2Jy2IlLcjU/IHA5bsGyHfdhfkC7CyLC/YVbYKdKmRYrdhWjkhNkag1BF3QNmadv2Cdv2FXfsL43Iu3uFuwKsVk2dn6f0b6vLbXwjyxiawwdPk6qaWNfyenh/TI4alP5SPRxxhiWmEdI20065IawWnTpaVUeJ1eN4+oaffdHr1JN+Tzv1G5ZYea3JFjmajCOp8l4pKStM5s7belcF9PLTsWzxrm+ut/eyQvuJvwYd4qL3NIPfYw/k1g+Qul1Um46ePNHC4XwdeSVsxfxbaNypeZUSx6Y2Yd/5N5ylJEQx6siVcs3HHqbfHoYMWnp4vu2ex0SjGKk1RwxxrTGKXBviyxyZlji9o8o5/db7ucvZTtJgTH9qKOryn29HndTH6eTbhnoGPU4fq49uewR5zYtWxLTi6fJFmG2moFLYz1Bq4Iq7v7BexFicuALvyF7GbkF7cgVJ+xamZufkNSoDTU73Ku+5lqVD1IDbV5HqMNaGp2EauWxEnuTKVPcUpp2UDdPfgHJUQ37E2+4BJu2Qwk999iW/YHE3zuJvvYn7BsoGwsT37cCe2zQFN2L+7EwvcKfoPRN1yFgUKwFYDsLFYXfcCoJznGMd2z67oenj03TQglTrf2eJ+idH9TN9aa2XFn0Vlxm1lkh3RilvXc6JcGCaTszYazywrdHm58LyT1Xuj2WtS38HDmSg3+SWLzXi/wCJly5moxDL0ubBblGj2+kjUW/JrkjHLBqSsbWtx89jyLhs1s06v9PcG54fwckZ18ZqmLN9dOev621ApEBFkx01WTchOtuxqvnHZh9J9gukoRaZphxwUrSt8l4+nvnyazjHCvuxpeoc8301GK3lJ0kdmPpY4qyLlvcIdFGWGOWrk9/sdEI6sL8pmpMebrvXZH9qoaexEHUE72Ku90bcjTGuCU9gT/AHN1fT/Ui5wXy8eTy56ounse45Jcs58+HDnjvtLyTFlx5CkPV+BdRhlgmoye3Z+TJSutzLbbUCfHgy1BqINb/AN0jLUDl7Kob357C1P+KJk9tyb/oI1Ur7lXaMG+w02tmBs5UNSZjq33Hq9hGjk7DVuZOT8hbA0k99yW67k6mS3f5Arnkzk/INpdu5LfsqORyAm+QT8mhVgxP7hfsgL9iv8g+RFDvcLEAU79he4hdwLvjc06bDLqM8YLhvcx5fs+g/SOilix/VmqlLhBNel08I4ccYRXCNjKnF7AsunaSDLVq9jlns5R7o6IyUuDLLG5OXolmiMM9WN77o5OpuUtK5Zrge017HCGrJqfYxWmmGChhUXzQNL+Sm1RjKV3RcNE901RyZ+mhlTTW50tsmnJ0iY1K8mfSzxNpW0ZNOu6PaePnYyy9NGafx3DU6eVHJWxqsyS3Nn0GqVI2/+MhGm7sY1/0c0OolKVQTZ0QwOWaMsj3vg6sXTwxK4xSHGGrMvCYxm967FNQkov8AazSEVG0v2sx6qDljUlyjHB1Ti6k7VnTXJ2alFOLew4zXFqji6nMoyvszn+vkzxcYbLyYvcjXPFr0Z9VFPTD5SI/y1VuX8HDFyxKpRv2ZZMsZTTqmc/8ApbfHWfFHc+q1tWtjWLTSpnnRmm0dOOehX2LtXriJ67p551GUHx2PManj2mmmexjyuctuB58MM0akjWudmPF17DU2zXP0eTE/j8onPuudis401NBqe1md+xJ+wNJt1uZ2AiitQWSHtgVdcjciLXdibp9gLb90PWqpmTYX6CNXIlv78kOW4SfkCr9kuTE2JsowaJdoHLyDaKgsL3ExcBVN+QELuBVsQmFgMO4gKPQ/TMOCWVZOokklwj6D/KwbJZIrxufIK5NLuduHptSV8mbcWc6+nUlJfGSf2YOKlyjwsePPi3x5XftnZ036hJPRnjT/APITqVLxY656sTtbouGRZIPzQ04zjs7TOSaeHKt9isssF/XnHg601FUckZX1Da8GksqW3cy00lK3SCvZnB0rbBZE7SIRppGkkSn7HdgOxN26SH9hppsv2isWNR+T5Ccrl22FPJWy3ITvkoMr2dE9L+8baewJqLtGfr1r9O1q07PM6iOPHl3ntfBeTqnplHHL5+Dy1iySyfUzvZsl6nU+yS/p6UoKca/A4QWOCindBCUXFOLVCnJ09Lpni9+nT3MRn6iOFb7vweZPqHOdmXUuf1m5Myctj1cfHJE46elgnxKT2OhT+q0k/iePHJJpW9ju6TNVJmrMdfy162OoRSWyKU/Jy/VqNvgWOUptPhEc7Hat42YdR0ePPG0qmkUpVFFKTLrOPCyRljm4yQrr7HpfqOBTx/US+S5PKs3PWWl+WJt+iW9uOwXtwAN0LX2E3sJv2UVe4N1ZDYO73CK1A3/BImwKtivfklvsDe4RWp/7E5PfcVoTdFViAmBWVNg9thfyAUB6DuAB6D0ABB6DuHc2wY9UlaFuLJvjbpsPEmtzvxoxxw4Ruo+DjbrvJkWm0wkoyVSVkOS2TBz90Qp4epl0s1FtvG/9HZ1U4y6XXHe+Dy8sk7ROPq66aWFv7HTmuXU/jox5Kgq5Zako/KT3o4IZ1FXYnneSWwwdzyubpG+NVt3fg5MNQVtqzZZ07RB0p/6Hq22Ob6qfD4NIzTj/AOio11jT+1mSmuCrLEVOW9oIyM/3Pn7l6fi15OXXySLIzy5Um90q9mMs2TPcMSaj/wCTMo9O5Tcs8vinwVPqKjoxR0xXgzbv166c8Xo1CHTfOUtUxS+pnuT2XgzxSg5N5H+S8nUxjHTDcnu/+u8n4+QdNkepw53OnJJqJxdJvmtHXl33Mdz/AEz39vP6lJ7nHW525qbaOGW0j08fTl0uO/B14moQ1OtjjjLTuNzcvsWzVlduKc5y1Se3ZHoYsi012PJwTqkdKzaFbe3azFjf3Ha8qRpGfxvseTHNPJK+x2QzfFImJY6cktWNp8UeJOo5GvB6by/A8rI/+STN8ufR37C/ZDe3Am/RthTYMix2BTdA5E6hvuEEnQm1RLE2BTYm9xWJsCrE37FYmBIABpAAAAAAAAAC3dAXCOqR3YoqKVHPiSS9nTjfCOXV124mOhduLE5NCXgbaqjDSdasl5PZE9mZN/c1iWjJPwcuq5GmWVMws6SOfVXqZtjajv3OdGkXYqR0fVcu5pGVrfk5omsZeDKumMr9bGkJPjscsZNvi2awnfHbkuJa6r/FXbNsSb3fBxKV3KTaivHc6cXUKbUUqOPy9WTIcS310ifDOXN1mPE6ttrkFn+rHVF7Hm/Dr7bjlnqllcd3uKbULiufJ0trd9zlxw+plbb9nol37eidbFY8Dmm5Oiv8Xve1mspKEW/BzyzSn8YKiS9Vy7+bGmKcceXTskinkllm9OyMvotxtvc6IrRD5KkTrPtyn5dX1y5Y72ceVb3R3qccja8HFndPg7cfxvrz7YLctGabLTpbHSsc1qnppsFJze/Bkre7LjKqI3ruhGMY2Jy9nPLLpSVC+o2Zxq9N55Kjz2ONvuOc77kWakcurqrCxMTKyoHwTYMBsBA3QA35EwYmUF9gvsKwCHf9ibATAAACoAAAAAAAHHkRUeQsb4zoh2SOeP4NYy8HKu0dCewGamNS3MqJ1wc8maykYZeTUZYTdy8EnZhhByVqzXJ0kckW4qmlZr8ozeL9vORSYmnFtPZoRphspLgqMrdInDinllUV/J6XT9NHEre8u5z76nLcZQ6aWjVJ0qCM4q0lbNOqlLZXszD6iUdKRiW2a68/HL7W0pwyLVLauwYcmvK5JUkYxhKT4o1uOKD+xLJ9Ru2SM+qUIKUv+0jPocrjNx7Mzk5dTl24OqOjBB8f+zd85/G/bz7t11cmMIOGVv8A6sMWaOSNp7l6rTOHs8dZWXUN3zsQ1WK483ubSipxaZhKKgmtX8HTn+OF46/Jr087T1MefPq+K4ObU2tuDXA4NtyF5k9ejjj8ZtEcTrXdJHLnxSvUt4nXmy6/jHZInGvjUto9jXNs9p8nOza4VCVXQNONWdri4PgyyQ1rw0bnWuX4+MXFxStpMrBgnmku0fIYob/8jelHVDqo46UY7Dq2T/JObfa26rpF/jJw5itzy72PUj1acHGUatHm5IOLvszHx79U65siP6DsK9gs7MKsQrH2AHxsDewrHYADYmwbAGJgwCEP0ICgAAAAAAgAAAAAAAqIAStc/bWL7GkX2ADFdItPgdqIAZaZSyrhGc22l6ADbIxzafJ39NkvkAJ01z7HL18EsqcVyTi6SUo6pcABOurOZjnZ7XXhyaY/TjH5HTBSW8uQA49s8237c/VtSar7GeKUXKq38gBuT/L0/pWXNDGvZxZMrySdvbwAHTjmSa8/Vp4o5ece1lvBkn+6QATrq6kmrxYZ4pWpHTbrkAOdu310kxnmnJL0Zxg5/YANTyOu5GyqEdlwY3qltywAT+rz5NdEMCjHXkfHYylLW7TaSADPN32vL83VtxtCsuHb90Tndxu0ADn7xv47sYrVOXo6I44wSbW4Ab6/jcKeTW0or0aZMSfTqD/cACT2RPm6s5ec006YrADq4/oMGwAKTBgAQMGAFCYAAB3AACAAAD//2Q=="/>
</body>
</html>
Community
  • 1
  • 1
Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Hi Shnugo,Thanks for this.I have no data at the moment in my DB so can't test this. I will be working on SQL2012, that's mind blowing how a alphanumeric reference can generate an image without the need to store it. Wish I could insert a GIF for mind blown – Lauren Buxton Nov 02 '16 at 15:04
  • Fantastic a working example, you have gone all out Shnugo, thank you! – Lauren Buxton Nov 03 '16 at 09:42