I have a XPath statement that works well on my xml.
//PaymentProperty[Name='PaymentReference']/StoredStringValue
When I store this xml in mytable.xmlField, is there a way to get this value from SQL? I tried
select x.xml.query('//PaymentProperty[@Name=PaymentReference]/StoredStringValue') from (select cast(xmlField as xml) as xml from mytable
But I guess XQuery likes different syntax? It is quite a dynamic xml so all I need to know is if there is Value of "PaymentReference" anywhere in the parentNode PaymentProperty against the tag then get the value against the tag StoredStringValue. Is there anyway to achieve this from SQL?
Thanks for all the help