Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
share|improve this question
1  
Please read the following blog: Bad habits to kick : declaring VARCHAR without (length). –  Marian Jun 22 '13 at 22:01

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
Ok.But the @id may have any number of characters in my case so how it can be handled.Should i use varchar(max)? –  IT researcher Jun 22 '13 at 9:27
1  
@ITresearcher Unless it is enough with a varchar(63) you can use varchar(max). –  Mikael Eriksson Jun 22 '13 at 9:42
1  
@ITresearcher why would you need the [@id] variable / the [user] node being varchar(max)? Do you really want to search in the XMLs by a key that can be up to 2 GB large (max)? What's the real size for your data there? –  Marian Jun 22 '13 at 22:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.