XML query filter by element name I have the following structure with a variable number of elements on the 2nd level under root with unknown names:

&lt;root&gt;
   &lt;A&gt;text a&lt;/A&gt;
   &lt;B&gt;text b&lt;/B&gt;
&lt;/root&gt;


I need a query that delivers the text of a specific element in the 2nd level. the name of the required element is the argument passed in by a user. 

I already succeeded in finding it for example for element "A", querying the XML document stored in variable @x:

declare @str varchar(1000) = '&lt;root&gt;&lt;A&gt;text a&lt;/A&gt;&lt;B&gt;text b&lt;/B&gt;&lt;/root&gt;'
declare @x xml = convert(xml, @str)
SELECT xLangStruct.x.value('(A/text())[1]', 'varchar(100)') from @x.nodes('//root') as xLangStruct(x)


This works for "A", but now I need to replace "A" with a variable or any kind of filtering... So I need anything like that: 

SELECT xLangStruct.x.value('(@SEARCHEDELEMENT/text())[1]', 'varchar(100)') from @x.nodes('//root') as xLangStruct(x)


or

SELECT xLangStruct.x.value('(./text())[1]', 'varchar(100)') from @x.nodes('//root') as xLangStruct(x) WHERE xLangStruct.x.Elementname = @SEARCHEDELEMENT
While there's no messing with Mikael Eriksson when it comes to XML (he is, after all, my spiritual XML guide), here's an answer specific to your question.

You just have to change the way you're handling the XML a little bit. I also changed the XML a touch to rule out false positives searching for A and B in the node names to text 1 and text 2.

DECLARE @xmltable TABLE (xmlvalue XML)
INSERT @xmltable ( xmlvalue )
    VALUES ( '&lt;root&gt;&lt;A&gt;text 1&lt;/A&gt;&lt;B&gt;text 2&lt;/B&gt;&lt;/root&gt;' )

DECLARE @searchstring VARCHAR(100) = 'A'

SELECT x.*,
xa.c.value('.', 'VARCHAR(100)') AS [weird]
FROM @xmltable AS x
CROSS APPLY x.xmlvalue.nodes('/root/*[local-name()=sql:variable("@searchstring")]') xa (c)


This returns text 1.

DECLARE @xmltable TABLE (xmlvalue XML)
INSERT @xmltable ( xmlvalue )
    VALUES ( '&lt;root&gt;&lt;A&gt;text 1&lt;/A&gt;&lt;B&gt;text 2&lt;/B&gt;&lt;/root&gt;' )

    DECLARE @searchstring VARCHAR(100) = 'B'

    SELECT x.*,
    xa.c.value('.', 'VARCHAR(100)') AS [weird]
    FROM @xmltable AS x
    CROSS APPLY x.xmlvalue.nodes('/root/*[local-name()=sql:variable("@searchstring")]') xa (c)


This returns text 2.