Christian Louboutin ttza
New Member
Just similar other post, I need to retrieve any rows from table applying criteria on Xml column, for instance, supposing you have an xml column like this:\[code\]<DynamicProfile xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.datacontract.org/2004/07/WinTest"> <AllData xmlns:d2p1="http://schemas.microsoft.com/2003/10/Serialization/Arrays"> <d2p1:KeyValueOfstringstring> <d2p1:Key>One</d2p1:Key> <d2p1:Value>1</d2p1:Value> </d2p1:KeyValueOfstringstring> <d2p1:KeyValueOfstringstring> <d2p1:Key>Two</d2p1:Key> <d2p1:Value>2</d2p1:Value> </d2p1:KeyValueOfstringstring> </AllData></DynamicProfile>\[/code\]My query would be able to return all rows where node value \[code\]<d2p1:Key> = 'some key value'\[/code\] AND node value \[code\]<d2p1Value = 'http://stackoverflow.com/questions/10519701/some value value'\[/code\].Imagine of that just as a dynamic table where KEY node represent the column name and Value node represent column's value.The following query does not work because key and value nodes are not sequential:\[code\]select * from MyTable whereMyXmlField.exist('//d2p1:Key[.="One"]') = 1AND MyXmlField.exist('//d2p1:Value[.="1"]') = 1 \[/code\]