Home TSQL XML locate particular block(s)
Reply: 0

TSQL XML locate particular block(s)

user1687
1#
user1687 Published in July 17, 2018, 7:58 am
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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.402136 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO