Searching XML Data Type

Last Post 09 Dec 2008 07:52 AM by tcarnes. 4 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

--
08 Dec 2008 02:23 PM
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:

quote:

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.
tcarnes
New Member
New Member

--
09 Dec 2008 07:52 AM

tcarnes
New Member
New Member

--
09 Dec 2008 08:09 AM
Russell,

Thank you for your reply.

I was hoping that there is an easy way to check to see if a given XML field contains a given word, without having to know anything about the structure of the XML contained therein. This is because my XML field is a catch-all of various "sections" of data.

Following are some examples of the xml field contents:

quote:

<component>
<section ID="Dosage_Admin">
<id root="01B758BB-70C1-4AB5-A972-6CAA66605506" />
<code code="34068-7" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" displayName="DOSAGE & ADMINISTRATION SECTION" />
<title mediaType="text/x-hl7-title+xml">DOSAGE AND ADMINISTRATION</title>
<text>
<paragraph>Patients with tinea (pityriasis) versicolor should apply Mentax<sup>®</sup> once daily for two weeks. Sufficient Mentax<sup>®</sup> Cream should be applied to cover affected areas and immediately surrounding skin of patients with tinea versicolor. If a patient shows no clinical improvement after the treatment period, the diagnosis and therapy should be reviewed.</paragraph>
</text>
<effectiveTime value="20060120" />
</section>
</component>


quote:

<component>
<section>
<id root="59D1F486-AA7A-AF89-BC40-6054FE37CDC0" />
<code code="34070-3" codeSystem="2.16.840.1.113883.6.1" codeSystemName="LOINC" displayName="CONTRAINDICATIONS SECTION" />
<title mediaType="text/x-hl7-title+xml">CONTRAINDICATIONS</title>
<text>
<paragraph>EMCYT Capsules should not be used in patients with any of the following conditions:</paragraph>
<list>
<item>
<caption>1)</caption>Known hypersensitivity to either estradiol or to nitrogen mustard.</item>
<item>
<caption>2)</caption>Active thrombophlebitis or thromboembolic disorders, except in those cases where the actual tumor mass is the cause of the thromboembolic phenomenon and the physician feels the benefits of therapy may outweigh the risks.</item>
</list>
</text>
<effectiveTime value="20060407" />
</section>
</component>


quote:

<component>
<section ID="s18">
<id root="d65ea3b2-9ffb-46f1-bfb7-c31343b9475a" />
<code code="34069-5" codeSystem="2.16.840.1.113883.6.1" displayName="HOW SUPPLIED SECTION" />
<title mediaType="text/x-hl7-title+xml">HOW SUPPLIED
</title>
<text>
<paragraph>Fluocinolone Acetonide Ointment USP, 0.025%
</paragraph>
<list listType="unordered" styleCode="disc">
<item>
<caption> </caption>15 gram tubes, NDC 0168-0064-15
</item>
<item>
<caption> </caption>60 gram tubes, NDC 0168-0064-60
</item>
</list>
<paragraph>Store at controlled room temperature 15°-30°C (59°-86°F).
</paragraph>
<paragraph>
<content styleCode="bold">E. FOUGERA & CO.
</content>
<br />
<br />
<content styleCode="italics">a division of Altana Inc.
</content>
<br />
<br />MELVILLE, NEW YORK 11747
</paragraph>
<paragraph>I26415B
<br /><br />#296
<br /><br />R8/98
</paragraph>
</text>
<effectiveTime value="20060119" />
</section>
</component>
tcarnes
New Member
New Member

--
09 Dec 2008 08:58 AM
It looks like I have figured it out. Thanks again, Russell, for pointing me to the two resources listed above.

To find all records where the search term "pharmacokinetics" is found in any <paragraph> node within the xml field [Section XML]:
select * from section where SectionXml.exist('//paragraph/text()[contains(.,"pharmacokinetics")]') = 1

The "//" allows me to search every paragraph node without having to know the exact structure of the XML in the field.

Thanks again for your help!

Terry
SwePeso
New Member
New Member

--
10 Dec 2008 03:24 AM
You can also use t-sql variables for searching xml data.
See http://weblogs.sqlteam.com/peterl/a...ables.aspx
You are not authorized to post a reply.

Acceptable Use Policy