Click here to Skip to main content
15,867,956 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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

XML
<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
Updated 1-Feb-13 5:02am
v2

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:

SQL
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[^]
 
Share this answer
 
Comments
sarahmccarthy 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 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.
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
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900