Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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. Below are details: Table:

CREATE TABLE [dbo].[tablename](
    [Sl_no] [int] NOT NULL,
    [Date] [date] NULL,
    [Operation] [nvarchar](max) NULL,
    [Allot] [nvarchar](50) NULL,
    **[xmlcolumn]** [xml] NULL,
    [By] [nvarchar](255) NULL,
    [Dept] [nvarchar](255) NULL,
    [Db] [varchar](255) NULL,
    [tabl] [varchar](255) NULL,
    [Remark] [varchar](5000) NULL,
    [Work] [int] NULL,
    [F2] [nvarchar](max) NULL,
    [F6] [nvarchar](max) NULL,
    [F5] [nvarchar](max) NULL,
    [F8] [nvarchar](max) NULL,
    [ListC] [nvarchar](255) NULL,
    [pro] [nvarchar](max) NULL,
    [Completed] [varchar](50) NULL,
    [WorkTime] [xml] NULL,
    [RelatedData] [varchar](255) NULL,
    [User] [xml] NULL,
    [TeBy] [xml] NULL,
    [Date1] [nvarchar](50) NULL,
    [Num1] [nvarchar](50) NULL,
 CONSTRAINT [PK_DBChanges] PRIMARY KEY CLUSTERED 
(
    [Sl_no] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Number of rows: 3000 Sample xml in a column:(number of nodes may change little bit.

<Root>
  <Row>
    <User>abs</User>
    <Rowid>1</Rowid>
  </Row>
  <Row>
    <User>xra</User>
    <Rowid>2</Rowid>
  </Row>
  <Maxrowid>2</Maxrowid>
</Root>

Type of search expected:To search for a substring within xml column 'xmlcolumn' and then return the full row containing it.

So the query i used is

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

The IO for the query by using set statistics time on

SQL Server Execution Times:
   CPU time = 62 ms,  elapsed time = 93 ms.

I tried with another query(But it will search only in the node value)

select *
from tablename
where xmlcolumn.exist('//*/text()[contains(., "abc")]') = 1

and statistics output was

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 109 ms.
share|improve this question
1  
do you need to the location of the substring, or just that the substring is within the string? –  Thomas Rushton Jun 24 '13 at 13:51
1  
Thomas asked what you need since SELECT * is generally considered bad practice and might be a large portion of your performance problem. If you are specific about your requirements, we are able to help you in a more meaningful way. –  Max Vernon Jun 24 '13 at 14:28
1  
This question is a follow-on from dba.stackexchange.com/questions/45133/… –  Max Vernon Jun 24 '13 at 14:37
3  
You should add much more detail to your question. What do you consider slow? How many rows are in the table? What is the table structure? Are you doing this pattern match on a regular basis, or is this just for some one-off background process? –  Max Vernon Jun 24 '13 at 14:40
1  
If you could change the process that inserts the XML, you could have it mark the row as containing the text in question. That would make it trivially easy to find the desired rows. –  Max Vernon Jun 24 '13 at 14:41
show 5 more comments

2 Answers

To know what performance you will have you have to test on your data. I obviously can't do that so I made up my own xml data to test the two queries you have in this question.

Create a table with 5000 rows containing an XML document of 9475 characters in 415 nodes:

create table T
(
  ID int identity primary key,
  XMLCol xml not null
)

declare @X xml = 
(
  select top 100 *
  from master..spt_values
  for xml path('row'), root('root'), type
)

insert into T(XMLCol)
select top(5000) @X
from master..spt_values as m1, master..spt_values as m2

Execute the queries to search for a value that is present in the first node (rpc) and another value that is present in the last node (SERVER ROLE).

select count(*)
from T
where charindex('rpc',cast(xmlcol as varchar(max))) > 0

select count(*)
from T
where XMLCol.exist('//*/text()[contains(., "rpc")]') = 1

select count(*)
from T
where charindex('SERVER ROLE',cast(xmlcol as varchar(max))) > 0

select count(*)
from T
where XMLCol.exist('//*/text()[contains(., "SERVER ROLE")]') = 1

The IO for the different queries is the same so here is the output from using set statistics time on

Search for rpc with charindex:

 SQL Server Execution Times:
   CPU time = 1435 ms,  elapsed time = 1434 ms.

Search for rpc with xml exist

 SQL Server Execution Times:
   CPU time = 63 ms,  elapsed time = 68 ms.

Search for SERVER ROLE with charindex

 SQL Server Execution Times:
   CPU time = 7316 ms,  elapsed time = 7321 ms.

Search for SERVER ROLE with xml exist

 SQL Server Execution Times:
   CPU time = 3245 ms,  elapsed time = 3244 ms.

Clear winner in both cases is the XML query. It does a better job of scanning the entire XML and it does a much better job of early termination when the search string is found.

This is true for the test data above using SQL Server 2012. It could be different for you with your data and your search strings. You have to test to know what is best for you.

NOTE: As stated in the answer to your other question, the two queries above will not return the same result bucause the XML query only search node values where the charindex query searches the entire XML document including nodenames and markup.

share|improve this answer
    
Thank you.But do you know any other option or query which can search entire XML document including nodenames and markup? –  IT researcher Jun 25 '13 at 11:45
    
@ITresearcher Not sure what/how you actually want to search. It would help if you could provide a table with a sample XML and the expected output showing how you want to filter on node-names. –  Mikael Eriksson Jun 25 '13 at 12:17
    
I will try to post the table and details as soon as possible as some details in table are confidential. By the way why my question is closed? –  IT researcher Jun 25 '13 at 14:31
1  
@ITresearcher If you modify your question so that you include enough details about what you want to do then it might be reopened. Table structure, number of rows, size/complexity of XML, type of searches against the XML, expected output. Info on what your performance is now with the queries you already have and what would be acceptable performance for you. –  Mikael Eriksson Jun 25 '13 at 14:40
add comment

If performance is an issue, id start off checking all the indexes:

Primary Xml Index:

CREATE PRIMARY XML INDEX [PXML_tablename_tablecolumn] ON [dbo].[tablename]
(
    [tablecolumn]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

Secondary Xml Path Index:

CREATE XML INDEX [IX_tablecolumn_pathx] ON [dbo].[tablename]
(
    [tablecolumn]
)
USING XML INDEX [PXML_tablename_tablecolumn] FOR PATH WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Secondary Value Index:

CREATE XML INDEX [IX_Uds_tablecolumn_valuex] ON [dbo].[tablename]
(
    [tablecolumn]
)
USING XML INDEX [PXML_tablename_tablecolumn] FOR VALUE WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Secondary Property Index:

CREATE XML INDEX [IXML_tablecolumn_Property] ON [dbo].[tablename]
(
    [tablecolumn]
)
USING XML INDEX [PXML_tablename_tablecolumn] FOR PROPERTY WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Selective Xml Index:

CREATE SELECTIVE XML INDEX [xsi_tablename] ON [dbo].[tablename]
(
    [columnname]
)
WITH XMLNAMESPACES
(
DEFAULT 'http://www.something.xsd'
)

FOR
(
[1] = '/Root' as XQUERY 'node()',  
[2] = '/Root/Row' as XQUERY 'node()', 
[3] = '/Root/Maxrowid' as XQUERY 'node()',  
[4] = '/Root/Row/User' as XQUERY 'node()', 
[5] = '/Root/Row/Rowid' as XQUERY 'node()', 
[6] = '/Root/@Something' as SQL [nvarchar](50) SINGLETON , 
[7] = '/Root/Row/@SomethingElse' as SQL [int] SINGLETON 

)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

If you have a fulltext catalog and index setup you can try searching for the sub strings with something like this: (I didnt fully test it, but you can pass in the search and it will shred your xml column. Hope this helps!

    CREATE PROCEDURE [dbo].[SearchSomething]
        @findString NVARCHAR(100)
    AS
    BEGIN

        SET NOCOUNT ON
        SET @findstring = UPPER(@findstring);

    ;WITH XMLNAMESPACES (Default 'http://www.w3.org/2001/XMLSchema' )
    SELECT 

                XmlId

               ,x.value('.' , 'nvarchar(MAX)') AS Value
               ,a.value('(@Something1)[1]', 'NVARCHAR(200)') AS Column1
               ,b.value('(@Something2)[1]','nvarchar(MAX)') As Column2 
               ,c.value('@Something3[1]', 'INT') AS Column3

          --,RANK() OVER 
       --        (PARTITION BY d.value('@Something3[1]', 'INT') 
          --      Order By XmlId  DESC) As RANK

    FROM dbo.XmlTable x

    CROSS APPLY XmlDocument.nodes('Root/Row') As aa(a)
    CROSS APPLY a.nodes('User') dd(b) 
    CROSS APPLY a.nodes('Rowid') As cc(c)
    CROSS APPLY a.nodes('(.//*[contains(upper-case(text()[1]),     sql:variable("@findString"))])[1]
    , (.//@*[contains(upper-case(.), sql:variable("@findString"))])[1]') a(x)
    --Where a.x.value('.','nvarchar(MAX)') like '%'+@findstring+'%'


     Where ' ' + a.x.value('.','nvarchar(MAX)') + ' ' like '%[^a-z]' + @findstring + '[^a-z]%'

    Order By XmlId

        RETURN
    END

    GO

you can comment out the Where clause to see unfiltered results etc.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.