TSQL XML detects specific block (s)

Question

DECLARE @XMLData XML

SET @XMLData = '<ArrayOfAttributeValueDO>
      <AttributeValueDO>
        <AttributeID>421</AttributeID>
        <AttributeValue>100% cotton pre-shrunk drill</AttributeValue>
      </AttributeValueDO>
      <AttributeValueDO>
        <AttributeID>422</AttributeID>
        <AttributeValue>190gsm</AttributeValue>
      </AttributeValueDO>
      <AttributeValueDO>
        <AttributeID>1221</AttributeID>
        <AttributeValue>Long Sleeve</AttributeValue>
      </AttributeValueDO>
      <AttributeValueDO>
        <AttributeID>1481</AttributeID>
        <AttributeValue>No</AttributeValue>
      </AttributeValueDO>
    </ArrayOfAttributeValueDO>'

Given the above example, how can I get a specific one <AttributeValueDO>by searching in <AttributeID>and <AttributeValue>?

I kind of expect syntax like this, but have an error between "[]" Actually, I'm trying to get any <AttributeValueDO>with attributeIDfrom 422 and attributeValuecontains the word "gsm"

DECLARE @strAttributeID VARCHAR(1000) = '422'
DECLARE @strAttributeValue VARCHAR(1000) = '190gsm'

SELECT [AttributeValueXML] 
FROM [dbo].[tbl_Stock_Master_AttributeValue] 
WHERE [AttributeValueXML].exist('(/ArrayOfAttributeValueDO/AttributeValueDO[AttributeID=sql:variable("@strAttributeID") && AttributeValue=sql:variable("@strAttributeValue")])') = 1

Answer 1

One chance is to read the entire batch as a view and put your filter with WHEREat the end (as suggested in another answer). But, at least in my eyes, it’s more efficient to place the filter directly in XQuery.

If you can be sure that there is only one event, try this:

DECLARE @strAttributeID VARCHAR(1000) = '422'
DECLARE @strAttributeValue VARCHAR(1000) = '190gsm'
SELECT @XMLData.value(N'(/ArrayOfAttributeValueDO
                         /AttributeValueDO[(AttributeID/text())[1]=sql:variable("@strAttributeID") 
                                            and contains((AttributeValue/text())[1],sql:variable("@strAttributeValue"))]
                         /AttributeValue/text())[1]','nvarchar(max)');

, .nodes(), .value(), node:

SELECT Attr.value('(AttributeValue/text())[1]','nvarchar(max)')
FROM @XMLData.nodes(N'/ArrayOfAttributeValueDO
                      /AttributeValueDO[(AttributeID/text())[1]=sql:variable("@strAttributeID") 
                                            and contains((AttributeValue/text())[1],sql:variable("@strAttributeValue"))]') AS Searched(Attr);

: (SomeElement/text())[1] SomeElement[1]/text()[1] SomeElement[1]. ( )... : , . ... /text()

Answer 2

- :

DECLARE @AttributeID INT = 422
DECLARE @AttrValue VARCHAR(20) = 'gsm'

SELECT
    xc.query('.')
FROM
    @XMLData.nodes('/ArrayOfAttributeValueDO/AttributeValueDO') AS XT(XC)
WHERE
    xc.value('(AttributeID)[1]', 'int') = @AttributeID 
    AND xc.value('(AttributeValue)[1]', 'varchar(50)') LIKE '%' + @AttrValue + '%'

XML ( XML- <AttributeValueDO>), .