Datatype XML in SQL Server

[Kenbla]

Hello out there!
I'm trying to retrieve a number of rows from a table in SQL Server 2005. One of the columns is an XML datatype (xmlcolumn).
The XML in the column has a number of repeating nodes (Person) and I would like to retrieve some of those nodes using some kind of SQL select statement.
I have tried a number of different ways for example:
SELECT * FROM table_logg
WHERE xmlcolumn.value('(Root/Person/Data/Firstname) [1]', 'nvarchar(1000)') = 'john'
AND xmlcolumn.value('(Root/Person/Data/City) [1]', 'nvarchar(1000)') = 'boston'
This piece of code works OK BUT it only gives one occurence! How should I do in order to retrieve multiple rows?
Thanks!

 

[DjangMan]

Is there only one john from boston?

 

[Kenbla]

No, that's one of the problems! As it is right now it only retrieves the first [1] Person/Data/City element but I want to retrieve all cities that contains 'boston' in each Person/Data node.
The XML data type column (xmlcolumn) may contain any number of Person/Data/City occurrences and I want all of them in my query.
The XML document looks something like this:
<Root>
<Person>
<Data>
<City>Boston</City>
</Data>
<Data>
<City>Dallas</City>
</Data>
<Data>
<City>Phoenix</City>
</Data>
<Data>
<City>Boston</City>
</Data>
</Person>
</Root>
Using this example I would like to retrieve to "Boston rows

 

[DjangMan]

Presumably you've tried:

SELECT * FROM table_logg
WHERE xmlcolumn.value('(Root/Person/Data/City) [1]', 'nvarchar(1000)') = 'boston'

 

[Kenbla]

Thanks. Yes I have tried that, but it only retrieves one occurrence of Boston from my XML type column and there are two Boston!
I need to get rid of the value function with the [1] argument and replace it with some other piece of code but I don't know how to do it!

 

[whosrdaddy]

xmlcolumn.value will return a single value (as outlined in the help file). xmlcolumn.nodes will return a rowset.

/Daddy

-----------------------------------------------------
What You See Is What You Get
Never underestimate tha powah of tha google!

 

[Kenbla]

Yes, I know that but what should I do to make it work? I understand that I should use the function "nodes" in order to return a rowset but I can't get it to work! By using the "nodes" function I can get a complete rowset of the data all the nodes that I want but how should I apply a "where" clause to my query, the "where" clause should be applied against the rowset I guess, but how?

 

[DjangMan]

Does this page help:

http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic44439.aspx

It looks like using the CrossApply function/keyword is important.

 

[Kenbla]

Thanks for your reply. I still don't get it to work and I have now tried a different approach using the function exist like this:

SELECT * FROM table_logg
WHERE xmlcolumn.exist('/Root/Person/Data/City="Boston") = 1

This query gives me ALL records in the database!! Not only those where city = Boston!? What can be wrong? Any ideas anyone?

 

[Kenbla]

I have also tried to use the query function:

SELECT xmlcolumn.query('//Root[//Person/Data/City="Boston"') AS XMLNode
FROM table_logg

This query returns ALL records in the database but the the ones that are not in Boston returns a NULL value in the XMLNode column, so in a way it works a little bit better than the "exist" example but I want the query to return only the ones in Boston!

 

[DjangMan]

What does this do:

SELECT * FROM table_logg
WHERE xmlcolumn.exist('/Root/Person/Data/City="Boston")

 

[Kenbla]

I was hoping that it would give me all rows from the table table_logg that has city = Boston in the xmlcolumn.
The xmlcolumn is defined in the database as a datatype XML column.
My problem is that this query retrieves every row in the database and not only those that has city = Boston.