0

Scenario :

  • I convert a pdf file to base64 and put it in an xml, and

Problem :

  • when I receive the xml I will have to convert this base64 data back to the original pdf file using SQL Server and store it somewhere.

I found this link but could not figure out how to do it.
This is what I have:

DECLARE 
    @SQLcommand VARCHAR(8000),
    @MyOriginalFile VARCHAR(8000),
    @RawData VARCHAR(8000)

SET @RawData = 'JVBERi0x etc'

SET @SQLcommand = 'bcp "SELECT @MyOriginalFile = @RawData" queryout "\\MY-SERVER\MySharedFolder\New.pdf" -T -n -S A70195\dev'

EXEC xp_cmdshell @SQLcommand
  • I think I need to understand what does the -T -n -S A70195\dev mean.

    Can someone please help?
    Thanks.
Abhishek Kumar
  • 2,501
  • 10
  • 25
Blue Bamboo
  • 147
  • 3
  • 13

1 Answers1

0

Starting from SQL2005 I would use VARBINARY(MAX) data type for PDF content and FOR XML ... , BINARY BASE 64 to convert binary values to BASE64:

-- VarBinary -> XML
DECLARE @PdfContent VARBINARY(MAX)
SET @PdfContent = 0x12345678

SELECT  @PdfContent AS BinaryContent
FOR XML RAW, BINARY BASE64
-- Output <row BinaryContent="EjRWeA==" />

-- XML -> VarBinary
DECLARE @PdfContentAsXML XML
SET     @PdfContentAsXML = N'<row BinaryContent="EjRWeA==" />'
SELECT  x.XmlCol.value('(@BinaryContent)', 'VARBINARY(MAX)') AS BinaryContentFromXml
FROM    @PdfContentAsXML.nodes('row') x(XmlCol)
-- Output 0x12345678
Bogdan Sahlean
  • 19,233
  • 3
  • 42
  • 57
  • I've tried your code and I can see my binary data. Does it mean we can convert binary data back to the original pdf file and save it to disk? – Blue Bamboo Mar 28 '15 at 06:05
  • I would use a table with a `VARBINARY(MAX)` column to store PDF content plus a column `FileName NVARCHAR(260)` to store file name. If PDF content is bigger than ~ 1MB then I would use (after testing) FILESTREAM](https://msdn.microsoft.com/en-us/library/gg471497.aspx). A FILESTREAM column will store the content in separate files on filesystem but not in data files of SQL Server database. Here you may find how to import / export PDF files in/from SQL Server DB: http://jahaines.blogspot.ro/2009/10/ssis-importing-binary-files-into.html . – Bogdan Sahlean Mar 28 '15 at 06:38
  • Please see above Bogdan Sahlean. Thanks – Blue Bamboo Mar 28 '15 at 06:53
  • @user1564533: Documentation for `bcp` utility is available [here](https://msdn.microsoft.com/en-us/library/ms162802.aspx). – Bogdan Sahlean Mar 28 '15 at 11:39