TSQL FOR XML导出实际UTF-8编码数据 [英] TSQL FOR XML Exporting ACTUAL UTF-8 Encoded data

问题描述

我们有一个写XML的过程（使用SQL的FOR XML）。当通过批处理文件中的SQLCMD执行输出时，输出为UTF-8格式（特别是8位ASCII字符变为2字节）。
当我通过SSIS中的Execute SQL Command执行同样的操作时，它不是UTF-8编码的。

这是一个简单的例子。 ®应该成为2个字节：

  SELECT'Diversity®Certified'作为fldAgentLastName 
 FOR XML PATH（'agent' ），ELEMENTS，TYPE，ROOT（'agents'）
  

输出是：Diversity®Certified p>

它应该是：Diversity®认证

并使用SQLCMD。我明白内部XML存储为UCS-2（？），但是我需要一种方式来获取UTF-8编码数据（不只是8位）的输出。

我也不能使用我所看到的BCP技巧。

我不想使用CDATA标签，因为这将需要重新创建一个巨大的丑陋查询

我在网络上找到的一切都不会对高ASCII字符进行编码。

这是运行在SQL Server 2008 R2上。

解决方案

我想我所要做的只是问，然后我会找到自己的答案：问题不在于SQL，它是SSIS的下游。在另一个线程中，我将基于此答案的CONVERT代码页和最终的文本文件更改为65001：

...解决方法很简单，尽管违反直觉 - 添加OLE DB Source和平面文件目的地之间的数据转换转换步骤，将您的输入Dat列从DT_NTEXT转换为DT_TEXT，代码页为65001.然后将新转换的列直接提供给平面文件的输出列，文件dest ...问候，Jacob

http://www.sqlservercentral.com/Forums/Topic719421-149-1.aspx

We have a process that writes XML (Using SQL's FOR XML). When it was executed via SQLCMD in a batch file the output was in UTF-8 format (specifically 8-bit ascii characters become 2 byte). When I do the same thing through Execute SQL Command in SSIS it's not UTF-8 encoded.

Here's a simple example. The ® should become 2 bytes:

SELECT 'Diversity® Certified' as fldAgentLastName
FOR XML PATH('agent'), ELEMENTS, TYPE, ROOT('agents')

The output is: Diversity® Certified

it SHOULD be: Diversity® Certified

and was using SQLCMD. I understand that internally XML is stored as UCS-2(?), but I need a way to get the output as UTF-8 encoded data (not just 8-bit).

I also cannot use the BCP trick I've seen mentioned.

I don't want to use the CDATA tag because that would entail recreating a giant ugly query.

Everything I've found on the web doesn't encode the high ascii characters.

This is running on SQL Server 2008 R2.

解决方案

I guess all I had to do was ask, and then I'd find my own answer: The problem wasn't with SQL, it was downstream in SSIS. I changed the codepage for a CONVERT as well as the final Text file to 65001 based on this answer in another thread:

...The workaround is simple albeit counterintuitive - add a Data Conversion Transformation step between the OLE DB Source and the Flat-File Destination that converts your input "Dat" column from DT_NTEXT to DT_TEXT with a codepage of 65001. Then you feed the newly transformed column directly to the output column in your flat-file dest. ... Regards, Jacob

http://www.sqlservercentral.com/Forums/Topic719421-149-1.aspx

这篇关于TSQL FOR XML导出实际UTF-8编码数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！