none
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

Answers

  • 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>
      <Row>
        <User>abc</User>
        <Rowid>1</Rowid>
      </Row>
      <Row>
        <User>def</User>
        <F8>#User#</F8>
        <givenby>ghi</givenby>
        <R>2</R>
      </Row>
      <Maxrowid>2</Maxrowid>
    </Root>'
    
    -- Show all elements
    SELECT 
    	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
    http://msdn.microsoft.com/en-us/library/ms345118.aspx

    XML Indexes in SQL Server 2005
    http://msdn.microsoft.com/en-us/library/ms345121(SQL.90).aspx

    Tuesday, June 25, 2013 1:09 AM
    Answerer