June 22, 2013 at 12:59 am
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
June 22, 2013 at 4:33 am
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply