XML query filter by element name

Question

I have the following structure with a variable number of elements on the 2nd level under root with unknown names:

<root>
 <A>text a</A>
 <B>text b</B>
</root>

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) = '<root><A>text a</A><B>text b</B></root>'
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

• Sean Gallardy: Here is an already great answer: stackoverflow.com/questions/9231411/…

Answer 1

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 ( '<root><A>text 1</A><B>text 2</B></root>' )
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 ( '<root><A>text 1</A><B>text 2</B></root>' )
 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.

• Magier: Appreciate your time Erik! Your solution works perfectly. However, the solution linked in the comment above, which I unfortunately was not able to find on my own, looks more simple, doesn't it?
• Erik Reasonable Rates Darling: @Magier I don't know what your actual setup looks like, so that's hard to answer. I'd test both to see what you get for performance.