sql server 2008 xml文件到表

Question

我有一个xml文件，我试图导入到表中。我的xml文件如下所示：

<?xml version="1.0" encoding="utf-8"?> 
<xml_objects xmlns="http://www.blank.info/ns/2002/ewobjects"> 
<item_id item_id="41-FE-001"> 
<class display="true"> 
<class_name>FEEDER</class_name> 
</class> 
<name display="true"> 
<name_value>41-FE-001</name_value> 
</name> 
<attributes> 
<attribute> 
<attributename>Type</attributename> 
<value>EQUIP</value> 
</attribute> 
<attribute> 
<attributename>Tag No</attributename> 
<value>41-FE-001</value> 
</attribute> 
</attributes> 
</item_id> 
</xml_object> 

这是我正在使用的SQL。我无法让SQL返回任何值：

CREATE TABLE [dbo].[item_data](
[item_id] [nchar](15) NULL, 
[class] [nchar](10) NULL, 
) ON [PRIMARY] 
GO 

--INSERT INTO item_data (item_id) 
SELECT xmldata.value('(@item_id)', 'NCHAR') AS item_id  
FROM ( 
SELECT CAST(x AS XML) 
FROM OPENROWSET(BULK 'C:\xmlfile.xml', 
SINGLE_BLOB) AS T(x)) AS T(x) 
CROSS APPLY x.nodes('//xml_objects/item_id') AS X(xmldata); 

我试过各种xpath和SQL语法。我无法让SQL返回任何值。任何帮助将不胜感激。

Answer 1

你忽略那是根元素上定义的XML命名空间：

<xml_objects xmlns="http://www.blank.info/ns/2002/ewobjects"> 
      *********************************************** 

你需要把它添加到您的查询：

;WITH XMLNAMESPACES(DEFAULT 'http://www.blank.info/ns/2002/ewobjects') 
SELECT 
    xmldata.value('(@item_id)', 'NCHAR') AS item_id  
FROM 
    (SELECT CAST(x AS XML) 
    FROM OPENROWSET(BULK 'C:\xmlfile.xml', 
    SINGLE_BLOB) AS T(x)) AS T(x) 
CROSS APPLY 
    x.nodes('//xml_objects/item_id') AS X(xmldata);