locked
'Like' operator in XQuery in a Table RRS feed

  • Question

  • I have a table  test_table with 2 columns:

    CREATE TABLE [dbo].[test_xml_table](
        [ID] [int] NOT NULL,
        [XMLData] [xml] NOT NULL

    the XML column has Data formatted like this

    <TestDoc id = "123">
        <Data>
            <DataItem Key="food" Value="curds and whey" />
            <DataItem Key="furniture" Value="tuffit" />
            <DataItem Key="text" Value="Little Miss Muffit sat on her tuffit eating her curds and whey." />
        </Data>
    </TestDoc>

    There are multiple records with data in the same format.  What I would like to do is return the "Value" from the DataItem where the "Key" = "text" where the value is like '%Muffit%curds%' in SQL LIKE syntax.  What is the best way to retrieve that data?

    Wednesday, August 1, 2012 10:49 PM

Answers

  • You can use the XQuery "contains" function or pull the value of the Value attribute out and then use the actual SQL Like syntax.  You could also do something like this with full text search.

    Here's a few examples to get you started:

    USE tempdb
    GO
    
    CREATE TABLE [dbo].[test_xml_table](
         [ID] [int] NOT NULL,
         [XMLData] [xml] NOT NULL 
    	)
    GO
    
    INSERT INTO [dbo].[test_xml_table] ( ID, XMLData )
    SELECT 1, '<TestDoc id = "123">
     <Data>
         <DataItem Key="food" Value="curds and whey" />
         <DataItem Key="furniture" Value="tuffit" />
         <DataItem Key="text" Value="Little Miss Muffit sat on her tuffit eating her curds and whey." />
     </Data>
    </TestDoc>'
    UNION ALL
    SELECT 2, '<TestDoc id = "7">
    <Data><DataItem Key="text" Value="wBob versus Miss Muffit." /></Data></TestDoc>'
    
    
    -- Doesn't guarantee order of words
    SELECT t.ID, di.c.value('@Value', 'VARCHAR(MAX)')
    FROM [dbo].[test_xml_table] t
    	CROSS APPLY t.XMLData.nodes('TestDoc/Data/DataItem[@Key="text"][contains(@Value,"Muffit")][contains(@Value,"curds")]') di(c)
    
    -- Using Like
    SELECT t.ID, di.c.value('@Value', 'VARCHAR(MAX)')
    FROM [dbo].[test_xml_table] t
    	CROSS APPLY t.XMLData.nodes('TestDoc/Data/DataItem[@Key="text"]') di(c)
    WHERE di.c.value('@Value', 'VARCHAR(MAX)') Like '%Muffit%curds%' 
    
    --!!TODO full text search example
    GO
    
    -- Cleanup
    DROP TABLE [dbo].[test_xml_table]

    • Proposed as answer by Iric Wen Sunday, August 5, 2012 11:08 AM
    • Marked as answer by Iric Wen Thursday, August 9, 2012 2:05 AM
    Thursday, August 2, 2012 12:08 AM
    Answerer

All replies

  • You can use the XQuery "contains" function or pull the value of the Value attribute out and then use the actual SQL Like syntax.  You could also do something like this with full text search.

    Here's a few examples to get you started:

    USE tempdb
    GO
    
    CREATE TABLE [dbo].[test_xml_table](
         [ID] [int] NOT NULL,
         [XMLData] [xml] NOT NULL 
    	)
    GO
    
    INSERT INTO [dbo].[test_xml_table] ( ID, XMLData )
    SELECT 1, '<TestDoc id = "123">
     <Data>
         <DataItem Key="food" Value="curds and whey" />
         <DataItem Key="furniture" Value="tuffit" />
         <DataItem Key="text" Value="Little Miss Muffit sat on her tuffit eating her curds and whey." />
     </Data>
    </TestDoc>'
    UNION ALL
    SELECT 2, '<TestDoc id = "7">
    <Data><DataItem Key="text" Value="wBob versus Miss Muffit." /></Data></TestDoc>'
    
    
    -- Doesn't guarantee order of words
    SELECT t.ID, di.c.value('@Value', 'VARCHAR(MAX)')
    FROM [dbo].[test_xml_table] t
    	CROSS APPLY t.XMLData.nodes('TestDoc/Data/DataItem[@Key="text"][contains(@Value,"Muffit")][contains(@Value,"curds")]') di(c)
    
    -- Using Like
    SELECT t.ID, di.c.value('@Value', 'VARCHAR(MAX)')
    FROM [dbo].[test_xml_table] t
    	CROSS APPLY t.XMLData.nodes('TestDoc/Data/DataItem[@Key="text"]') di(c)
    WHERE di.c.value('@Value', 'VARCHAR(MAX)') Like '%Muffit%curds%' 
    
    --!!TODO full text search example
    GO
    
    -- Cleanup
    DROP TABLE [dbo].[test_xml_table]

    • Proposed as answer by Iric Wen Sunday, August 5, 2012 11:08 AM
    • Marked as answer by Iric Wen Thursday, August 9, 2012 2:05 AM
    Thursday, August 2, 2012 12:08 AM
    Answerer
  • Something like this will work in SQL 2012 but for element-based XML only:

    -- SQL 2012 only ...
    SELECT ID, XMLData
    FROM [dbo].[test_xml_table]
    WHERE CONTAINS(XMLData, 'NEAR((Muffit,tuffit), 10, TRUE)');
    

    This also guarantees order. 

    See here for more info on why attributes aren't included in full-text indexes.

    Thursday, August 2, 2012 1:41 AM
    Answerer