Filtering XML query by attribute value

Question

I am trying to extract parameters from an xml field but can't seem to figure out how to filter my parameters by the parameter's name attribute.

XML

<parameters>
    <parameter name="id">
        <item>2</item>
    </parameter>
    <parameter name="channel">
        <item>retail</item>
        <item>wholesale</item>
    </parameter>
</parameters>

I am trying to extract the int 2 and in a subsequent query extract the words retail and wholesale.

I have seen it done many different ways online but none seem to work for me. Any help would be appreciated.

thanks!

Answer 1

Try this:

declare @x xml = '<parameters>
    <parameter name="id">
        <item>2</item>
    </parameter>
    <parameter name="channel">
        <item>retail</item>
        <item>wholesale</item>
    </parameter>
</parameters>'

select t.s.value('.', 'nvarchar(max)')
from @x.nodes('//parameter[@name = "id"]/item') t(s)

It returns following table:

2