Post reply

Query for XML column values comparison

  • March 23, 2013 at 6:40 am

    #291369

    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?

  • March 25, 2013 at 2:54 am

    #1600197

    Try this

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

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

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

  • March 25, 2013 at 7:43 am

    #1600311

    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?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply