Sql-server – xml queries with exist and contains giving unexpected results

sql serversql-server-2008-r2xmlxquery

I have a query as shown below.

DECLARE @tbl TABLE  
(   
 id INT,    
 col XML        
)   
INSERT INTO @tbl    
VALUES  
(1,'<Root>
  <Row>
    <User>xyz</User>
    <Rowid>1</Rowid>
  </Row>
  <Maxrowid>1</Maxrowid>
</Root>'),
(2,'<Root>
  <Row>
    <User>xyz</User>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <User>mnj</User>
    <Rowid>2</Rowid>
  </Row>
  <Maxrowid>2</Maxrowid>
</Root>'),(3,'<Root>
  <Row>
    <User>abs</User>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <User>xra</User>
    <Rowid>2</Rowid>
  </Row>
  <Maxrowid>2</Maxrowid>
</Root>
')  

--table before---
SELECT  *
FROM    @tbl t1
------------------

DECLARE @id varchar 
SELECT @id = 'xyz'  
SELECT  *   
FROM    @tbl t1 
WHERE col.exist('//*/text()[contains(.,sql:variable("@id"))]') = 1  

I am expecting this query to output rows which has 'xyz' in xml column 'col' anywhere. But it returns all rows.(when i checked it returns all rows having 'x' in xml column. So please tell me why does it happen?where i am going wrong?Also please correct it.

Also i have another problem where below query for the same above xml does not return any output.I expect it to return xml column having 'xyz' anywhere in it.So please have a look at it also and correct my errors.

DECLARE @id varchar 
SELECT @id = 'xyz'  
SELECT  *   
FROM    @tbl t1 
WHERE t1.col.exist('//*[text()=sql:variable("@id")]')=1 

Best Answer

You have not specified the length of @id. Using only varchar will in this case give you a length of one so the content of @id will be x.

Your first query checks for nodes that contain x and that matches all rows. The second query checks for nodes that has x as value and no row has that.

Change the declaration of @id to DECLARE @id varchar(3) and everything will work as expected.