August 2, 2017 at 4:25 am
1. I have a table with a xml column.
2. I require to search for sub string in that xml column for all its node and value. Search should be case insensitive
3. Structure of XML in each row is different
I used below query to do that,
select * from TableName Where Cast(xmlcolumn as varchar(max) ) like '%searchString%'
this works for short length xml rows, if row length goes huge it cant handle the situation. Only partial of the data was searched.
Suggest me some other ways to achieve.
August 2, 2017 at 5:16 am
Thom A - Wednesday, August 2, 2017 5:07 AMSome sample XML would be great here, so we know what we're looking for and what the structure of your data is like.
I think sample is not required because XML structure is not static. We just require, the given string is available anywhere in xml or not.
I got this query, looking for some better way
DECLARE @Table1 TABLE (
ID INT IDENTITY PRIMARY KEY,
CommentAsXML XML
)
INSERT @Table1 (CommentAsXML)
VALUES (N'<root><item /><item type="Reg">0001</item><item type="Inv">B007</item><item type="Cus">A0001</item><item type="Br">F0001</item></root>')
INSERT @Table1 (CommentAsXML)
VALUES (N'<root><item /><item type="Reg">0005</item><parent><child>B007</child></parent><item type="Br">F0005</item></root>')
INSERT @Table1 (CommentAsXML)
VALUES (N'<root><item /><item type="Reg">0005</item></root>')
-- Following query is searching for B007 within InnerText of all XML elements:
SELECT *
FROM @Table1 t
WHERE t.CommentAsXML.exist('//*[lower-case(text()[1]) eq "b007"]') = 1
August 2, 2017 at 5:26 am
What do you mean by "better". For the sample you provided your new SQL works. You also mentioned that the XML is very large, however, that XML is small. Is that still when you have the problem? I don't have any "large" xml fields in by Sandbox, Dev or Live environments i can test against, so I can't check.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy