Data set Table Single Column Write To xml

Question

hi experts,

From a Data set Table named receipts i wanted a column named "ui" of type GUID to be exported as an xml.

above XML i want to pass as a parameter to fetch record from "receiptdetails" tables 

can some one help me how to  do this

Answer 1

Please check the following example. I was convert a single column from DataTable to xml. Modify this as per your business requirement.

DataTable dtRoleFunction = new DataTable("Role_Function ");
dtRoleFunction.Columns.Add("RoleId", typeof(int));
dtRoleFunction.Columns.Add("FunctionId", typeof(int));
dtRoleFunction.Rows.Add(1, 1);
dtRoleFunction.Rows.Add(2, 2);
dtRoleFunction.Rows.Add(3, 3);
dtRoleFunction.Rows.Add(4, 1);
dtRoleFunction.Rows.Add(5, 4);
var xEle = new XElement("FunctionID", from dt in dtRoleFunction.AsEnumerable() select new XElement("FUN",dt.Field<int>("FunctionId").ToString())); 

---

With Thanks and Regards
Sambath Raj.C
click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
Happy Programming!

Answer 2

Thanks Sambath Raj.C,

Really this  was  the short and sweet code, and it wrote a xml for me.

i got the result like below

'<Receiptsui>
  <ui>3501d989-308c-481c-8b2a-f92230d3f9bc</ui>
  <ui>da832de7-b719-43bb-bfd6-e386eb6dbbda</ui>
  <ui>11c26563-acb0-4aac-aabd-ef12022feae6</ui>
  <ui>2e200248-8755-41e9-8be2-922422110443</ui>
  <ui>e77dffa1-5be9-4671-a819-9ec77060b993</ui>
  <ui>c507433b-2d05-4f8d-beb3-12bcc5fc66b2</ui>
</Receiptsui>'

but now there is a problem i want to read xml output as 

DECLARE @xml xml
SET @xml= '<Receiptsui>
  <ui>3501d989-308c-481c-8b2a-f92230d3f9bc</ui>
  <ui>da832de7-b719-43bb-bfd6-e386eb6dbbda</ui>
  <ui>11c26563-acb0-4aac-aabd-ef12022feae6</ui>
  <ui>2e200248-8755-41e9-8be2-922422110443</ui>
  <ui>e77dffa1-5be9-4671-a819-9ec77060b993</ui>
  <ui>c507433b-2d05-4f8d-beb3-12bcc5fc66b2</ui>
</Receiptsui>'
 
SELECT
	T.n.value('@ui[1]', 'uniqueidentifier')  [ui]
FROM
	@xml.nodes('/Receiptsui/ui') T(n)
GO

i am getting null values,

how can i solve this

Answer 3

Wbob Has given the solution.

replacing   '@ui[1]'  with '.' after T.n.values i got the result.

thanks to forum experts like wBob & Sambath Raj.C. they spend so much of time and effort to help people like me.

Answer 4

Dear Sambath Raj.C

var xEle = new XElement("Receiptsui", from dt in ds.Tables["billwithitem"].AsEnumerable() select new XElement("ui", dt.Field<Guid>("ui").ToString()));    

as suggested by you the i could get the result & thanks a lot for the same.

can u please further tell me if i want distinct rows how do i modify the Qury

because i tried following , still it gives all rows without giving me disitinct rows

var xEle = new XElement("Receiptsui", (from dt in ds.Tables["billwithitem"].AsEnumerable() select new XElement("ui", dt.Field<Guid>("ui").ToString())).Distinct());