DataSet.WriteXml

  • Thread starter Thread starter hope
  • Start date Start date
H

hope

Hi,
I have a stored procedure with a query

SELECT
DR_EmployeeView.emp_id,
DR_EmployeeView.emp_sme_id_1 as [emp_sme_id],
DR_EmployeeView.emp_sme_id_2 as [emp_sme_id]
FROM DR_EmployeeView

I need my xml looks like this (with 2 same elements, emp_sme_id):
<nc_employee>

<emp_id>00000026</emp_id>

<emp_sme_id> abc</emp_sme_id>

<emp_sme_id> def</emp_sme_id>

</nc_employee>



When I use Dataset.WriteXml, it looks like this:


<nc_employee>

<emp_id>00000026</emp_id>



<emp_sme_id> abc</emp_sme_id>

<emp_sme_id1> def</emp_sme_id1>

</nc_employee>

It automatically added 1 to the second element of .emp_sme_id.

Please help. Thanks.
 
I think that the Dataset object prevents you from having more than one
column with the same name. Since the WriteXML function basically saves
the schema (and data) of your dataset to a file, the resulting XML
file will reflect those limitations.

You could always use an XMLWriter or XMLDocument to create an XML file
some scratch, looping through the records in your dataset, and creating
the XML nodes/elements as needed.
 
All column in the datatable must be unique. During the SELECT, any columns
that conflict with an existing column are numbered starting at 1. That is
what you are seeing.

There is no way around it.

Think about it, if you are doing something like:
myDataSet.Tables(0).Rows(0)("emp_sme_id"), which column do you expect to
get? Do you expect "abc" or "def"? That DataTable would have no way of
figuring out which one you wanted.
 
This is one of the more peculiar queries I have seen so far with regards to
column naming. I have no idea what your data model looks like, but my bet
would be that your problem lies there. Rewrite the query to that you have a
single emp_sme_id column (this is what you want in the end, right?) and
everything will be fine.

If I understand the data correcly, the select would look something like:

SELECT
DR_EmployeeView.emp_id,
DR_EmployeeView.emp_sme_id_1 as [emp_sme_id],
FROM DR_EmployeeView
union
SELECT
DR_EmployeeView.emp_id,
DR_EmployeeView.emp_sme_id_2 as [emp_sme_id]
FROM DR_EmployeeView


On the other hand you could always use an XSLT on the utput xml and form it
the way you want it... but I generally recommend to solve the problemat the
point of origin, not the consequences.

Regards,

Ziga Jakhel
MCSD.NET
 
Back
Top