I have following output of xml response. How can I convert this xml data to a data table in c#.
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Header>
<correlationId xmlns:ns4="http://www.crifbuergel.de/bone_v4" xmlns:ns3="http://www.crifbuergel.de/bone_v3" xmlns:ns2="http://www.crifbuergel.de/bone_v2" xmlns="http://www.crifbuergel.de/bone_v1">002</correlationId>
</soap:Header>
<soap:Body>
<searchResponse xmlns="http://www.crifbuergel.de/bone_v1" xmlns:ns2="http://www.crifbuergel.de/bone_v2" xmlns:ns3="http://www.crifbuergel.de/bone_v3" xmlns:ns4="http://www.crifbuergel.de/bone_v4">
<candidate>
<identifier>
<type>VAT</type>
<value>DE666814999</value>
</identifier>
<identifier>
<type>ONR</type>
<value>26120001</value>
</identifier>
<identifier>
<type>TAX_ID</type>
<value>5333044444444</value>
</identifier>
<registry>
<type>HRB</type>
<number>1234</number>
<city>Hamburg</city>
</registry>
<hitType>COMPANY</hitType>
<name>Möbelhaus Peter Neumann GmbH</name>
<location>
<street>Seewartenstr.</street>
<house>9</house>
<city>Hamburg</city>
<zip>20459</zip>
<country>
<code>DEU</code>
<text>Deutschland</text>
</country>
</location>
<unitType>HEADOFFICE</unitType>
<status>active</status>
<similarity>100</similarity>
</candidate>
<candidate>
<identifier>
<type>VAT</type>
<value>DE123456789</value>
</identifier>
<identifier>
<type>ONR</type>
<value>11498001</value>
</identifier>
<identifier>
<type>TAX_ID</type>
<value>5123012341234</value>
</identifier>
<registry>
<type>HRB</type>
<number>1268</number>
<city>Aachen</city>
</registry>
<hitType>COMPANY</hitType>
<name>Möbelhaus Peter Neumann AG</name>
<location>
<street>Adalbertsteinweg</street>
<house>26</house>
<city>Aachen</city>
<zip>52070</zip>
<country>
<code>DEU</code>
<text>Deutschland</text>
</country>
</location>
<unitType>HEADOFFICE</unitType>
<status>active</status>
<similarity>84</similarity>
</candidate>
<candidate>
<identifier>
<type>ONR</type>
<value>31621001</value>
</identifier>
<hitType>COMPANY</hitType>
<name>Peter Neumann</name>
<location>
<street>Friedensallee</street>
<house>254</house>
<city>Hamburg</city>
<zip>22763</zip>
<country>
<code>DEU</code>
<text>Deutschland</text>
</country>
</location>
<unitType>BRANCH</unitType>
<status>active</status>
<similarity>84</similarity>
</candidate>
<candidate>
<identifier>
<type>ONR</type>
<value>42076150</value>
</identifier>
<hitType>COMPANY</hitType>
<name>Möbelhaus Peter Neumann GmbH</name>
<location>
<street>Waldstr.</street>
<house>5</house>
<city>Pinneberg</city>
<zip>25421</zip>
<country>
<code>DEU</code>
<text>Deutschland</text>
</country>
</location>
<unitType>BRANCH</unitType>
<status>active</status>
<similarity>84</similarity>
</candidate>
<candidate>
<identifier>
<type>VAT</type>
<value>DE123437641</value>
</identifier>
<identifier>
<type>ONR</type>
<value>19276000</value>
</identifier>
<identifier>
<type>TAX_ID</type>
<value>5333044444444</value>
</identifier>
<registry>
<type>HRB</type>
<number>1268</number>
</registry>
<hitType>COMPANY</hitType>
<name>Möbelhaus Peter Neumann - Negativ GmbH - Basisdaten Nachtragstest</name>
<location>
<street>Friedlandstr.</street>
<house>2</house>
<city>Aachen</city>
<zip>52064</zip>
<country>
<code>DEU</code>
<text>Deutschland</text>
</country>
</location>
<unitType>HEADOFFICE</unitType>
<status>active</status>
<similarity>83</similarity>
</candidate>
<candidate>
<identifier>
<type>ONR</type>
<value>12425003</value>
</identifier>
<hitType>COMPANY</hitType>
<name>Peter Neumann</name>
<location>
<street>Holzgraben</street>
<house>24</house>
<city>Aachen</city>
<zip>52062</zip>
<country>
<code>DEU</code>
<text>Deutschland</text>
</country>
</location>
<unitType>HEADOFFICE</unitType>
<status>active</status>
<similarity>81</similarity>
</candidate>
<candidate>
<identifier>
<type>ONR</type>
<value>42046616</value>
</identifier>
<hitType>COMPANY</hitType>
<name>Neumann GmbH</name>
<location>
<street>Paul-Ehrlich-Str.</street>
<house>5a</house>
<city>Dietzenbach</city>
<zip>63128</zip>
<country>
<code>DEU</code>
<text>Deutschland</text>
</country>
</location>
<unitType>HEADOFFICE</unitType>
<status>active</status>
<similarity>81</similarity>
</candidate>
<candidate>
<identifier>
<type>ONR</type>
<value>42075283</value>
</identifier>
<hitType>COMPANY</hitType>
<name>Peter Neumann</name>
<location>
<street>Eisenstockstr.</street>
<house>12</house>
<city>Ettlingen</city>
<zip>76275</zip>
<country>
<code>DEU</code>
<text>Deutschland</text>
</country>
</location>
<unitType>HEADOFFICE</unitType>
<status>active</status>
<similarity>81</similarity>
</candidate>
<candidate>
<identifier>
<type>ONR</type>
<value>25189001</value>
</identifier>
<registry>
<type>HRB</type>
<number>1269</number>
</registry>
<hitType>COMPANY</hitType>
<name>Max Mustermann GmbH</name>
<location>
<street>Gasstr.</street>
<house>18</house>
<city>Hamburg</city>
<zip>22761</zip>
<country>
<code>DEU</code>
<text>Deutschland</text>
</country>
</location>
<unitType>HEADOFFICE</unitType>
<status>active</status>
<similarity>80</similarity>
</candidate>
</searchResponse>
</soap:Body>
</soap:Envelope>
I have tried like below, But DataTable contains no row:
string xmlData = txtResponse.Text;
XElement x = XElement.Parse(xmlData);
DataTable dt = new DataTable();
XElement setup = (from p in x.Descendants() select p).First();
foreach (XElement xe in setup.Descendants()) // build your DataTable
dt.Columns.Add(new DataColumn(xe.Name.ToString(), typeof(string))); // add columns to your dt
var all = from p in x.Descendants(setup.Name.ToString()) select p;
foreach (XElement xe in all)
{
DataRow dr = dt.NewRow();
foreach (XElement xe2 in xe.Descendants())
dr[xe2.Name.ToString()] = xe2.Value; //add in the values
dt.Rows.Add(dr);
}
return dt;
you have to flatten your data
using Newtonsoft.Json;
string json = JsonConvert.SerializeXNode(x);
JArray jArr = JObject.Parse(json).SelectToken("soap:Envelope.soap:Body.searchResponse.candidate");
// converting JArray to DataTable
var jArrFlat = new JArray();
foreach (JObject jObj in jArr)
{
var jo = new JObject();
foreach (var prop in jObj.Properties())
{
if (prop.Value.Type == JTokenType.Array)
for (var i = 0; i < prop.Value.Count(); i++)
{
JObject jO = (JObject)prop.Value[i];
foreach (var p in jO.Properties())
jo.Add(new JProperty(prop.Name + i.ToString() + "-" + p.Name, p.Value));
}
else if (prop.Value.Type == JTokenType.Object)
{
if (prop.Name != "identifier")
foreach (var p in ((JObject)prop.Value).Properties())
{
if (p.Value.Type != JTokenType.Object)
jo.Add(new JProperty(prop.Name + "-" + p.Name, p.Value));
else
foreach (var pp in ((JObject)p.Value).Properties())
jo.Add(new JProperty(prop.Name + "-" + p.Name + "-" + pp.Name, pp.Value));
}
else
foreach (var p in ((JObject)prop.Value).Properties())
jo.Add(new JProperty(prop.Name + "0" + "-" + p.Name, p.Value));
}
else jo.Add(new JProperty(prop.Name, prop.Value));
}
jArrFlat.Add(jo);
}
DataTable dt = jArrFlat.ToObject<DataTable>();
or it is much easier to convert it to a c# List
List<Data> identifiers = jArr.ToObject<List<Data>>();
public class Data
{
public List<Identifier> identifiers { get; set; }
public Registry registry { get; set; }
public string hitType { get; set; }
public string name { get; set; }
public Location location { get; set; }
public string unitType { get; set; }
public string status { get; set; }
public string similarity { get; set; }
public Data(JToken identifier)
{
if (identifier.Type == JTokenType.Array) identifiers = identifier.ToObject<List<Identifier>>();
else identifiers = new List<Identifier> { identifier.ToObject<Identifier>() };
}
}
public class Identifier
{
public string type { get; set; }
public string value { get; set; }
}
public class Country
{
public string code { get; set; }
public string text { get; set; }
}
public class Location
{
public string street { get; set; }
public string house { get; set; }
public string city { get; set; }
public string zip { get; set; }
public Country country { get; set; }
}
public class Registry
{
public string type { get; set; }
public string number { get; set; }
public string city { get; set; }
}