将 XML 插入 SQL Server 2008 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3628846/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT XML into SQL Server 2008 database
提问by wonea
Hello I'm trying to insert some XML data into a table on SQL Server 2008. However I keep getting thrown this error;
您好，我正在尝试将一些 XML 数据插入 SQL Server 2008 上的表中。但是我一直被抛出这个错误；
XML parsing: line 1, character 39, unable to switch the encoding
XML解析：第1行，第39个字符，无法切换编码
The database column filemeta uses the XML datatype, and I've switch the encoding to UTF-16 which I believe is necessary for adding XML data.
数据库列 filemeta 使用 XML 数据类型，我已将编码切换为 UTF-16，我认为这是添加 XML 数据所必需的。
INSERT INTO testfiles
(filename, filemeta)
VALUES
('test.mp3', '<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>');
Help, I'm stuck.
帮助，我被卡住了。
NB: I created the XML with XMLTextWriter.
注意：我用 XMLTextWriter 创建了 XML。
回答by marc_s
Yes, there are issues when you try to insert XML into SQL Server 2008 and the XML contains an encoding instruction line.
是的，当您尝试将 XML 插入 SQL Server 2008 并且 XML 包含编码指令行时会出现问题。
I typically get around using the CONVERTfunction which allows me to instruct SQL Server to skip those instructions - use something like this:
我通常会使用CONVERT允许我指示 SQL Server 跳过这些指令的函数 - 使用如下内容：
INSERT INTO testfiles
(filename, filemeta)
VALUES
('test.mp3', CONVERT(XML, N'<?xml version="1.0" encoding="utf-16" standalone="yes"?>......', 2));
It has definitely helped me get various encoded XML stuff into SQL Server.
它确实帮助我将各种编码的 XML 内容导入 SQL Server。
See the MSDN docs on CAST and CONVERT- a bit down the page there's a number of styles you can use for CONVERTwith XMLand some explanations about them.
请参阅有关CAST和CONVERT MSDN文档-有些低落的页面有一些款式，你可以使用CONVERT与XML和一些关于他们的解释。
回答by Joe Stefanelli
You just need to include Nin front of your XML string to make it unicode.
您只需要在 XML 字符串前面包含N即可使其成为 unicode。
INSERT INTO testfiles
(filename, filemeta)
VALUES
('test.mp3', N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>');
回答by Talha Imam
This worked for me without any errors:
这对我有用，没有任何错误：
DECLARE @input XML
SET @input = N'<?xml version="1.0" encoding="utf-16" standalone="yes"?><!--This is a test XML file--><filemeta filetype="Audio"><Comments /><AlbumTitle /><TrackNumber /><ArtistName /><Year /><Genre /><TrackTitle /></filemeta>'
INSERT INTO testfiles (filename, filemeta)
VALUES ('test.mp3', @input);
