从xml文件中检索数据并插入到数据库表中

I am using c# .net 1.1 and SQL Server 2000....

我使用c#。net 1.1和SQL Server 2000 ....

I want to retrive data from an xml file and store that data into the database table.

我想从xml文件中检索数据并将数据存储到数据库表中。

XML file:

XML文件:

<information>
  <data>
    <id="1"></id>
    <name>peter</name>
    <age>25</age>
  </data>
</information>

My databse table is

我的数据库表

id int,
name varchar(100),
age int

2 个解决方案

#1

First off - your XML is invalid:

首先-您的XML无效:

<data>
    <id="1"></id>

This is not a valid XML construct - what should it be??

这不是一个有效的XML构造—应该是什么?

An "ID" attribute on the data node? <data id="1">

数据节点上的“ID”属性? <数据id = " 1>

An "ID" element with the value inside? <data><id>1</id>

一个包含值的“ID”元素? <数据> < id > < / id > 1

There's a ton of ways you can do this - totally depends on your situation:

有很多方法可以做到这一点——完全取决于你的情况:

create a XML schema so you can deserialize this XML into a .NET object - works best if you have tons of those files to import

创建一个XML模式，这样您就可以将这个XML反序列化为。net对象——如果您有大量的这些文件要导入的话，效果最好
use Linq-to-XML if you're on .NET 3.5 and up

如果你在。net 3.5或以上，请使用Linq-to-XML
use traditional XML document processing

使用传统的XML文档处理

If you use traditional XML document processing, this works best if your files are relatively small (since it loads the entire file into memory). In that case, you could do something like:

如果您使用传统的XML文档处理，那么如果您的文件相对较小(因为它将整个文件加载到内存中)，这将是最有效的方法。在这种情况下，你可以这样做:

XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load("yourfilename.xml");

XmlNodeList dataNodes = xmlDoc.SelectNodes("/information/data");

foreach(XmlNode node in dataNodes)
{
    int id = Convert.ToInt32(node.SelectSingleNode("id").InnerText);
    string name = node.SelectSingleNode("name").InnerText;
    int age = Convert.ToInt32(node.SelectSingleNode("age").InnerText);

    // insert into database, e.g. using SqlCommand or whatever
}

As for inserting into a database - you can use a stored procedure, or a SQL statement in a SqlCommand - totally up to you. Once you have the three bits of information (id, name, age), you can do with those whatever you like.

至于插入数据库——您可以使用存储过程或SqlCommand中的SQL语句——完全取决于您。一旦你有了3位信息(id, name, age)，你就可以随心所欲地处理这些信息了。

Word of warning also: this includes no error checking whatsoever! If a <data> node should be incomplete or a sub node has a wrong name, this will crash - you need to provide some error checks, too! (left out for simplicity)

警告:这包括任何错误检查!如果节点应该是不完整的，或者子节点有错误的名称，这将导致崩溃——您还需要提供一些错误检查!(左为简单起见)

Marc

马克

#2

For when the XML file is visible to the SQL Server service, this works using plain old T-SQL, just tweak it to insert into your table.

对于SQL Server服务可见的XML文件，这可以使用普通的老T-SQL，只需对其进行调整，将其插入到表中。

DECLARE @DOC INT
DECLARE @XML VARCHAR(MAX) 

-- Dump entire file into variable
SET @XML =
( 
    select BulkColumn from openrowset 
        (BULK N'<your filename>'
            , single_clob) as RuleFile
)

-- Prep doc
EXECUTE sp_xml_preparedocument @DOC OUTPUT, @XML

-- Return data
SELECT * 
    FROM OpenXML(@DOC, '<your xpath>', 2) 
    WITH <your table>

-- Clean up
EXECUTE sp_xml_removedocument @DOC

#1