Query to search for a substring in xml RRS feed

  • Question

  • I use below query to search for a substring in whole xml(including node name and node value)

    SELECT * 
    FROM tablename
    WHERE ( Charindex('abc',CAST([xmlcolumn] AS VARCHAR(MAX)))>0 ) 

    I want an alternative query which has good performance than this. So please suggest some.

    Monday, June 24, 2013 1:33 PM


  • To query both element names and values you can use local-name and the self-node (.), eg

    DECLARE @yourTable TABLE ( yourXML XML )
    INSERT INTO @yourTable ( yourXML )
    SELECT '<Root>
    -- Show all elements
    	x.y.value('local-name(.)', 'VARCHAR(MAX)') elementName,
        x.y.value('.', 'VARCHAR(MAX)') elementValue
    FROM @yourTable t
    	CROSS APPLY t.yourXML.nodes('//*[text()]') AS x(y)

    If you are going to be doing this regularly consider permanently storing the element names and values in a table for querying.

    Also read these great articles:

    Performance Optimizations for the XML Data Type in SQL Server 2005

    XML Indexes in SQL Server 2005

    Tuesday, June 25, 2013 1:09 AM