I have a database that has basically been formed by denormalizing the XML data into a series of linked tables. There is one section of the XML that is more free-form than the rest and not easily parsed, so I created a separate table that holds some meta-data, but mainly has a xml field which holds the xml snippet.
My question, how do I search the xml data for a given word? For example, my clients will want to be able to find all records that contain the word "medicine" in the xml snippet. I've been trying to read the various documentation that MS has on the web, but often find myself getting lost in discussions on how to create and/or index the xml datatype without a nice clear example of how to search.
Sample create table below:
CREATE TABLE [dbo].[Section](
[SectionId] [bigint] NOT NULL,
[LabelId] [bigint] NOT NULL,
[SectionRef] [varchar](150) NULL,
[SectionIdGuid] [varchar](50) NULL,
[SectionCode] [varchar](10) NULL,
[SectionCodeSystem] [varchar](50) NULL,
[SectionCodeSystemText] [varchar](250) NULL,
[SectionTitle] [varchar](250) NULL,
[SectionXml] [xml] NULL
) ON [PRIMARY]
Thank you in advance for any help you can offer.