0

I have the following xml document that is from a 3 party and saved in a database. I am trying to figure out the best way take the document and load classes so I can manipulate the data and then save it to the database in tables that closely representing the class.

<Payroll xmlns:dtv="http://www.datavantagecorp.com/xstore/"><dtv:Payroll>
<dtv:StartDate>2015-02-08</dtv:StartDate>
<dtv:EndDate>2015-02-14</dtv:EndDate>
<dtv:PostedDate>2015-02-16</dtv:PostedDate>
<dtv:Employee EmployeeId="111122">
<dtv:EmployeePayStatus></dtv:EmployeePayStatus>
<dtv:PayrollStoreNumber>8009</dtv:PayrollStoreNumber>
<dtv:ReviewedDate></dtv:ReviewedDate>
<dtv:PayrollCategory Category="OT">
<dtv:PayrollDate>2015-02-14</dtv:PayrollDate>
<dtv:Hours>0.53</dtv:Hours>
<dtv:PayrollDate>2015-02-12</dtv:PayrollDate>
<dtv:Hours>0.77</dtv:Hours>
<dtv:PayrollDate>2015-02-11</dtv:PayrollDate>
<dtv:Hours>0.13</dtv:Hours>
<dtv:PayrollDate>2015-02-09</dtv:PayrollDate>
<dtv:Hours>0.12</dtv:Hours>
</dtv:PayrollCategory>
<dtv:PayrollCategory Category="DT">
</dtv:PayrollCategory>
<dtv:PayrollCategory Category="REGULAR">
<dtv:PayrollDate>2015-02-09</dtv:PayrollDate>
<dtv:Hours>8.00</dtv:Hours>
<dtv:WorkCode code="888">
<dtv:TotalHours>8.12</dtv:TotalHours>
</dtv:WorkCode>
<dtv:PayrollDate>2015-02-10</dtv:PayrollDate>
<dtv:Hours>6.03</dtv:Hours>
<dtv:WorkCode code="888">
<dtv:TotalHours>6.03</dtv:TotalHours>
</dtv:WorkCode>
<dtv:PayrollDate>2015-02-11</dtv:PayrollDate>
<dtv:Hours>8.00</dtv:Hours>
<dtv:WorkCode code="888">
<dtv:TotalHours>8.13</dtv:TotalHours>
</dtv:WorkCode>
<dtv:PayrollDate>2015-02-12</dtv:PayrollDate>
<dtv:Hours>8.00</dtv:Hours>
<dtv:WorkCode code="888">
<dtv:TotalHours>8.77</dtv:TotalHours>
</dtv:WorkCode>
<dtv:PayrollDate>2015-02-13</dtv:PayrollDate>
<dtv:Hours>6.81</dtv:Hours>
<dtv:WorkCode code="888">
<dtv:TotalHours>6.81</dtv:TotalHours>
</dtv:WorkCode>
<dtv:PayrollDate>2015-02-14</dtv:PayrollDate>
<dtv:Hours>3.16</dtv:Hours>
<dtv:WorkCode code="888">
<dtv:TotalHours>3.69</dtv:TotalHours>
</dtv:WorkCode>
</dtv:PayrollCategory>
<dtv:PayrollCategory Category="SICK">
</dtv:PayrollCategory>
<dtv:PayrollCategory Category="VACATION">
</dtv:PayrollCategory>
<dtv:PayrollCategory Category="HOLIDAY">
</dtv:PayrollCategory>
<dtv:PayrollCategory Category="RT">
</dtv:PayrollCategory>
<dtv:PayrollCategory Category="EO">
</dtv:PayrollCategory>
<dtv:PayrollCategory Category="RSA">
</dtv:PayrollCategory>
</dtv:Employee>
</dtv:Payroll>
</Payroll>

The XML can have several Employees within a Payroll. I was thinking I would have the following classes:

Payroll Employee PayrollCategory

where for each Payroll, there is a list of employees and each employee has a list of PayrollCategory. I have started heading down the following path but not sure if this is the best way or if there is a better solution.

                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    var doc = new XmlDocument();
                    doc.LoadXml(row[0].ToString());
                }

Any suggestions or links on how to go out handling this would be greatly appreciated.

JohnK
  • 35
  • 8
  • Open your VS. `Edit =>Paste Special=>Paste XML as classes`. Then search for XmlSerializer. – EZI Feb 19 '15 at 21:01
  • Look into [Entity Framework](https://msdn.microsoft.com/en-us/data/ef.aspx). It's an ORM (Object-Relational Mapping) framework, which means it maps objects to a relational database. – BJ Myers Feb 19 '15 at 21:07
  • Entity Framework is not an option for me. It is not a standard for my company. – JohnK Feb 19 '15 at 21:14
  • https://msdn.microsoft.com/en-us/library/fa420a9y(v=vs.110).aspx XML deserialization is standard practice. Please follow the link. – ghostbust555 Feb 19 '15 at 21:00

2 Answers2

0

I would recommend creating classes for your XML elements, that you can serialize/deserialize from the XML document. There's a few questions and answers on how to do that (such as the one found here).

If you have an XSD Schema for your XML Document, you could probably even autogenerate the classes.

Community
  • 1
  • 1
Lars Kristensen
  • 1,410
  • 19
  • 29
-2
foreach (var row in ds.Tables[0].Rows)
{
    var doc = XDocument.Load(new MemoryStream(new UTF8Encoding().GetBytes(row[0])));
}
Kevin
  • 157
  • 3
  • Downvoting without a stated reason is about as useless as tits on a boar hog. XDocument is a class, and if he needs to get at the contained data he can use Linq to easily query it out. – Kevin Feb 19 '15 at 21:28
  • 2
    I downvoted, OP asked for a way to get objects out of the XmlDocument and I didn't think your answer helped. Your comment about using LINQ on the XDocument however does help. I think you should edit your answer to include this - and then I'll remove the downvote :) Still, OP won't get actual classes out of this solution, but it could solve the ultimate goal of mapping XML to database tables. – Lars Kristensen Feb 19 '15 at 21:37
  • 1
    And furthermore - didn't dv - answer containing only code is rather useless. You should explain what happens and why it works. – Willem Van Onsem Feb 20 '15 at 01:13