dataset to SQL column of XML data type

Question

0.00/5 (No votes)

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

<?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 15-Jan-13 23:21pm

sarahmccarthy

Add a Solution

1 solution

Add a Solution

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

josh-jw · Accepted Answer · 2013-01-15T23:35:00

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)

Posted 15-Jan-13 23:35pm

josh-jw

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

dataset to SQL column of XML data type

1 solution

Solution 1

Add your solution here

Preview 0

dataset to SQL column of XML data type

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us