2

I have a SQL table set up that stores .json files as base-64 encoded text of type varchar. Is there a way I can convert these files to JSON string using only SQL statements? I'm just trying to retrieve the JSON string from the encoded files.

I've tried many of the T-SQL JSON functions. However, they only work on JSON strings, not JSON documents.

Joseph Kersey
  • 21
  • 2
  • 2
    Yes you can, although it's not completely obvious: https://stackoverflow.com/q/5082345 – Roger Wolf Aug 08 '19 at 22:53
  • Possible duplicate of [Base64 encoding in SQL Server 2005 T-SQL](https://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql) – Tom Blodget Aug 10 '19 at 00:45

1 Answers1

1

On SQL Server 2005 and later you can use XML queries to do it for you, e.g.:

if object_id(N'dbo.StackOverflow') is not null
    drop table dbo.StackOverflow;
go
create table dbo.StackOverflow (
    Base64JSON varchar(30) not null
);
insert dbo.StackOverflow (Base64JSON) values ('eyJIZWxsbyI6ICJXb3JsZCEifQ==');
go
select Base64JSON,
    [JSON] = cast(cast('' as XML).value('xs:base64Binary(sql:column("StackOverflow.Base64JSON"))', 'varbinary(30)') as varchar(30))
from dbo.StackOverflow

Which yields the result:

Base64JSON                     JSON
------------------------------ ------------------------------
eyJIZWxsbyI6ICJXb3JsZCEifQ==   {"Hello": "World!"}
FooMonkey
  • 159
  • 1
  • 3