0

Now I am trying to make some service where I can read the xml file and parse it into sql server, I am already read and see many tutorial how to parse xml into sql server use c# but still I can't take the data.

<?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>

That's the xml file format and here is my attempt: 

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

The error console always appears in the xml file, any help, tricks, or hint I would really appreciate.

Improve this question
7
  • Look into Loading a DataSet from XML ... it will make things a lot easier. Commented Aug 15, 2018 at 9:11
  • What do the SQL table(s) look like? I will work on the xml error. Should have answer soon. Commented Aug 15, 2018 at 9:16
  • Thanks filburt i will try that, and for table format i am just use nvarchar with field source, appeared, disappeared, and top.. Commented Aug 15, 2018 at 9:22
  • Where do you get this xml it's terrible formatted? Commented Aug 15, 2018 at 9:22
  • 1
    @DwiRahmanIsmail You don't have any control of this other application because if you do you should output valid xml? Commented Aug 15, 2018 at 9:32

3 Answers 3

Reset to default
2

When I launch your code, I immediately get an exception at

 doc.Load("test.xml");

line. The exception message is pretty clear:

An unhandled exception of type 'System.Xml.XmlException' occurred in System.Xml.dll
Additional information: Name cannot begin with the '1' character, hexadecimal value 0x31. Line 2, position 9.

Apparently, it does not like XML tags named like "Source 1 - Subject 17", "appeared at 02/08/2018 5:28:43 PM" etc.

Update:

You can't use .Net XML-manipulation stuff since your file is not XML (it claims to be XML with its header, but it kinda lies). If you still need to put the contents of this file into your DB, you have to either write a custom parsing routine to read that custom data format or, if possible, contact the people who produce this "XML" for you and convince them to change their data format to real XML. It might be the easiest way if you are working in the same organization etc.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

I am pretty sure this is an invalid xml file
Yes actually it's output from program so you're right it doesn't look like xml usually format, and i am must make some service to take data from this output into sql server
Updated the answer to address your question.
Thanks, maybe easier to talk the programmer who make the program. Thanks for your help..
1

Your xml is awful. I fixed it below :

<?xml version="1.0" encoding="utf-8" ?>
<Source>
  <SubjectType>Faces</SubjectType>
  <FaceConfidence>100</FaceConfidence>
  <appeared>
    02/08/2018 5:28:43 PM>
    <FrameIndex>1033</FrameIndex>
    <Rectangle top="210" left="169" width="63" height="84"/>
  </appeared>
  <track>
    02/08/2018 5:28:44 PM>
    <FrameIndex>1050</FrameIndex>
    <Rectangle top="210" left="134" width="70" height="94"/>
    <Details>
      <FrameIndex>1050</FrameIndex>
      <Status>Ok</Status>
      <Eyes>
        <location x="260" y="169"/>
        <location x="229" y="169"/>
      </Eyes>
      <Rectangle top="210" left="134" width="70" height="94"/>
    </Details>
  </track>
  <disappeared>
    <TimeStamp>02/08/2018 5:28:46 PM</TimeStamp>
    <Top_1000> no matches found</Top_1000>
  </disappeared>
</Source>
Improve this answer

1 Comment

Thanks for your help, i am gonna save this format. i am really appreciate..
1

I'm not shore 100% about jdweng answer, but his idea point me that actually there should be multiple root elements. So my idea that each source has it own source and subject id. 

    <?xml version="1.0" encoding="utf-8" ?>
<Source>
<Source id="1">
<Subject id="17">
  <SubjectType>Faces</SubjectType>
  <FaceConfidence>100</FaceConfidence>
  <appeared>
    02/08/2018 5:28:43 PM
    <FrameIndex>1033</FrameIndex>
    <Rectangle top="210" left="169" width="63" height="84"/>
  </appeared>
  <track>
    02/08/2018 5:28:44 PM
    <FrameIndex>1050</FrameIndex>
    <Rectangle top="210" left="134" width="70" height="94"/>
    <Details>
      <FrameIndex>1050</FrameIndex>
      <Status>Ok</Status>
      <Eyes>
        <location x="260" y="169"/>
        <location x="229" y="169"/>
      </Eyes>
      <Rectangle top="210" left="134" width="70" height="94"/>
    </Details>
  </track>
  <disappeared>
    <TimeStamp>02/08/2018 5:28:46 PM</TimeStamp>
    <Top_1000> no matches found</Top_1000>
  </disappeared>
</Subject>
</Source>

<Source id="2">
<Subject id="18">
  <SubjectType>Faces</SubjectType>
  <FaceConfidence>101</FaceConfidence>
  <appeared>
    02/08/2018 6:28:43 PM
    <FrameIndex>1034</FrameIndex>
    <Rectangle top="210" left="169" width="63" height="84"/>
  </appeared>
  <track>
    02/08/2018 6:28:44 PM
    <FrameIndex>1051</FrameIndex>
    <Rectangle top="210" left="134" width="70" height="94"/>
    <Details>
      <FrameIndex>1051</FrameIndex>
      <Status>Ok</Status>
      <Eyes>
        <location x="260" y="169"/>
        <location x="229" y="169"/>
      </Eyes>
      <Rectangle top="210" left="134" width="70" height="94"/>
    </Details>
  </track>
  <disappeared>
    <TimeStamp>02/08/2018 6:28:46 PM</TimeStamp>
    <Top_1000> no matches found</Top_1000>
  </disappeared>
  </Subject>
  </Source>
</Source>
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.