June 20, 2013 at 11:26 pm
I have a table with column name 'xmlmycolumn' which is of datatype xml. I want to search for string in that whole xml. So to do this i am casting column as varchar and then i use charindex as shown below.
SELECT *
FROM mytable
WHERE ( Charindex('abc',CAST([xmlmycolumn] AS VARCHAR(MAX)))>0 )
Is there any other method other than casting the column as varchar so that performance of the query is increased?
June 20, 2013 at 11:55 pm
Hi,
you can go through this link hope this will work for you 😛
http://www.sqlservercentral.com/Forums/Topic1243118-338-1.aspx#bm1243586
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 21, 2013 at 3:12 am
hi kapil
In the link you have mentioned there i found below queries
DECLARE @tbl TABLE
(
id INT,
col XML
)
INSERT INTO @tbl
VALUES
(1,'<name><name1>5</name1></name>'),(2,'<name>6</name>')
DECLARE @id int
SELECT @id = 6
SELECT *
FROM @tbl t1
WHERE t1.col.exist('//*[text()=sql:variable("@id")]')=1
Here what is t1.col.exist('//*[text()=sql:variable("@id")]')=1
I mean where exactly it will search? i didn't understand what is //*[text()] will do?
Also in another query what does //*/text() mean?
SELECT *
FROM @tbl t1
WHERE col.exist('//*/text()[contains(.,sql:variable("@id"))]') = 1
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply