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?
<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?