取 XML 数据并插入 sql server 使用 c# asp.net

Question

现在我正在尝试提供一些服务，我可以在其中读取 xml 文件并将其解析为 sql server，我已经阅读并看到许多教程如何使用 c# 将 xml 解析为 sql server，但我仍然无法获取数据。

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

<Source 1 - Subject 17>

    SubjectType: Faces

    FaceConfidence: 100

    <appeared at 02/08/2018 5:28:43 PM> 

        FrameIndex: 1033

        Rectangle: at (210;169), width=63, height=84

    </appeared at 02/08/2018 5:28:43 PM>

    <track at 02/08/2018 5:28:44 PM> 

        FrameIndex: 1050

        Rectangle: at (210;134), width=70, height=94

        <Details available on frame 1050> 

            FrameIndex: 1050

            Status: Ok

            Eyes at: (260; 169) and (229; 169)

            Rectangle: at (210;134), width=70, height=94

        </Details available on frame 1050>

    </track at 02/08/2018 5:28:44 PM>

    <disappeared at 02/08/2018 5:28:46 PM> 

        TimeStamp: 02/08/2018 5:28:46 PM

        <Top 1000 of Best Matches> 

        no matches found

        </Top 1000 of Best Matches>

        Contains successfully generated template

    </disappeared at 02/08/2018 5:28:46 PM>

</Source 1 - Subject 17>

这是 xml 文件格式，这是我的尝试：

protected void Button1_Click(object sender, EventArgs e)

{

    string cs = @"Data Source=172.16.6.39;Initial Catalog=FC_SCAN;Persist Security Info=True;User ID=fc_adm;Password=P@ssw0rd";

    SqlConnection con = new SqlConnection(cs);

    XmlDocument doc = new XmlDocument();

    doc.Load("test.xml");

    var source = doc.DocumentElement.SelectNodes("Source").Cast<XmlElement>().ToList();

    var appeared = source[0].GetAttribute("Appeared");

    var disappeared = source[0].GetAttribute("Disappeared");

    var top = source[0].GetAttribute("Top");

    SqlCommand cmd;

    SqlDataAdapter da = new SqlDataAdapter();

    string sql = null;

    con.Open();

    sql = "Insert into Source values ('" + source + "','" + appeared + "','" + disappeared + "','"+top+"')";

    cmd = new SqlCommand(sql, con);

    da.InsertCommand = cmd;

    da.InsertCommand.ExecuteNonQuery();

    con.Close();

}

错误控制台总是出现在 xml 文件中，我非常感谢任何帮助、技巧或提示。

Answer 1

当我启动你的代码时，我立即在&nbsp;doc.Load("test.xml");线。异常消息非常清楚：An unhandled exception of type 'System.Xml.XmlException' occurred in System.Xml.dllAdditional information: Name cannot begin with the '1' character, hexadecimal value 0x31. Line 2, position 9.显然，它不喜欢命名为“Source 1 - Subject 17”、“appeared at 02/08/2018 5:28:43 PM”等的 XML 标签。更新：您不能使用 .Net XML 操作的东西，因为您的文件不是 XML（它声称是带有标题的 XML，但它有点撒谎）。如果您仍然需要将此文件的内容放入您的数据库中，您必须编写一个自定义解析例程来读取该自定义数据格式，或者，如果可能，联系为您生成此“XML”的人员并说服他们将它们的数据格式更改为真正的 XML。如果您在同一组织等中工作，这可能是最简单的方法。

Answer 2

