XML value search with wild card character is possible in SQL?

Question

In SQL Server, while reading an XML value, Do we have option of wildcard search in [], i.e. any same listed tag by the value like:

[COMMAND].value('(/ATTRIBUTE/ATTRIBUTEID)[*]','varchar(20)') 
(Note: [COMMAND].value('(/ATTRIBUTE/ATTRIBUTEID)[3]','varchar(20)') works)

 <ATTRIBUTE>
      <ATTRIBUTEID>A</ATTRIBUTEID>
      <ATTRIBUTEVALUE>a</ATTRIBUTEVALUE>
    </ATTRIBUTE>
    <ATTRIBUTE>
      <ATTRIBUTEID>B</ATTRIBUTEID>
      <ATTRIBUTEVALUE />
    </ATTRIBUTE>
    <ATTRIBUTE>
      <ATTRIBUTEID>C</ATTRIBUTEID>
      <ATTRIBUTEVALUE />
    </ATTRIBUTE>
  <ATTRIBUTE>

Using this in SQL where clause as

Where [COMMAND].value('(/ATTRIBUTE/ATTRIBUTEID)[*]','varchar(20)') like 'C"

but not worked. getting

Syntax error near ']'

Answer 1

You need to use a combination of XML methods to retrieve all the <ATTRIBUTEID> values and then filter them using a WHERE clause. First we split XML into rows using .nodes(), extract content with .value(), and narrow results using WHERE (e.g., LIKE 'C%').

DECLARE @xmlData XML = '
<ROOT>
    <ATTRIBUTE>
        <ATTRIBUTEID>A</ATTRIBUTEID>
        <ATTRIBUTEVALUE>a</ATTRIBUTEVALUE>
    </ATTRIBUTE>
    <ATTRIBUTE>
        <ATTRIBUTEID>B</ATTRIBUTEID>
        <ATTRIBUTEVALUE />
    </ATTRIBUTE>
    <ATTRIBUTE>
        <ATTRIBUTEID>C</ATTRIBUTEID>
        <ATTRIBUTEVALUE />
    </ATTRIBUTE>
</ROOT>
'
SELECT
    T.c.value('(./ATTRIBUTEID)[1]', 'varchar(20)') AS ATTRIBUTEID,
    T.c.value('(./ATTRIBUTEVALUE)[1]', 'varchar(20)') AS ATTRIBUTEVALUE
FROM
    @xmlData.nodes('/ROOT/ATTRIBUTE[./ATTRIBUTEID]') AS T(c)
WHERE
    T.c.value('.', 'varchar(20)') LIKE 'C%'

Answer 2

You can use

SELECT @X.value('(/ATTRIBUTE/ATTRIBUTEID)[text()[contains(.,"C")]][1]','varchar(20)')

To apply an XPath predicate that the text() contains the string C.

As you are using value and that expects a single result this uses [1] to get the first such result.

You may prefer nodes instead

SELECT n.value('text()[1]', 'varchar(20)')
FROM @X.nodes('/ATTRIBUTE/ATTRIBUTEID[text()[contains(.,"C")]]') n(n)

And if you wanted to get the whole Attribute element back as the context node...

SELECT n.value('(ATTRIBUTEID/text())[1]', 'varchar(20)'),
       n.value('(ATTRIBUTEVALUE/text())[1]', 'varchar(20)')
FROM @X.nodes('/ATTRIBUTE[ATTRIBUTEID/text()[contains(.,"A")]]') n(n)

DB Fiddle