Working with XML datafield

Last Post 27 Feb 2009 01:25 PM by tcarnes. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
tcarnes
New Member
New Member

--
27 Feb 2009 01:25 PM
I apologize if this is a dumb question. Having just returned from a funeral,
I'm not thinking too clearly, yet still trying to get some work done.

I have a SQL 2005 table with two fields (among others):
[SectionXml] [xml] NULL,
[SectionXmlText] [nvarchar](max) NULL

I need to search for instances of a word in the SectionXML field. I had been
using: WHERE
SectionXml.exist('//paragraph/text()[contains(.,"search_word")]')=1

However this is taking too long, and it turns out that text I should be
searching is not only in paragraph nodes, some are in paragraph/content
nodes, and who knows how many other nodes. I have heard that XML searching
in SQL 2005 is fairly slow, so I am looking for other options.

I have an XML index on SectionXML and Full Text Indices on SectionXML and
SectionXMLText. Would I be better off doing a Full Text Search on
SectionXML? If so, how would I do this? If not, I would like to copy all the
text over to the SectionXMLText field. How would I do this?

How would I do it if I only wanted to copy all the text() from all nodes?

Thank you for any help anyone can offer.

My table structure is:

CREATE TABLE [dbo].[Section](
[SectionId] [bigint] NOT NULL,
[SectionXml] [xml] NULL,
[SectionXmlText] [nvarchar](max) NULL,
CONSTRAINT [PK_Section] PRIMARY KEY CLUSTERED
(
[SectionId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

You are not authorized to post a reply.

Acceptable Use Policy