sql-server - 将 Bae64Encode CSV 文件的数据迁移到 SQL Server 表

Question

我想获得解决问题的帮助。请帮忙。

我有一个.CSV使用 SSIS 从端点 URL 下载的 Base64 编码文件。该文件有 4 列和 1 列行，我关心的是具有 base64 编码值的文件列。我能够使用 SSIS 将该列转储到 SQL Server 表中，然后使用此代码使用 T-SQL 进行解码：

    SELECT 
        CONVERT(VARCHAR(MAX),
        CAST('' AS XML).value('xs:base64Binary(sql:column("FILE"))', 'VARBINARY(MAX)')) AS RESULT    
    FROM ABC

上面查询的结果如下所示：

Agentid,agentname, phone, email, address,state,country, zip,ssn 1,nick,56677700987,[e-mail],address,tx,12345,9990009999 2,Jack,12677700987,[e-mail],address,tx,145,12990009999 3,mike,56677700987,[e-mail],address,tx,12345,9990009999

所需的结果应如下所示：

+---------+-----------+-------------+-----------------+----------+-------+---------+-------------+-----+
| Agentid | agentname |    phone    |      email      |  address | state | country |     zip     | ssn |
+---------+-----------+-------------+-----------------+----------+-------+---------+-------------+-----+
|       1 | nick      | 56677700987 | [e-mail]   | address  | tx    |   12345 |  9990009999 |     |
|       2 | Jack      | 12677700987 | [e-mail] | address  | tx    |     145 | 12990009999 |     |
|       3 | mike      | 56677700987 | [e-mail]  | address  | tx    |   12345 |  9990009999 |     |
+---------+-----------+-------------+-----------------+----------+-------+---------+-------------+-----+

标签： sql-servertsql