Web API - Convert XML output to datatable and display data

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.

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.

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

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

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

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

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

foreach (var rowItem in root.Results.RowList)
{
foreach (var coloumItem in rowItem.ColoumnList)
{
}
}

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 -

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

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

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.

Dear All,

Please help me out with this...

Thanks a ton to all in advance..

It is really important and urgent for me...

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

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

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">DesktopColumn>
<Column Index="1" Readable="1">LenovoColumn>
<Column Index="2" Readable="1">M73Column>
<Column Index="3" Readable="1">DT0040142Column>
<Column Index="4" Readable="1">SPC08WLKSColumn>
<Column Index="5" Readable="1">Deepak.KamaleColumn>
<Column Index="6" Readable="1" />
<Column Index="7" Readable="1">CPU-LENOVO-M73-4 GB DDR3-500 GB SATAColumn>
Row>
<Row Index="1">
<Column Index="0" Readable="1">LaptopColumn>
<Column Index="1" Readable="1">HPColumn>
<Column Index="2" Readable="1">840 G3Column>
<Column Index="3" Readable="1">LT0074561Column>
<Column Index="4" Readable="1">5CG71336GFColumn>
<Column Index="5" Readable="1">Deepak.KamaleColumn>
<Column Index="6" Readable="1" />
<Column Index="7" Readable="1">Laptop-HP-840 G3-8 GB DDR3 RAM-500 GColumn>
Row>
<Row Index="2">
<Column Index="0" Readable="1">LaptopColumn>
<Column Index="1" Readable="1">HPColumn>
<Column Index="2" Readable="1">ProBook 430 G1Column>
<Column Index="3" Readable="1">LT0014977Column>
<Column Index="4" Readable="1">2CE41225N1Column>
<Column Index="5" Readable="1">Deepak.KamaleColumn>
<Column Index="6" Readable="1" />
<Column Index="7" Readable="1">Laptop-HP-ProBook 430 G1-4 GB DDR3-5Column>
Row>
<Row Index="3">
<Column Index="0" Readable="1">LaptopColumn>
<Column Index="1" Readable="1">LenovoColumn>
<Column Index="2" Readable="1">L450Column>
<Column Index="3" Readable="1">LT0032927Column>
<Column Index="4" Readable="1">PF0BVRKMColumn>
<Column Index="5" Readable="1">Deepak.KamaleColumn>
<Column Index="6" Readable="1" />
<Column Index="7" Readable="1">Laptop-LENOVO-L450-4 GB DDR3-500 GBColumn>
Row>
<Row Index="4">
<Column Index="0" Readable="1">LaptopColumn>
<Column Index="1" Readable="1">HPColumn>
<Column Index="2" Readable="1">HP 440 G5Column>
<Column Index="3" Readable="1">LT0105685Column>
<Column Index="4" Readable="1">5CD8310M65Column>
<Column Index="5" Readable="1">Deepak.KamaleColumn>
<Column Index="6" Readable="1">Ajit.BambrasColumn>
<Column Index="7" Readable="1">Laptop-HP-440 G5-8 GB-500 GB-i3Column>
Row>
<Row Index="5">
<Column Index="0" Readable="1">LaptopColumn>
<Column Index="1" Readable="1">HPColumn>
<Column Index="2" Readable="1">HP 440 G5Column>
<Column Index="3" Readable="1">LT0105615Column>
<Column Index="4" Readable="1">5CD8310M7QColumn>
<Column Index="5" Readable="1">Deepak.KamaleColumn>
<Column Index="6" Readable="1" />
<Column Index="7" Readable="1">Laptop-HP-440 G5-8 GB-500 GB-i3Column>
Row>
<Row Index="6">
<Column Index="0" Readable="1">LaptopColumn>
<Column Index="1" Readable="1">HPColumn>
<Column Index="2" Readable="1">HP 440 G5Column>
<Column Index="3" Readable="1">LT0105619Column>
<Column Index="4" Readable="1">5CD8310M8RColumn>
<Column Index="5" Readable="1">Deepak.KamaleColumn>
<Column Index="6" Readable="1" />
<Column Index="7" Readable="1">Laptop-HP-440 G5-8 GB-500 GB-i3Column>
Row>
Result>
Query>

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.

15 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ashutosh Gupta6y ago
Accepted 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.
+1
Riddhi Valecha6y ago
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..
0
Jignesh Kumar6y ago
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();
0
Jaish Mathews6y ago
Everyone please respect each others time. Forums are to help, not to do others job
0
Ashutosh Gupta6y ago
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);
            }
0
Riddhi Valecha6y ago
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
0
Ashutosh Gupta6y ago
1. foreach (var rowItem in root.Results.RowList)
2. {
3. foreach (var coloumItem in rowItem.ColoumnList)
4. {
5. }
6. }
Create your datatable accordingly from here.