Query XML data type using xquery 1

[jrenae]

Hello,

I'm trying to pull out records that have certain values in the XML Data type field.
table def
ID int,
XMLcol xml

The data in the XMLcol field is such

<Vet>
  <DogDetail>
    <breed>Poodle</breed>
    <color>Brown</color>
    <sex>male</sex>
  </DogDetail>
  <DogDetail>
    <breed>Lab</breed>
    <color>black</color>
    <sex>male</sex>
  </DogDetail>
  <DogDetail>
    <breed>Lab</breed>
    <color>yellow</color>
    <sex>female</sex>
  </DogDetail>
</Vet>

The xquery I'm using requires that I use a 'singleton' because the I haven't typed the xml data against a schema collection (I have reasons not to).
I'm just trying to pull out the ids of the vets that have 'Lab' as a breed. I don't get the id back for this record because the 'Lab's are not the first Dog in the xml. HOw do I omit the '[1]' in my query without getting the dreaded error???:
"'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'"

select ID
from tablename (nolock)
where XMLcol.value('(/Vet/DogDetail/breed)[1]','varchar(50)') = 'Lab'

Thanks for any insight...I'm fairly new to xquery and the sql XML data type

 

[tsuji]

It seems XMLcol.exist can apply predicate to the path. Try this?
[tt]
select ID
from tablename (nolock)
where XMLcol.exist('(/Vet/DogDetail/breed[contains(.,"Lab")]') = 1
[/tt]

 

[jrenae]

Thanks! That did work...Now I want to pass in a sql variable to the Breed. Meaning I want to replace "Lab" with @Breed

declare @Breed varchar(25)
set @Breed = "Lab"
select ID
from tablename (nolock)
where XMLcol.exist('(/Vet/DogDetail/breed[contains(.,' + @Breed + ')]') = 1

but I get the following error:
The argument 1 of the xml data type method "exist" must be a string literal.

Do you know how to construct the query so I can pass a Variable rather than a literal?

Thanks so much!

 

[tsuji]

I would say this instead.
>where XMLcol.exist('(/Vet/DogDetail/breed[contains(.,' + @Breed + ')]') = 1
[tt]where XMLcol.exist('(/Vet/DogDetail/breed[contains(.,[red]"[/red]' + @Breed + '[red]"[/red])]') = 1[/tt]

 

[jrenae]

Thanks but that still gives me this error:
The argument 1 of the xml data type method "exist" must be a string literal.

I solved the problem using the sql:variable instead like this:

Where XMLCol.exist('(/Vet/DogDetail/breed)[. = sql:variable("@Breed")]') = 1

Thank you so much for helping!

 

[tsuji]

Thanks for the feedback.