XQuery XML数据类型SQL [英] XQuery XML Data Type SQL
问题描述
我想知道是否有人可以建议'Xquery'
我在我的表中有一个xml列被调用，在该列中我有如下数据
I was wondering if anyone could advise on 'Xquery'
I have an xml column in my table its called and in that column i have data like as follows
<ResultsDS>
<Results>
<Referendum_You_Voted />
<Your_First_Choice>Micheal D Higgins</Your_First_Choice>
<Your_Second_Choice>Sarah McCarthy</Your_Second_Choice>
<Your_Third_Choice>David Norris</Your_Third_Choice>
<Your_Fourth_Choice>Mary Robinson</Your_Fourth_Choice>
<Your_Fifth_Choice>Barack Obama</Your_Fifth_Choice>
</Results>
</ResultsDS>
表名结果2
xml列名结果_xml
我最终想成为能够计算结果并将其显示给用户。当我将结果存储在常规数据类型中时，我使用正常的SQL查询。
所以基本上就像结果一样读取
最多被选为第一选择：micheal d higgins
最多被选为第二选择：sarah mccarthy等等。
任何帮助或示例都将不胜感激。我自己看了网上但我没有遇到任何能够正确解释查询如何组合的例子。
提前感谢。
Table name results2
xml column name results_xml
I ultimately want to be able to count the results and display them to the user. i have this working using normal sql queries when i have the results stored in regular datatypes.
so essentially id like the results to read
most voted as 1st choice: micheal d higgins
most voted as second choice: sarah mccarthy etc etc.
Any help or examples would be appreciated. Ive looked online myself but ive not come across any examples that give a proper explanation of how the query's are assembled.
thanks in advance.
推荐答案
MSSQL 2005+支持直接查询xml列以及索引它们。
因此，您应该可以使用稍微修改的XML字段标准来运行典型查询：
MSSQL 2005+ supports direct quering xml columns as well as indexing them.
Thus you should be able to run your typical queries with slightly modified criteria for XML fields:
SELECT
CREATE TABLE results_xml (pk INT PRIMARY KEY, FieldSQL XML not null)
insert into results_xml (pk,FieldSQL) values (1,'
<resultsds>
<results>
<referendum_you_voted />
<your_first_choice>Micheal D Higgins</your_first_choice>
<your_second_choice>Sarah McCarthy</your_second_choice>
<your_third_choice>David Norris</your_third_choice>
<your_fourth_choice>Mary Robinson</your_fourth_choice>
<your_fifth_choice>Barack Obama</your_fifth_choice>
</results>
</resultsds>
'
)
select
FieldSQL.value('(/ResultsDS/Results/Your_First_Choice)[1]', 'nvarchar(1000)')
,* from results_xml
where
FieldSQL.value('(/ResultsDS/Results/Your_Fifth_Choice)[1]', 'nvarchar(1000)')='Barack Obama'
语法不准确，因为我现在没有SQL Server运行。
更多信息可以可以在MSDN上找到：
http://msdn.microsoft.com/en-us/library/m s345117（v = sql.90）.aspx [ ^ ]
如果您要求粉碎XMl数据值，可以执行以下操作：
创建表#tempTbl
（
Id int，
Doc XML
）
插入#tempTbl值（1，'< resultsds>
< results>
< referendum_you_voted>
< your_first_choice> Micheal D Higgins
< your_second_choice> Sarah McCarthy
< your_third_choice> David Norris
< your_fourth_choice> Mary Robinson
< your_fifth_choice> Barack Obama
'）
插入#tempTbl值（2，'< resultsds>
< results>
< referendum_you_voted>
< your_first_choice> Barack Obama
< your_second_choice> David Norris
< your_third_choice> Sarah McCarthy
< your_fourth_choice> Mary Robinson
< your_fifth_choice> Micheal D Higgins
' ）
插入#tempTbl值（3，'< resultsds>
< results>
< referendum_you_voted>
< your_first_choice> Barack Obama
< your_second_choice> Micheal D Higgins
< your_third_choice> David Norris
< your_fourth_choice> ; Mary Robinson
< your_fifth_choice> Sarah McCarthy
'）
选择
FirstChoice = x.value（'Your_First_Choice [1]'，'varchar（100）'），
SecondChoice = x.value（'Your_Second_Choice [1] '，'varchar（100）'），
ThirdChoice = x.value（'Your_Third_Choice [1]'，'varchar（100）'），
FourthChoice = x .value（'Your_Fourth_Choice [1]'，'varchar（100）'），
FifthChoice = x.value（'Your_Fifth_Choice [1]'，'varcha r（100）'）
来自#tempTbl tc交叉申请tc.Doc.nodes（'ResultsDS / Results'）e（x）
它将为您提供表格数据，通过这些数据，您可以计算每列中出现次数最多的特定位置投票
If your requirement is to shred XMl Data values you can do this as below:
Create table #tempTbl
(
Id int,
Doc XML
)
Insert into #tempTbl values (1,'<resultsds>
<results>
<referendum_you_voted>
<your_first_choice>Micheal D Higgins
<your_second_choice>Sarah McCarthy
<your_third_choice>David Norris
<your_fourth_choice>Mary Robinson
<your_fifth_choice>Barack Obama
')
Insert into #tempTbl values (2,'<resultsds>
<results>
<referendum_you_voted>
<your_first_choice>Barack Obama
<your_second_choice>David Norris
<your_third_choice>Sarah McCarthy
<your_fourth_choice>Mary Robinson
<your_fifth_choice>Micheal D Higgins
')
Insert into #tempTbl values (3,'<resultsds>
<results>
<referendum_you_voted>
<your_first_choice>Barack Obama
<your_second_choice>Micheal D Higgins
<your_third_choice>David Norris
<your_fourth_choice>Mary Robinson
<your_fifth_choice>Sarah McCarthy
')
Select
FirstChoice=x.value('Your_First_Choice[1]','varchar(100)'),
SecondChoice=x.value('Your_Second_Choice[1]','varchar(100)'),
ThirdChoice=x.value('Your_Third_Choice[1]','varchar(100)'),
FourthChoice=x.value('Your_Fourth_Choice[1]','varchar(100)'),
FifthChoice=x.value('Your_Fifth_Choice[1]','varchar(100)')
from #tempTbl tc Cross apply tc.Doc.nodes('ResultsDS/Results') e(x)
It will give you tabular data by which you can count occurence in each column to most voted for specific position
这篇关于XQuery XML数据类型SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
