How to convert many-to-one XML data to DataSet?
- by TruMan1
I have an XML document that has a collection of objects. Each object has a key/value pair of label and value. I am trying to convert this into a DataSet, but when I do ds.ReadXml(xmlFile), then it creates two columns: label and value.
What I would like is to have a column for each "label" and the value to be part of the row. here is my sample of the XML:
<responses>
<response>
<properties id="1" Form="Account Request" Date="Tuesday, March 16, 2010 5:04:26 PM" Confirmation="True" />
<fields>
<field>
<label>Name</label>
<value>John</value>
</field>
<field>
<label>Email</label>
<value>[email protected]</value>
</field>
<field>
<label>Website</label>
<value>http://domain1.com</value>
</field>
<field>
<label>Phone</label>
<value>999-999-9999</value>
</field>
<field>
<label>Place of Birth</label>
<value>Earth</value>
</field>
<field>
<label>Misc</label>
<value>Misc</value>
</field>
<field>
<label>Comments</label>
<value />
</field>
<field>
<label>Agree to Terms?</label>
<value>True</value>
</field>
</fields>
</response>
<response>
<properties id="2" Form="Account Request" Date="Tuesday, March 17, 2010 5:04:26 PM" Confirmation="True" />
<fields>
<field>
<label>Name</label>
<value>John2</value>
</field>
<field>
<label>Email</label>
<value>[email protected]</value>
</field>
<field>
<label>Website</label>
<value>http://domain2.com</value>
</field>
<field>
<label>Phone</label>
<value>999-999-9999</value>
</field>
<field>
<label>Place of Birth</label>
<value>Earth</value>
</field>
<field>
<label>Misc</label>
<value>Misc</value>
</field>
<field>
<label>Comments</label>
<value />
</field>
<field>
<label>Agree to Terms?</label>
<value>True</value>
</field>
</fields>
</response>
<response>
<properties id="3" Form="Account Request" Date="Tuesday, March 18, 2010 5:04:26 PM" Confirmation="True" />
<fields>
<field>
<label>Name</label>
<value>John3</value>
</field>
<field>
<label>Email</label>
<value>[email protected]</value>
</field>
<field>
<label>Website</label>
<value>http://domain3.com</value>
</field>
<field>
<label>Phone</label>
<value>999-999-9999</value>
</field>
<field>
<label>Place of Birth</label>
<value>Earth</value>
</field>
<field>
<label>Misc</label>
<value>Misc</value>
</field>
<field>
<label>Comments</label>
<value />
</field>
<field>
<label>Agree to Terms?</label>
<value>True</value>
</field>
</fields>
</response>
<response>
<properties id="4" Form="Account Request" Date="Tuesday, March 19, 2010 5:04:26 PM" Confirmation="True" />
<fields>
<field>
<label>Name</label>
<value>John</value>
</field>
<field>
<label>Email</label>
<value>[email protected]</value>
</field>
<field>
<label>Website</label>
<value>http://domain4.com</value>
</field>
<field>
<label>Phone</label>
<value>999-999-9999</value>
</field>
<field>
<label>Place of Birth</label>
<value>Earth</value>
</field>
<field>
<label>Misc</label>
<value>Misc</value>
</field>
<field>
<label>Comments</label>
<value />
</field>
<field>
<label>Agree to Terms?</label>
<value>True</value>
</field>
</fields>
</response>
</responses>
How would I convert this to a DataSet so that I can load it into a gridview with the columns: Name, Email, Website, Phone, Place of Birth, Misc, Comments, and Agree to Terms?
Then row 1 would be:
John, [email protected], http://domain1.com, 999-999-9999, Earth, Misc, , True
How can I do this with the XML provided?