xml 将xml文件导入sql server表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11631352/
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
import xml file into sql server table
提问by user1547369
I'm looking for help reading an XML file into a SQL Server 2008 R2 data table.
我正在寻求将 XML 文件读入 SQL Server 2008 R2 数据表的帮助。
My XML file looks like this:
我的 XML 文件如下所示：
<?xml version="1.0" encoding="utf-8"?>
<e_objects xmlns="http://www.blank.info/ns/2012/objects">
<item item_id="41-FE-001">
<class display="true">
<class_name>FEEDER</class_name>
</class>
</item>
</e_objects>
My create table SQL looks like this:
我的创建表 SQL 如下所示：
CREATE TABLE [dbo].[handover_data](
[item_id] [nchar](15) NULL,
[class] [nchar](10) NULL,
) ON [PRIMARY]
GO
This is the SQL I'm using. I can't get it to return any values:
这是我正在使用的 SQL。我无法让它返回任何值：
SELECT xmldata.value('(item[@name="item_id"]/@value)[1])', 'NCHAR') AS item_id,
xmldata.value('(class_name)', 'NCHAR') AS class
FROM (
SELECT CAST(x AS XML)
FROM OPENROWSET(BULK 'C:\xmlfile.xml',
SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY x.nodes('e_objects/*') AS X(xmldata);
Any help would be greatly appreciated. Thanks
任何帮助将不胜感激。谢谢
回答by marc_s
Well, first of all - you're again, like in your last question which I already answered, ignoring the XML namespace- don't !
好吧，首先 - 你又来了，就像我已经回答的上一个问题一样，忽略了 XML 命名空间- 不要！
<e_objects xmlns="http://www.blank.info/ns/2012/objects">
*********************************************
That's the XML namespace- it's there for a reason - don't just ignore it!
那就是XML 命名空间——它的存在是有原因的——不要只是忽略它！
Then you're not paying attention to the structure of your XML file. Your <class_name>element is inside the <class>element and that again is inside the <item>node. So you need to select accordingly.
那么您就没有注意 XML 文件的结构。您的<class_name>元素位于元素内，而该<class>元素又位于<item>节点内。所以你需要相应地选择。
Also: your syntax to select the XML attribute is invalid; see my sample - does that work for you?
另外：您选择 XML 属性的语法无效；看我的样本 - 这对你有用吗？
Try something like this:
尝试这样的事情：
;WITH XMLNAMESPACES(DEFAULT 'http://www.blank.info/ns/2012/objects')
SELECT
xmldata.value('(@item_id)[1]', 'NCHAR(10)') AS item_id,
xmldata.value('(class/class_name)[1]', 'NCHAR(20)') AS class
FROM
(SELECT CAST(x AS XML)
FROM OPENROWSET(BULK 'C:\xmlfile.xml',
SINGLE_BLOB) AS T(x)) AS T(x)
CROSS APPLY
x.nodes('/e_objects/item') AS X(xmldata);
This gets a list of all the <item>nodes under <e_objects>as X(xmldata). From those elements, I then select
这将获取as<item>下所有节点的列表。从这些元素中，我然后选择<e_objects>X(xmldata)
- the
item_idattribute on the<item>node itself - the
<class>/<class_name>element contained inside the<item>node
- 节点本身的
item_id属性<item> - 该
<class>/<class_name>元件包含在所述内部<item>节点
Also: I would recommend notusing NCHARtoo much - it's fixed-length, i.e. it's always 10 or 20 characters long - even if your string is only 2 characters. Not a good idea, most of the type - use NVARCHAR(20)instead - much better, for string lengths >= 5 !
另外：我建议不要使用NCHAR太多 - 它是固定长度的，即它总是 10 或 20 个字符长 - 即使您的字符串只有 2 个字符。不是一个好主意，大多数类型 - 使用NVARCHAR(20)- 更好，对于字符串长度 >= 5 ！
回答by Waqar Janjua
Create a table with an xml column to store the column. Try this, it works fine for me
创建一个带有 xml 列的表来存储该列。试试这个，它对我来说很好用
CREATE TABLE XmlImportTest
(
xmlFileName VARCHAR(300),
xml_data xml
)
GO
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'C:\xmlPath.xml'
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
SELECT * FROM XmlImportTest
DROP TABLE XmlImportTest
Reference: http://weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx
参考：http: //weblogs.sqlteam.com/mladenp/archive/2007/06/18/60235.aspx
