Home TSQL XML locate particular block(s)

# TSQL XML locate particular block(s)

L.W.
1#
L.W. Published in 2017-12-07 06:06:41Z
 DECLARE @XMLData XML SET @XMLData = ' 421 100% cotton pre-shrunk drill 422 190gsm 1221 Long Sleeve 1481 No '  Given the above example, how can I get a particular  by searching on the  and ? I am kind of expecting syntax like this but having error in between "[]" In fact, I am trying to get any  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 
marc_s
2#
 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  XML element) where those two criteria apply.
 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