起因
SQL Server 导入导出功能中,可以导出为 Excel,但是 SQL Server 有一列为 varchar(max),导出时总是出错:在将列“内容”(26)转换为列“内容”(87)时数据转换失败。转换操作返回状态值 4 和状态文本“文本被截断,或者一个或多个字符在目标代码页中没有匹配项。”。
开始以为是字符串长度超过 Excel 单元格的最大字符数 32767,但是换为 Access 也出错,且确认最大长度还不足 16000,所以不是字符串超长的原因。
于是想到导出 XML。
SQL Server 导出 XML
命令提示符中,输入 bcp,就可以看到命令提示了,我们举个示例。
bcp "select * from tbl FOR XML PATH('article'), ROOT('articles'), TYPE" QueryOut "C:\itpow\test.xml" -S . -T -d db -c
bcp 是命令。
然后跟一个 SQL 语句,用引号引起来,注意后面有 FOR XML...
然后跟 QueryOut,再跟路径,表示将这条查询输出到这个路径。如果是 Out,则是直接输出表。
-S 后面跟个点,表示 Server 是本地。
-T 表示使用信任连接。
-d 后面跟 db,表示数据库是 db,如果省略这个,则在查询语句中要指写数据库名。
-c 表示以字符类型输出。
如果我们遇到:Unable to open BCP host data-file,则多半是我们没有目标文件的写入权限,比如我们直接将 xml 保存在 C 盘根目录,通常是没有权限的。
回过头来说说 FOR XML(这些关键字都忽略大小写,为了明显,我们使用大写)
FOR XML 我们可以在 SQL Server Management Studio 中测试。
FOR XML AUTO:表名作为一条记录的节点名,字段名作为属性名。
FOR XML RAW:row 作为一条记录的节点名,字段名作为属性名。RAW('article'),表示以 article 作为属性名。
FOR XML PATH:row 作为一条记录的节点名,字段名作为子节点名。PATH('article'),表示以 article 作为属性名。
FOR XML AUTO, ELEMENTS:字段名作为子节点名,而不是属性名。
FOR XML RAW, ELEMENTS:字段名作为子节点名,而不是属性名。
ROOT('articles'):添加根节点 articles,比如:FOR XML PATH('article'), ROOT('articles')。
TYPE:如果指明 TYPE 指令,表示输出的是 XML 类型,XQuery 一类的可以直接查询;否则的话,就当作普通字符串对待。在 SQL Server Management Studio 中测试时,我们会发现没有指明 TYPE,打开查询结果时,文件名通常是 XML_.....xml 很长一串,而如果指明了 TYPE,打开时,通常是 xmlresult2.xml 这类很短的名称。
值格式:
不论值是以属性值输出,还是以节点内容输出,都是经过 HTMLEncode 编码了的,即数据库中存储的 &,输出时是:&。
不过内容中的特殊字符不会被过滤掉,请参照本文的相关阅读,自行过滤这类不适合 XML 的特殊字符。
还有就是要注意整个文件的编码不是 UTF-8 的。
相关阅读