Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query for XML column values comparison Expand / Collapse
Author
Message
Posted Saturday, March 23, 2013 6:40 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:11 AM
Points: 272, Visits: 1,756
Hi
We have a query for return rows by comparing with XML column values

SELECT [DBChanges].* FROM [Checklists].dbo.[DBChanges] WHERE ( [Permited_by].exist('for $x in /Root/Row where ( fn:upper-case($x/User[1]) = fn:upper-case("shw") or fn:upper-case($x/User[1]) = fn:upper-case("skt") or fn:upper-case($x/User[1]) = fn:upper-case("prs") ) return $x')>0 )

It seems we cant use IN in Xquery. So we ended up with using OR conditions for matching many values for a row.
Is there anything to optimize in the query?
Post #1434602
Posted Monday, March 25, 2013 2:54 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:03 AM
Points: 1,683, Visits: 19,615


Try this


WHERE  [Permited_by].exist('/Root/Row/User/.[fn:upper-case(.)= ("SHW","SKT","PRS")]')>0 



____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1434779
Posted Monday, March 25, 2013 7:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 5:11 AM
Points: 272, Visits: 1,756
Thanks for reply
"SELECT [DBChanges].* FROM [Checklists].dbo.[DBChanges] WHERE [Permited_by].exist('/Root/Row/User/.[fn:upper-case(.)= ("SHW","SKT","PRS")]')>0" will work very well
But then I thought i should use IN in query.After searching sometimes i found this query
select *
from [Checklists].dbo.[DBChanges]
where [Permited_by].value('(Root/Row/User)[1]', 'varchar(50)') IN ('Shw','skt','prs')

But this query will match with user only in first row because of using '(Root/Row/User)[1]'.How can i madify this to match with whole XML row?
Post #1434913
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse