Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Complex query for datatype=XML in SQL Server 2005 1

Status
Not open for further replies.

Kenbla

Programmer
Oct 11, 2006
68
SE
I have an XML document stored in column defined as datatype=XML (xmlcolumn) in an SQL Server 2005 table (logg_table). The XML document in the column looks something like this:
<Root>
<Person>
<Data>
<Adress>Boston Street</>
<City>Boston</City>
</Data>
<Data>
<Adress>Dallas Main Street</>
<City>Dallas</City>
</Data>
<Data>
<Adress>Phoenix Main Street</>
<City>Phoenix</City>
</Data>
<Data>
<Adress>Boston Main Street</>
<City>Boston</City>
</Data>
</Person>

Using this XML I would like to write a query that should retrieve all rows that meet the following criteria:
- Adress = Boston Main Street
- City = Boston

I have a query but it doesn't work exactly as I want it to. It looks like this:
SELECT colName FROM logg_table
WHERE xmlcolumn.exist('/Root/Person/Data/City/text()[.="Boston"]') = 1
AND xmlcolumn.exist('/Root/Person/Data/Adress/text()[.="Boston Main Street"]') = 1
The query above will in this case return one row which seems to be OK BUT the following query will also return one row which is not correct if you look at the XML document:
SELECT colName FROM logg_table
WHERE xmlcolumn.exist('/Root/Person/Data/City/text()[.="Boston"]') = 1
AND xmlcolumn.exist('/Root/Person/Data/Adress/text()[.="Phoenix Main Street"]') = 1

I want the query to search for rows meeting the criteria within the same node (Data)! How do I do that?
 
[tt]SELECT colName FROM logg_table
WHERE xmlcolumn.exist('/Root/Person/Data[City = "Boston" AND Adress = "Boston Main Street"]') = 1[/tt]
 
Tsuji! Thank you very much for your reply.
You have solved my problem and my query works fine now!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top