Accessing node values in the where clause

[Pavan]

Hi,
I have an XML column in a table and i need to fetch the record based on some
values, that are part of the XML as nodes.
The xml format looks like below.
<a>
<b id="">
<c>valuec</c>
</b>
</a>

I need to fetch the record based on the "<c>" values.
Can anyone tell me a way, that i can use the "<c>" value in the where clause?

When i tried using "CROSS APPLY" and ".nodes" , i was getting an error, node
not defined.

Iam doing all this in sql2008

Regards,
Pavan

[Martin Honnen]

Which where clause exactly are you talking about, SQL, XQuery?
And you might not even need any of that as XQuery simply allows e.g.
/a/b[c = "valuec"]
where you put the condition into square brackets. The posted expression
selects the 'b' child elements 'of the 'a' root element where a 'c'
child of the 'b' has the string value "valuec".

--

Martin Honnen --- MVP XML
http://msmvps.com/blogs/martin_honnen/

[Pavan]

Hi Martin,
Actually i have multiple <c> nodes.
The code given by you works if i have only one <c> under <b>.

your help is appreciated.

"Martin Honnen" wrote:

> .
>

[Martin Honnen]

Pavan wrote:

> Actually i have multiple <c> nodes.
> The code given by you works if i have only one <c> under <b>.

Please post a sample of the XML you have and explain which data you want
and which condition you want to test in plain English, then we can try
to express that with XQuery.
It is currently not clear to me what you want to achieve.

[Pavan]

<a>
<b id="">
<c>valuec</c>

<c>valuec1</c>
<c>valuec2</c>
</b>
</a>

I would like to have a query on "valuec1"..

something like
select ...
where c = 'valuec1'

Thanks,
Pavan

"Martin Honnen" wrote:

> .
>

[Martin Honnen]

Pavan wrote:
> <a>
> <b id="">
> <c>valuec</c>
> <c>valuec1</c>
> <c>valuec2</c>
> </b>
> </a>
>
> I would like to have a query on "valuec1"..
>
> something like
> select ...
> where c = 'valuec1'

Well the path I suggested earlier e.g.

/a/b[c = "valuec1"]

does work, it does not matter how many 'c' child elements the 'b'
element has, the condition in the square brackets is true if there is at
least one 'c' child with string value 'valuec1'.

[Pavan]

Thanks Martin....It worked...
The XML was not proper before..

"Martin Honnen" wrote:

> .
>