0

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 
5

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.

  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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