search tag attribute value based on child node val

Question

I have a xml file having structure something like below:

        <role name="test" pattern=".*">
            <assignedSIDs>
                <sid>abc</sid>
                <sid>cde</sid>
                <sid>def</sid>
            </assignedSIDs>
        </role>

        <role name="test1" pattern=".*">
            <assignedSIDs>
                <sid>abc</sid>
                <sid>zxc</sid>
                <sid>vbn</sid>
            </assignedSIDs>
        </role>

        <role name="test2" pattern=".*">
            <assignedSIDs>
                <sid>abc</sid>
                <sid>hex</sid>
                <sid>oct</sid>
            </assignedSIDs>
        </role>

I want to find role tag name attribute based on value of sid tag. for eg: if I search for abc, the query must return test, test1 and test2

I referred this below link and got half of the solution .

XMLStarlet Return attribute based on value (Reverse lookup)

I also refered this too

http://xmlstar.sourceforge.net/doc/UG/xmlstarlet-ug.html

but I didn't got any example that would support my requirement.

Is there something I can do?

Answer 1

Even though the XPATH given by @martin-honnen was correct, i was not getting the expected output. This was due to old version of xmlstarlet 1.0.1 that was installed in my system. As I upgraded to 1.6.1, problem was solved.
here is the link, where @kjhughes helped me to find the solution xmlstarlet XPath expression selects single result rather than multiple

Answer 2

The XPath expression you can use is //role[.//sid = 'abc' ]/@name, the command line for xmlstarlet is xmled.exe sel -t -v "//role[.//sid = 'abc']/@name" input.xml (I think it depends on your command line shell which quote character you need to use to wrap the XPath expression).