@gaurav - If u look at the table, u will come to know that there is a heiararchy maintained. The ParentId specifies the parent of element. As u will see the XML ouput is maintaining the hierarchy.
Hope that will help u understand what i am requesting .
Thanks.
As far as I see, it won't be so easy to do this "nested xml outputting"，you have to do with that by first filling into DataTable，and then do loop to generate xml with the help of XDocument：
Here's a small sample for you:
publicclassMainTest
{
staticvoid Main(string[] args)
{
DataTable dt = newDataTable();
dt.Columns.Add("Id",typeof(int));
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("PId", typeof(int));
dt.Rows.Add(1, "IND", 0);
dt.Rows.Add(2, "PHARM", 1);
dt.Rows.Add(3, "FIN", 0);
dt.Rows.Add(4, "CFIN", 3);
dt.Rows.Add(5, "CMAR", 4);
dt.Rows.Add(6, "Test", 4);
var result = from item in dt.AsEnumerable()
join sitem in dt.AsEnumerable() on item["Id"].ToString() equals sitem["PId"].ToString() into temp
selectnew
{
Name = item["Name"].ToString(),
Children = temp
};
XDocument doc = XDocument.Parse("<Root></Root>");
XElement root = doc.Root;
foreach (var item in result)
{
XElement ele = newXElement(item.Name);
if (item.Children != null && item.Children.Count() > 0)
{
foreach (var sitem in item.Children)
{
ele.Add(newXElement(sitem["Name"].ToString()));
}
}
root.Add(ele);
}
root.Elements().Where(e => e.Elements() == null || e.Elements().Count() == 0).Remove();
System.Console.WriteLine(root);
}
}
Sir, but it woudn't be possible, because the real database table contains more 6000 rows.
I would be greatful if u can provide me with the answer by considering such a database.
Thanks for your reply.
PGaggar
Member
22 Points
19 Posts
Query on XML Path
Nov 22, 2012 04:34 AM|LINK
I have a table like this (Actually it contains more 6000 records)
DDL
<IND>dhol.gaurav
Contributor
3998 Points
725 Posts
Re: Query on XML Path
Nov 22, 2012 08:08 AM|LINK
Hello,
I am not able to find any relation Between your data and xml format, can you tell me the what type of relation between data and excle format
check follwing query it littal some thing about xml query
Select A.* From [tblIndustryCodes] A INNER JOIN [tblIndustryCodes] B ON A.IdIndustry = B.ParentId FOR XML path('Industry'), root('IND'), typelet me know if any query
Gaurav Dhol
Skype ID : dhol.gaurav
If My Post contains helped you, Please Mark as Answer
PGaggar
Member
22 Points
19 Posts
Re: Query on XML Path
Nov 22, 2012 08:56 AM|LINK
@gaurav - If u look at the table, u will come to know that there is a heiararchy maintained. The ParentId specifies the parent of element. As u will see the XML ouput is maintaining the hierarchy.
Hope that will help u understand what i am requesting .
Thanks.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Query on XML Path
Nov 23, 2012 01:59 AM|LINK
Hello,
As far as I see, it won't be so easy to do this "nested xml outputting"，you have to do with that by first filling into DataTable，and then do loop to generate xml with the help of XDocument：
Here's a small sample for you:
PGaggar
Member
22 Points
19 Posts
Re: Query on XML Path
Nov 23, 2012 05:43 AM|LINK
Sir, but it woudn't be possible, because the real database table contains more 6000 rows.
I would be greatful if u can provide me with the answer by considering such a database.
Thanks for your reply.
RameshRajend...
Star
7983 Points
2099 Posts
Re: Query on XML Path
Nov 23, 2012 05:46 AM|LINK
i have one sample code.u can check
select *,(select * from #orderitem where id=o.id for XML path ('item'),root('items'),type) from #order o for xml path('order'),ROOT('orders')PGaggar
Member
22 Points
19 Posts
Re: Query on XML Path
Nov 23, 2012 07:41 AM|LINK
@Ramesh
i want to replace the tagname in XML but a value which is placed b/w them. (Here with the IndustryCode)
Thanks for your reply.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Query on XML Path
Nov 23, 2012 07:54 AM|LINK
Hi again,
As far as I see, It's hard to deal with that by XML Output in SQL management Studio——SQL cannot do everything;)