将XML拆分为多个SQL表 [英] Splitting XML into multiple SQL tables

问题描述

大家好，

我在不久前发布了这个问题并收到了Martin使用LINQ完全有效且有效的答案，但想知道是否有人
有没有通过使用XmlReader实现这一点？

如果是这样，请发表一个例子或指出我正确的方向。我找到了许多使用
XmlReader将xml读取到单个表但不是多个表的示例。我真的不想进入为什么我不会使用LINQ，足以说出它的管理决定：o）

----------------- ---原帖------------------------

< p style ="margin：0cm 0cm 10pt"> 我有一个问题，我希望有人可以回答或指出我正确的方向。我有一个XML文件，需要将
导入SQL数据库，但跨多个表。表1“ DRIVER”需要包含每个驱动程序的单个记录，包括所有驱动程序个人信息< driverid>，< fullname>，< birthdate>和< placeofbirth>。表2“车辆”
需要包含每种车型的记录，每条记录应包含< driverid>，vehicle< name>和< type>。任何帮助将不胜感激。 C＃代码将简单地放入按钮单击事件中以启动它。我在下面包含了
a简单的XML文件布局。

谢谢Gary。

<？xml version =" 1.0"编码= QUOT; UTF-8英寸？>

< drivers>

 
< driver>

   
< driverid> red5< / driverid>

   
< fullname> luke skywalker< / fullname>

   
< birthdate>很久以前< / birthdate>

   
< placeofbirth>一个遥远的星系< / placeofbirth>

   
< vehicle>

     
< name> landspeeder< / name>

     
< type> ground< / type>

   
< / vehicle>

   
< vehicle>

     
< name> x-wing< / name>

< span style ="font-family：'Courier New';颜色：黑色;字体大小：10pt">      
< type> air< / type>

   
< / vehicle>

 
< / driver>

 
< driver>

   
< driverid> eccho7< / driverid>

   
< fullname> Han Solo< / fullname>

   
< birthdate>很久以前< / birthdate>

   
< placeofbirth> correlia< / placeofbirth>

   
< vehicle>

     
< name> millenium falcon< / name>

     
< type> air< / type>

   
< / vehicle>

 
< / driver>

< / drivers>

解决方案

那里：

您可以使用xml序列化直接将数据反序列化为对象。然后你可以用它们做你想要的，使用LINQ to SQL或ADO.NET将它们插入到数据库中。

 public class Drivers 
 {
 [XmlElement] 
 public Driver [] driver; 
} 
公共类驱动程序
 {
 [XmlElement] 
 public string driverid; 
 [XmlElement] 
 public string fullname; 
 [XmlElement] 
 public string birthdate; 
 [XmlElement] 
 public string placeofbirth; 
 
 [XmlElement] 
公共车辆[]车辆; 
} 
 
公共类车辆
 {
 [XmlElement] 
公共字符串名称; 
 [XmlElement] 
公共字符串类型; 
} 
 
司机ds; 
 FileStream fs = new FileStream（@"testout.xml"，FileMode.Open）; 
 XmlSerializer x = new XmlSerializer（typeof（Drivers））; 
 ds =（司机）x。反序列化（fs）; 

Hi to all,

I posted this question a short time ago and recieved a totally valid and working answer from Martin using LINQ but was wondering if anybody had ever accomplished this through the use of XmlReader?

If so could you please post an example or point me in the right direction. I have found many examples of reading xml to a single table using XmlReader but not to multiple tables. I don't really want to go into why I will not be using LINQ, suffice to say its a mamangement decision :o)

-------------------- Original Post ------------------------

I have a question which I hope somebody can either answer or point me in the right direction. I have an XML file which needs to be imported into a SQL database but across multiple tables. Table 1 “DRIVER” needs to contain a single record per driver including all driver personal information <driverid>, <fullname>, <birthdate> and <placeofbirth>. Table 2 “VEHICLES” needs to contain a record for each vehicle type, each record should have <driverid>, vehicle <name> and <type>. Any help would be greatly appreciated. The C# code will simple be placed into a buttons click event to kick it off. I have included a simple XML file layout below.

Thanks Gary.

<?xml version="1.0" encoding="utf-8" ?>

<drivers>

  <driver>

    <driverid>red5</driverid>

    <fullname>luke skywalker</fullname>

    <birthdate>a long time ago</birthdate>

    <placeofbirth>a galaxy far far away</placeofbirth>

    <vehicle>

      <name>landspeeder</name>

      <type>ground</type>

    </vehicle>

    <vehicle>

      <name>x-wing</name>

      <type>air</type>

    </vehicle>

  </driver>

  <driver>

    <driverid>eccho7</driverid>

    <fullname>Han Solo</fullname>

    <birthdate>a long time ago</birthdate>

    <placeofbirth>correlia</placeofbirth>

    <vehicle>

      <name>millenium falcon</name>

      <type>air</type>

    </vehicle>

  </driver>

</drivers>

解决方案

Hi, there:

You can use xml serialization to deserialize the data into objects directly. Then you can do what you wanted with them to use either LINQ to SQL or ADO.NET to insert them into database.

  public class Drivers
  {
    [XmlElement]
    public Driver[] driver;
  }
  public class Driver
  {
    [XmlElement]
    public string driverid;
    [XmlElement]
    public string fullname;
    [XmlElement]
    public string birthdate;
    [XmlElement]
    public string placeofbirth;

    [XmlElement]
    public vehicle[] vehicle;
  }

  public class vehicle
  {
    [XmlElement]
    public string name;
    [XmlElement]
    public string type;
  }

      Drivers ds;
      FileStream fs = new FileStream(@"testout.xml", FileMode.Open);
      XmlSerializer x = new XmlSerializer(typeof(Drivers));
      ds = (Drivers) x.Deserialize(fs);

这篇关于将XML拆分为多个SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！