数据集到XML数据类型的SQL列 [英] dataset to SQL column of XML data type

问题描述

我正在寻找一种方法将

a数据集写入xql数据类型的sql表中的列？





目前我将数据集写入xml文件，然后将xml文件映射到表列（其中6个）。

生成的xml文件在映射到数据库之前显示给用户，以允许他们预览他们的选择（它是一个正在进行的投票系统）

这是有效的很好，我可以查询表格，然后检查投票结果等



我只是在寻找另一个选项，这可能会更高效

由数据集生成的xml文件非常小，下面的示例如下。

每个元素当前写入各自的列是/否，pollposition1等

i希望表只有selector_id和xml数据类型的结果列

所以我想在我的表中只有两列而不是现在的7列。

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

推荐答案

你可以通过搜索节点从xml中选择

在你的例子中你可以选择Election_ID值，如

SELECT ls.value（''@ Election_ID''，''VARCHAR（25）''）FROM @ HoldFileXML.nodes（''// ResultsDS / Results''） AS L（ls）

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)

这篇关于数据集到XML数据类型的SQL列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！