Web API - Convert XML output to datatable and display data

Question

Dear Team,

I have got 2 web api and I am getting their output in xml string.

I have to convert this xml string to datatable and display it in gridview.

I am sharing one xml output.

Please guide - How to convert it in datatable.

Ashutosh Gupta · Answer

ridhi, you need to convert your xml into .neypbject then rest of things will be easy for you to hanlde. e.g. [XmlRoot(ElementName = "Query" )] public class QueryRoot { public QueryRoot() { Results = new Result(); } /// /// Gets or sets sessions /// [XmlElement(ElementName = "Result" )] public Result Results { get ; set ; } } public class Result { public Result() { RowList = new List (); } public int Index { get ; set ; } [XmlElement(ElementName = "Row" )] public List RowList { get ; set ; } } public class Rows { public Rows() { ColoumnList = new List (); } [XmlElement(ElementName = "Column" )] public List ColoumnList { get ; set ; } } public class Coloums { public int Index { get ; set ; } public int Readable { get ; set ; } [XmlText] public string Value { get ; set ; } } this is similar structure what you have. now when you have xml string de searlize in this strucure. XmlSerializer serializer = new XmlSerializer( typeof (QueryRoot)); using (TextReader reader = new StringReader( "your xml string data" )) { QueryRoot root = (QueryRoot)serializer.Deserialize(reader); } now, you have root object of type QureryRoot which have all rows with number coloumns. now it is up to you how to you insert into database. e.g. you could write loop on root.Result.Rows and insert each row one by one and take all into one collection which can be inserted in one shot.

Riddhi Valecha · Answer

Dear sir, I again need help in converting XML to .NET Object.. But, this api is of PUT Method - It is for updating record based on ID. Attached file is the XML Output for the API. Please guide..

Jignesh Kumar · Answer

aHi Ridhi, You can convert your Web API XML response to dataset direct and same dataset you can bind with gridview. https://www.c-sharpcorner.com/blogs/convert-xml-to-dataset-in-c-sharp public DataSet ConvertXMLToDataSet( string xmlResponseData) { StringReader stream = null ; XmlTextReader reader = null ; try { DataSet xmlDS = new DataSet(); stream = new StringReader(xmlResponseData); reader = new XmlTextReader(stream); xmlDS.ReadXml(reader); return xmlDS; } finally { } } Gridview1.datasource = ConvertXMLToDataSet(XMLResponseData); Gridview1.Databind();

Jaish Mathews · Answer

Everyone please respect each others time. Forums are to help, not to do others job

Ashutosh Gupta · Answer

you have almost done it, a small task was left. anyways, let me complte it now . int rowindex = 0; foreach (var rowItem in root.Results.RowList) { rowindex = 0; DataRow row = dt.NewRow(); foreach (var coloumItem in rowItem.ColoumnList) { row[rowindex++] = coloumItem.Value.ToString(); } dt.Rows.Add(row); }

Riddhi Valecha · Answer

Dear Ashutosh sir, Yes, Your Answer has helped me out... One last help required from your end - i need to bind the result to datatable and display it in a gridview. I am sharing my method - Please guide for the same - -- public void XmlSerialization() { try { DataTable dt = new DataTable(); dt.Columns.Add("Computer.ComputerType", typeof(string)); dt.Columns.Add("Model.Brand.Name", typeof(string)); dt.Columns.Add("Model.Name", typeof(string)); dt.Columns.Add("Computer.TcpIpHostName", typeof(string)); dt.Columns.Add("Asset.SerialNo", typeof(string)); dt.Columns.Add("User.UserName", typeof(string)); dt.Columns.Add("RIL_Inventoried_User.UserName", typeof(string)); dt.Columns.Add("Asset.Field1", typeof(string)); XmlSerializer serializer = new XmlSerializer(typeof(QueryRoot)); string result = CreateSOAPWebRequest_PendingAssets(); using (TextReader reader = new StringReader(result)) { QueryRoot root = (QueryRoot)serializer.Deserialize(reader); int i = root.Results.RowList.Count; foreach (var rowItem in root.Results.RowList) { foreach (var coloumItem in rowItem.ColoumnList) { DataRow row = dt.NewRow(); row["Computer.ComputerType"] = coloumItem.Value.ToString(); dt.Rows.Add(row); } } } } catch (Exception err) { err.Message.ToString(); } } ----------- THANKS A TON

Ashutosh Gupta · Answer

foreach (var rowItem in root.Results.RowList) { foreach (var coloumItem in rowItem.ColoumnList) { } } Create your datatable accordingly from here.

Riddhi Valecha · Answer

Dear Ashutosh sir, I have implemented your code - Class method. I am sharing the output - I think I am almost here .... But, I am failiing in looping the results. Please guide a little bit - Thanks a million for your help. Output -

Local Coders · Answer

You can use this code(Recommended) MemoryStream objMS = new MemoryStream(); DataTable oDT = new DataTable();//Your DataTable which you want to convert oDT.WriteXml(objMS); objMS.Position = 0; XPathDocument result = new XPathDocument(objMS); This is another way but first ex. is recommended StringWriter objSW = new StringWriter(); DataTable oDt = new DataTable();//Your DataTable which you want to convert oDt.WriteXml(objSW); string result = objSW.ToString();

Riddhi Valecha · Answer

Dear Ashutosh sir, Your Solution - one option is to create class structure similar to your xml and convert first your xml into .net object using XmlSerializer. once you xml is converted into .net object then you can push that objct into your database. ------------------------ Could you please explain with example ?? Else, I have shared my xml output with you, could you please guide on that ??

Ashutosh Gupta · Answer

Hello Ridhi, one option is to create class structure similar to your xml and convert first your xml into .net object using XmlSerializer. once you xml is converted into .net object then you can push that objct into your database.

Riddhi Valecha · Answer

Dear All, Please help me out with this... Thanks a ton to all in advance.. It is really important and urgent for me...

Riddhi Valecha · Answer

Dear Team, In my xml output string - Columns are following - Computer.ComputerType Model.Brand.Name Model.Name Asset.SerialNo User.UserName RIL_Inventoried_User.UserName" Asset.Field1 And Result > Row Index = "0" > --These are the values for each column. PLEASE HELP ME OUT IN MAKING THE DATATABLE FROM THIS XML STRING AND DISPLAY IT ON GRIDVIEW. PLEASE HELP AS IT IS VERY URGENT...

Riddhi Valecha · Answer

Dear sir, From the web api, I am getting the response as xml string. I am sharing that output with you - XML String Output - ------------- xml version= "1.0" encoding= "UTF-8" ?> Query > Schema Content = "amPortfolio" > Column Name = "Computer.ComputerType" Content = "Computer.ComputerType" Type = "String" Index = "0" /> Column Name = "Model.Brand.Name" Content = "Model.Brand.Name" Type = "String" Index = "1" /> Column Name = "Model.Name" Content = "Model.Name" Type = "String" Index = "2" /> Column Name = "Computer.TcpIpHostName" Content = "Computer.TcpIpHostName" Type = "String" Index = "3" /> Column Name = "Asset.SerialNo" Content = "Asset.SerialNo" Type = "String" Index = "4" /> Column Name = "User.UserName" Content = "User.UserName" Type = "String" Index = "5" /> Column Name = "RIL_Inventoried_User.UserName" Content = "RIL_Inventoried_User.UserName" Type = "String" Index = "6" /> Column Name = "Asset.Field1" Content = "Asset.Field1" Type = "String" Index = "7" /> Schema > Result > Row Index = "0" > Column Index = "0" Readable = "1" > Desktop Column > Column Index = "1" Readable = "1" > Lenovo Column > Column Index = "2" Readable = "1" > M73 Column > Column Index = "3" Readable = "1" > DT0040142 Column > Column Index = "4" Readable = "1" > SPC08WLKS Column > Column Index = "5" Readable = "1" > Deepak.Kamale Column > Column Index = "6" Readable = "1" /> Column Index = "7" Readable = "1" > CPU-LENOVO-M73-4 GB DDR3-500 GB SATA Column > Row > Row Index = "1" > Column Index = "0" Readable = "1" > Laptop Column > Column Index = "1" Readable = "1" > HP Column > Column Index = "2" Readable = "1" > 840 G3 Column > Column Index = "3" Readable = "1" > LT0074561 Column > Column Index = "4" Readable = "1" > 5CG71336GF Column > Column Index = "5" Readable = "1" > Deepak.Kamale Column > Column Index = "6" Readable = "1" /> Column Index = "7" Readable = "1" > Laptop-HP-840 G3-8 GB DDR3 RAM-500 G Column > Row > Row Index = "2" > Column Index = "0" Readable = "1" > Laptop Column > Column Index = "1" Readable = "1" > HP Column > Column Index = "2" Readable = "1" > ProBook 430 G1 Column > Column Index = "3" Readable = "1" > LT0014977 Column > Column Index = "4" Readable = "1" > 2CE41225N1 Column > Column Index = "5" Readable = "1" > Deepak.Kamale Column > Column Index = "6" Readable = "1" /> Column Index = "7" Readable = "1" > Laptop-HP-ProBook 430 G1-4 GB DDR3-5 Column > Row > Row Index = "3" > Column Index = "0" Readable = "1" > Laptop Column > Column Index = "1" Readable = "1" > Lenovo Column > Column Index = "2" Readable = "1" > L450 Column > Column Index = "3" Readable = "1" > LT0032927 Column > Column Index = "4" Readable = "1" > PF0BVRKM Column > Column Index = "5" Readable = "1" > Deepak.Kamale Column > Column Index = "6" Readable = "1" /> Column Index = "7" Readable = "1" > Laptop-LENOVO-L450-4 GB DDR3-500 GB Column > Row > Row Index = "4" > Column Index = "0" Readable = "1" > Laptop Column > Column Index = "1" Readable = "1" > HP Column > Column Index = "2" Readable = "1" > HP 440 G5 Column > Column Index = "3" Readable = "1" > LT0105685 Column > Column Index = "4" Readable = "1" > 5CD8310M65 Column > Column Index = "5" Readable = "1" > Deepak.Kamale Column > Column Index = "6" Readable = "1" > Ajit.Bambras Column > Column Index = "7" Readable = "1" > Laptop-HP-440 G5-8 GB-500 GB-i3 Column > Row > Row Index = "5" > Column Index = "0" Readable = "1" > Laptop Column > Column Index = "1" Readable = "1" > HP Column > Column Index = "2" Readable = "1" > HP 440 G5 Column > Column Index = "3" Readable = "1" > LT0105615 Column > Column Index = "4" Readable = "1" > 5CD8310M7Q Column > Column Index = "5" Readable = "1" > Deepak.Kamale Column > Column Index = "6" Readable = "1" /> Column Index = "7" Readable = "1" > Laptop-HP-440 G5-8 GB-500 GB-i3 Column > Row > Row Index = "6" > Column Index = "0" Readable = "1" > Laptop Column > Column Index = "1" Readable = "1" > HP Column > Column Index = "2" Readable = "1" > HP 440 G5 Column > Column Index = "3" Readable = "1" > LT0105619 Column > Column Index = "4" Readable = "1" > 5CD8310M8R Column > Column Index = "5" Readable = "1" > Deepak.Kamale Column > Column Index = "6" Readable = "1" /> Column Index = "7" Readable = "1" > Laptop-HP-440 G5-8 GB-500 GB-i3 Column > Row > Result > Query >

Jaish Mathews · Answer

I hope your XML keeps some standard format and then I would suggest re-usable custom classes, to serialize XML to those classes. To create a re-usable custom class for an XML, create a new class file in Visual Studio and open it. Now copy the XML and got to Visual Studio "Edit" menu -> Paste Special -> paste XML As Classes . This will generate the class structure for that specific XML. This is only one time setup for a specific XML. Now everywhere this same custom class can re-use to represent that specific XML i.e. you can serialize and de-serialize using this custom class.