SQLServerCentral.com / SQL Server 2008 - General / SQL Server 2008 / Xml index / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 31 Mar 2015 12:29:04 GMT20RE: Xml indexhttp://www.sqlservercentral.com/Forums/Topic1449054-391-1.aspx[quote][b] IT researcher (5/3/2013)[/b][hr]Whether i can use selective xml index? Does it available in sql server 2008 r2 express?[/quote]It's available from sql server 2012.Fri, 03 May 2013 06:43:19 GMTIgor MicevRE: Xml indexhttp://www.sqlservercentral.com/Forums/Topic1449054-391-1.aspxWhether i can use selective xml index? Does it available in sql server 2008 r2 express?Fri, 03 May 2013 05:48:35 GMT IT researcherRE: Xml indexhttp://www.sqlservercentral.com/Forums/Topic1449054-391-1.aspx[quote][b] IT researcher (5/3/2013)[/b][hr]Ok. But while creating PATH type index is there needed to specify the path which i use? I don't know much about xml indexing. But for other datatypes there is option of filtered index where i can specify the condition. So is there any option in xml also? If not then for which path it will create index for xml column?[/quote]No, you don't need to specify any pathsCan you try this?[code="sql"]create primary xml index PXML_daily_Balance on BalanceTable(daily_Balance)create xml index [SECXMl_daily_Balance_path] on BalanceTable(daily_Balance) using xml index [PXML_daily_Balance] for path[/code]Regards,IgorMiFri, 03 May 2013 03:36:07 GMTIgor MicevRE: Xml indexhttp://www.sqlservercentral.com/Forums/Topic1449054-391-1.aspxOk. But while creating PATH type index is there needed to specify the path which i use? I don't know much about xml indexing. But for other datatypes there is option of filtered index where i can specify the condition. So is there any option in xml also? If not then for which path it will create index for xml column?Fri, 03 May 2013 03:15:30 GMT IT researcherRE: Xml indexhttp://www.sqlservercentral.com/Forums/Topic1449054-391-1.aspxHi,Because you use exist() you should create FOR PATH type xml index.RegardsIgorMiFri, 03 May 2013 02:58:00 GMTIgor MicevXml indexhttp://www.sqlservercentral.com/Forums/Topic1449054-391-1.aspxI have a table with following column.(I have directly give the create table command)CREATE TABLE [dbo].[BalanceTable]( [AccountID] [int] NOT NULL, [Type] [varchar](10) NULL, [Date] [date] NOT NULL, [Balance] [decimal](15, 2) NULL, [TRansactionDr] [decimal](15, 2) NULL, [TRansactionCr] [decimal](15, 2) NULL, [daily_Balance] [xml] NULL, CONSTRAINT [PK_BalanceTable] PRIMARY KEY CLUSTERED ( [AccountID] ASC, [Date] 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]GOI have a xml query which updates this table. But it works slowly and take some seconds. I want to speed it up. So i was try to use xml index.So in this case which index i should use? Primary? secondary(in that i have path,value and property)? So which will be better to use which will reduce time required for execution.Below is queryUpdate BalanceTable set [daily_balance].modify('replace value of (/Root/Row[date=''2011-03-23'']/Balance/text())[1] with (/Root/Row[date=''2011-03-23'']/ Balance)[1] +1') where [AccountID]=26 and [Date]='31-Mar-2011' and [daily_balance].exist('/Root/Row[date=''2011-03-23'']')=1;Also here is sample of xml column&lt;Root&gt; &lt;Row&gt; &lt;Rowid&gt;0&lt;/Rowid&gt; &lt;date&gt;2011-01-23&lt;/date&gt; &lt;Balance&gt;0.0E0&lt;/Balance&gt; &lt;TRansactionDr&gt;0.0E0&lt;/TRansactionDr&gt; &lt;TRansactionCr&gt;0.0E0&lt;/TRansactionCr&gt; &lt;/Row&gt; &lt;Maxrowid&gt;0&lt;/Maxrowid&gt; &lt;Row&gt; &lt;Rowid&gt;0&lt;/Rowid&gt; &lt;date&gt;2011-01-31&lt;/date&gt; &lt;Balance&gt;123&lt;/Balance&gt; &lt;TRansactionDr&gt;123&lt;/TRansactionDr&gt; &lt;TRansactionCr&gt;0.0E0&lt;/TRansactionCr&gt; &lt;/Row&gt;&lt;/Root&gt;So please give me suggestionsThu, 02 May 2013 23:49:21 GMT IT researcher