我不是 100% 支持jdweng 的答案，但他的想法告诉我实际上应该有多个根元素。所以我的想法是每个来源都有自己的来源和主题 ID。&nbsp; &nbsp; <?xml version="1.0" encoding="utf-8" ?><Source><Source id="1"><Subject id="17">&nbsp; <SubjectType>Faces</SubjectType>&nbsp; <FaceConfidence>100</FaceConfidence>&nbsp; <appeared>&nbsp; &nbsp; 02/08/2018 5:28:43 PM&nbsp; &nbsp; <FrameIndex>1033</FrameIndex>&nbsp; &nbsp; <Rectangle top="210" left="169" width="63" height="84"/>&nbsp; </appeared>&nbsp; <track>&nbsp; &nbsp; 02/08/2018 5:28:44 PM&nbsp; &nbsp; <FrameIndex>1050</FrameIndex>&nbsp; &nbsp; <Rectangle top="210" left="134" width="70" height="94"/>&nbsp; &nbsp; <Details>&nbsp; &nbsp; &nbsp; <FrameIndex>1050</FrameIndex>&nbsp; &nbsp; &nbsp; <Status>Ok</Status>&nbsp; &nbsp; &nbsp; <Eyes>&nbsp; &nbsp; &nbsp; &nbsp; <location x="260" y="169"/>&nbsp; &nbsp; &nbsp; &nbsp; <location x="229" y="169"/>&nbsp; &nbsp; &nbsp; </Eyes>&nbsp; &nbsp; &nbsp; <Rectangle top="210" left="134" width="70" height="94"/>&nbsp; &nbsp; </Details>&nbsp; </track>&nbsp; <disappeared>&nbsp; &nbsp; <TimeStamp>02/08/2018 5:28:46 PM</TimeStamp>&nbsp; &nbsp; <Top_1000> no matches found</Top_1000>&nbsp; </disappeared></Subject></Source><Source id="2"><Subject id="18">&nbsp; <SubjectType>Faces</SubjectType>&nbsp; <FaceConfidence>101</FaceConfidence>&nbsp; <appeared>&nbsp; &nbsp; 02/08/2018 6:28:43 PM&nbsp; &nbsp; <FrameIndex>1034</FrameIndex>&nbsp; &nbsp; <Rectangle top="210" left="169" width="63" height="84"/>&nbsp; </appeared>&nbsp; <track>&nbsp; &nbsp; 02/08/2018 6:28:44 PM&nbsp; &nbsp; <FrameIndex>1051</FrameIndex>&nbsp; &nbsp; <Rectangle top="210" left="134" width="70" height="94"/>&nbsp; &nbsp; <Details>&nbsp; &nbsp; &nbsp; <FrameIndex>1051</FrameIndex>&nbsp; &nbsp; &nbsp; <Status>Ok</Status>&nbsp; &nbsp; &nbsp; <Eyes>&nbsp; &nbsp; &nbsp; &nbsp; <location x="260" y="169"/>&nbsp; &nbsp; &nbsp; &nbsp; <location x="229" y="169"/>&nbsp; &nbsp; &nbsp; </Eyes>&nbsp; &nbsp; &nbsp; <Rectangle top="210" left="134" width="70" height="94"/>&nbsp; &nbsp; </Details>&nbsp; </track>&nbsp; <disappeared>&nbsp; &nbsp; <TimeStamp>02/08/2018 6:28:46 PM</TimeStamp>&nbsp; &nbsp; <Top_1000> no matches found</Top_1000>&nbsp; </disappeared>&nbsp; </Subject>&nbsp; </Source></Source>

Answer 3

你的 xml 很糟糕。我在下面修复了它：<?xml version="1.0" encoding="utf-8" ?><Source>&nbsp; <SubjectType>Faces</SubjectType>&nbsp; <FaceConfidence>100</FaceConfidence>&nbsp; <appeared>&nbsp; &nbsp; 02/08/2018 5:28:43 PM>&nbsp; &nbsp; <FrameIndex>1033</FrameIndex>&nbsp; &nbsp; <Rectangle top="210" left="169" width="63" height="84"/>&nbsp; </appeared>&nbsp; <track>&nbsp; &nbsp; 02/08/2018 5:28:44 PM>&nbsp; &nbsp; <FrameIndex>1050</FrameIndex>&nbsp; &nbsp; <Rectangle top="210" left="134" width="70" height="94"/>&nbsp; &nbsp; <Details>&nbsp; &nbsp; &nbsp; <FrameIndex>1050</FrameIndex>&nbsp; &nbsp; &nbsp; <Status>Ok</Status>&nbsp; &nbsp; &nbsp; <Eyes>&nbsp; &nbsp; &nbsp; &nbsp; <location x="260" y="169"/>&nbsp; &nbsp; &nbsp; &nbsp; <location x="229" y="169"/>&nbsp; &nbsp; &nbsp; </Eyes>&nbsp; &nbsp; &nbsp; <Rectangle top="210" left="134" width="70" height="94"/>&nbsp; &nbsp; </Details>&nbsp; </track>&nbsp; <disappeared>&nbsp; &nbsp; <TimeStamp>02/08/2018 5:28:46 PM</TimeStamp>&nbsp; &nbsp; <Top_1000> no matches found</Top_1000>&nbsp; </disappeared></Source>