Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL Server
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>
 
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.
Posted 1-Feb-13 4:45am
Edited 1-Feb-13 6:02am
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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'
 

Syntax is not exact, as I do not have SQL Server running now.
 
More information can be found on MSDN:
 
http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx[^]
  Permalink  
Comments
SarahWebandCloud at 1-Feb-13 11:06am
   
Thanks for that it does produce a result which is better than the errors ive been getting. Im just looking to figure out now how to get the max count of a value (and the value) of an attribute in the XML data.
would you know off hand how to do that by any chance.
Vyacheslav Voronenko at 1-Feb-13 11:41am
   
I suppose in a similar manner, but if necessary cast to another datatype, rather than nvarchar, if element contains number.
 
Make sure you put indexes on appropriate paths.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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,'


Micheal D Higgins
Sarah McCarthy
David Norris
Mary Robinson
Barack Obama

')
Insert into #tempTbl values (2,'


Barack Obama
David Norris
Sarah McCarthy
Mary Robinson
Micheal D Higgins

')
Insert into #tempTbl values (3,'


Barack Obama
Micheal D Higgins
David Norris
Mary Robinson
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
  Permalink  

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

  Print Answers RSS
0 Zoltán Zörgő 300
1 /\jmot 205
2 Peter Leow 165
3 CHill60 145
4 CPallini 135
0 Sergey Alexandrovich Kryukov 9,373
1 OriginalGriff 6,881
2 Peter Leow 4,577
3 Zoltán Zörgő 4,244
4 CHill60 2,892


Advertise | Privacy | Mobile
Web02 | 2.8.150129.1 | Last Updated 18 Jun 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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