SQL Server将Base64数据转换为文件 [英] SQL server to convert base64 data into a file

问题描述

场景:

• 我将pdf文件转换为base64并将其放入xml中，然后

问题:

• 当我收到xml时，我将不得不将该base64数据转换回去 使用SQL Server转换为原始pdf文件，并将其存储在某处.

• 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

• 我认为我需要了解-T -n -S A70195 \ dev的含义.
  
  有人可以帮忙吗?
  谢谢.
• I think I need to understand what does the -T -n -S A70195\dev mean.
  
  Can someone please help?
  Thanks.

推荐答案

从SQL2005开始，我将使用VARBINARY(MAX)数据类型获取PDF内容，并使用FOR XML ... , BINARY BASE 64将二进制值转换为BASE64:

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

这篇关于SQL Server将Base64数据转换为文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！