Click here to Skip to main content
14,335,764 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi, I am looking for a way to perhaps write
a dataset to a column in a sql table that is of xml data type?


At the moment i have the dataset written to an xml file and the xml file is then mapped to the table columns (6 of them).
The xml file generated is shown to the user before it is mapped to the database to allow them to preview their choices (its a voting system im working on)
this works fine and i can query the table then to check voting results etc

Im just looking for another option of doing this maybe a bit more efficient
the xml file generated by the dataset is very small, a sample of this is below.
each element is currently written to their respective columns yes/no, pollposition1 etc
i would like the table to just have election_id and and results column of xml datatype
so i would like to just have two columns in my table as opposed to 7 as i have now.


<?xml version="1.0" standalone="yes"?>
<ResultsDS>
  <Results>
    <Election_ID>eire004</Election_ID>
    <YesNo_Vote />
    <PollPosition_1>David Norris</PollPosition_1>
    <PollPosition_2>Mary Robinson</PollPosition_2>
    <PollPosition_3 />
    <PollPosition_4 />
    <PollPosition_5 />
  </Results>
</ResultsDS>




Any help or suggestions would be much appreciated.
thanks in advance
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

you can select from xml like by searching nodes
here in you example you can select Election_ID value like
SELECT ls.value('@Election_ID', 'VARCHAR(25)') FROM @HoldFileXML.nodes('//ResultsDS/Results') AS L(ls)
   
Comments
sarahmccarthy 16-Jan-13 5:40am
   
Im looking to avoid the middle man so to speak. rather than write xml to a file from the dataset, write directly to the database? I could query it then to preview it for the voter. currently a gridview holds the xml file and shows the voter the preview. Im happy with the way it works currently, because it works. just something that requires fewer resources for the function

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100