sql server 2008 xml 文件到表 [英] sql server 2008 xml file to table

问题描述

我有一个 xml 文件，我正试图将其导入到表中.我的 xml 文件如下所示:

I have an xml file that I'm trying to import into a table. My xml file looks like this:

<?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 返回任何值:

This is the SQL I'm using. I can't get the SQL to return any values:

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 返回任何值.任何帮助将不胜感激.

I've tried various xpath's and SQL syntax. I can't get the SQL to return any values. Any help would be greatly appreciated.

推荐答案

您忽略了在根元素上定义的 XML 命名空间:

You're ignoring the XML namespace that's defined on the root element:

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

您需要将此添加到您的查询中:

You need to add this to your query:

;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);

这篇关于sql server 2008 xml 文件到表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！