Post reply

Finding a string in XML column using sql server

  • August 2, 2017 at 4:25 am

    #322924

    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:07 am

    #1953737

    Some sample XML would be great here, so we know what we're looking for and what the structure of your data is like.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • August 2, 2017 at 5:16 am

    #1953739

    Thom A - Wednesday, August 2, 2017 5:07 AM

    Some 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

    #1953742

    Sample data is VERY important, as without it, we can't test. If we can't test, we can't assure you that what we're providing works for you; and if it doesn't, it's unlikely we can do much to fix it if we can't replicate the problem.

    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