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 onlyvarchar
will in this case give you a length of one so the content of@id
will bex
.Your first query checks for nodes that contain
x
and that matches all rows. The second query checks for nodes that hasx
as value and no row has that.Change the declaration of
@id
toDECLARE @id varchar(3)
and everything will work as expected.