TSQL XML locate particular 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 particular <AttributeValueDO> by searching on the <AttributeID> and <AttributeValue>?

I am kind of expecting syntax like this but having error in between "[]" In fact, I am trying to get any <AttributeValueDO> with attributeID of 422 and attributeValue contains word of '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

Isn't it `and` instead of `&&`? Also, please include the complete error message you are getting in your question (blockquote it for readability). — TT., Dec 07 '17 at 06:19

score 3 · Answer 1 · answered Dec 07 '17 at 09:24

One chance is to read the whole lot as derived table and place your filter with WHERE at the end (as suggested in the other answer). But - at least in my eyes - it is more efficient to place the filter directly in the XQuery.

If you can be sure, that there is only one occurance 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)');

If there might be more occurances use .nodes() to get a derived table of fitting nodes and .value() to get the needed value out of each sub-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);

Hint: Using (SomeElement/text())[1] or SomeElement[1]/text()[1] is slightly faster than a simple SomeElement[1]. In your case it will return the same (with better readability)... But: In general I'd advise to be as specific as possible. Find more details on /text() here

score 0 · Answer 2 · answered Dec 07 '17 at 06:32

You can try something like this:

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 + '%'

This basically gives you any XML fragment (the whole <AttributeValueDO> XML element) where those two criteria apply.

TSQL XML locate particular block(s)

2 Answers2