search within single xml document in SQL 2005

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

firechaser@talk21.com - 06 Feb 2006 09:14 GMT

Hi

I have a full text indexed xml column in sql server 2005 containing the
following xml snippet:

<plays>
<folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says 'tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice
seen of us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>

I'd like to be able to search this document for keywords, such as
"belief" to return the value '@id=9267' i.e. return the id value of the
containing node ('page') where the string "belief" was found.

Likewise, if the search was for "him" then '@id=9267' and '@id=8423'
would be returned.

Could anyone give me pointers as to how to implement this in XQuery /
Full-text?

Many thanks, David

Reply to this Message

Hilary Cotter - 06 Feb 2006 13:28 GMT

Search on belief using the full-text contains predicate. Then search for its
value using the XQUERY contains predicate.

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Hi
>
[quoted text clipped - 39 lines]
>
> Many thanks, David

Reply to this Message

firechaser@talk21.com - 07 Feb 2006 18:02 GMT

Thanks for your time Hilary.

Will this work on searching the content of just a single row? All the
examples I've seen are of the type

select xmlcol from t1
where contains(xmlcol, '"dog cat"')

which return the whole xml record. Is there a way to accomplish this on
a single record as per xml snippet in my original post which exists as
an xml document in a single row in the table?

I don't understand how the XQuery contains() predicate would be able to
extract information from the FT results (whole xml record) that would
be any different from just pointing it to the record in the DB without
using FT.

I'm obviously missing something, so I'd appreciate your assistance in
trying to understand how this works.

Thanks again, David

Reply to this Message

Daniel Crichton - 08 Feb 2006 15:08 GMT

firechaser@talk21.com wrote on 7 Feb 2006 10:02:57 -0800:

> Thanks for your time Hilary.
>
[quoted text clipped - 15 lines]
> I'm obviously missing something, so I'd appreciate your assistance in
> trying to understand how this works.

At a guess, I'd say that's exactly what Hilary is suggesting. Use FTS to
find the rows for the results, and then use XQUERY on those rows to extract
just the bit you need from each XML document. In order to point XQUERY at a
row you need to first determine which row to point to :)

Dan

Reply to this Message

firechaser@talk21.com - 08 Feb 2006 21:12 GMT

Thanks Dan

There is only a single row in the table at present that contains the
above xml snippet, so what would FTS be doing that I couldn't do by
telling XQuery to go to that single row and querying the content of the
xml column?

I will know in advance which single record in the table needs to be
searched.

Looking at the above snippet, I need to be able to search for the word
"him" which would give me '@id=9267' and '@id=8423' returned.

I'm not looking to search across rows - that appears straightforward -
I'm trying to perform searches *within* the xml cell that exits in a
single (and at the moment, unique) record in my table.

Thanks to anyone who might be able to clear this up for me - I'm
obviously not seeing the wood for the trees.

Thanks again.

David

Reply to this Message

Daniel Crichton - 09 Feb 2006 10:34 GMT

firechaser@talk21.com wrote on 8 Feb 2006 13:12:17 -0800:

> Thanks Dan
>
[quoted text clipped - 15 lines]
> Thanks to anyone who might be able to clear this up for me - I'm
> obviously not seeing the wood for the trees.

If you already know which row to look in, then there's no point in using
FTS - just use XQUERY on the xml column in the row to find what you're
looking for.

Dan

Reply to this Message

firechaser@talk21.com - 09 Feb 2006 13:31 GMT

So FTS in SQL 2005 can't usefully index the contents of the xml column
of a single known row - it only works across multiple rows thus
returning the row(s) whose xml column data match the search criteria?

Searching within a single xml document as per my first post is
impossible with FTS?

Reply to this Message

Daniel Crichton - 09 Feb 2006 14:02 GMT

firechaser@talk21.com wrote on 9 Feb 2006 05:31:15 -0800:

> So FTS in SQL 2005 can't usefully index the contents of the xml column
> of a single known row - it only works across multiple rows thus
> returning the row(s) whose xml column data match the search criteria?
>
> Searching within a single xml document as per my first post is
> impossible with FTS?

No, you can search in the column of that one row - but it just tells you if
the row contains the data, not where the data is located within the xml.
Your reply implied that you already know which row you want to look at, and
that you need to extract the attributes that match the data you're searching
for which is where XQUERY comes in. If you already know the row to look in,
why would you need FTS to tell you? However, if you need to first find the
row(s) that contain the data, then use FTS to find them - and then process
each one with XQUERY to extract the bits you need.

Dan

Reply to this Message

firechaser@talk21.com - 09 Feb 2006 18:29 GMT

Thanks Dan

I was hoping to be able to use FTS for its advanced search features
(noise words, natural language such as go ->went, sit ->sat etc) which
are more powerful than what I can accomplish with the available subset
ofXQuery/XPath in SQL 2005.

But it it's not possible *within* a single xml document to return
multiple results, then so be it...

Regards, David

Reply to this Message

Hilary Cotter - 10 Feb 2006 20:08 GMT

Try this
create database FullTextXML
use FullTextXML
GO
drop table XMLFULLText
drop XML SCHEMA COLLECTION PlaysSchema
CREATE XML SCHEMA COLLECTION PlaysSchema AS '
<xsd:schema targetNamespace="http://www.plays.com/plays"
xmlns ="http://www.plays.com/plays"
elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:element name="plays"><xsd:complexType><xsd:complexContent>
<xsd:restriction base="xsd:anyType"><xsd:choice
maxOccurs="unbounded"><xsd:element name="folder">
<xsd:complexType><xsd:complexContent><xsd:restriction
base="xsd:anyType"><xsd:sequence>
<xsd:element name="page" minOccurs="0"
maxOccurs="unbounded"><xsd:complexType>
<xsd:complexContent><xsd:restriction base="xsd:anyType"><xsd:sequence>
<xsd:element name="submitdate" type="xsd:string" minOccurs="0" />
<xsd:element name="pagetext" type="xsd:string" minOccurs="0" />
</xsd:sequence><xsd:attribute name="id" type="xsd:string"
/></xsd:restriction></xsd:complexContent>
</xsd:complexType></xsd:element></xsd:sequence><xsd:attribute name="id"
type="xsd:string" />
</xsd:restriction></xsd:complexContent></xsd:complexType></xsd:element></xsd:choice></xsd:restriction>
</xsd:complexContent></xsd:complexType></xsd:element></xsd:schema>'
GO
Create Table XMLFULLText (pk int not null identity constraint XMLFULLTEXTPK
primary key, XMLDOC XML(PlaysSchema))
GO
declare @XMLDOC XML
set @XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let belief take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder></plays>'

insert into XMLFULLText (XMLDOC) values(@XMLDOC)
GO
sp_fulltext_database 'enable'
GO
create fulltext catalog test as default
GO
create fulltext index on XMLFULLText (XMLDOC) key index XMLFULLTextPK
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned. OK, lets make this a little more interesting
GO
declare @XMLDOC XML
set @XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>12-10-02</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let beliefs take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--1 row returned.
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 row returned. OK, lets restrict this to contents coming from an element
declare @XMLDOC XML
set @XMLDOC ='<plays xmlns="http://www.plays.com/plays"><folder id="681">
<page id="3155">
<submitdate>belief</submitdate>
<pagetext>Horatio says ''tis but our
fantasy,</pagetext>
</page>
<page id="9267">
<submitdate>09-04-04</submitdate>
<pagetext>And will not let take hold of
him</pagetext>
</page>
</folder>
<folder id="902">
<page id="1853">
<submitdate>22-11-05</submitdate>
<pagetext>Touching this dreaded sight, twice seen of
us:</pagetext>
</page>
<page id="8423">
<submitdate>31-05-02</submitdate>
<pagetext>Therefore I have entreated him
along</pagetext>
</page>
</folder>
</plays>'
insert into XMLFULLText (XMLDOC) values(@XMLDOC)
GO
select * from XMLFULLText where contains(*,'belief')
GO
--2 rows returned, one in page text and one in submit date
select * from XMLFULLText where FREETEXT(*,'belief')
GO
--2 rows returned. OK, lets restrict this to contents coming from an element
select * from XMLFulltext where contains(*,'belief')

WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/plays/folder/page/pagetext/text()[contains(.,"belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column

WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk, XMLDOC.query('
-- <pd:plays>
--{/pd:folder/pd:page/pd:pagetext}
-- </pd:plays>
--') AS Result
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays/pd:folder/pd:page/pd:pagetext[1]/text()[contains(.,"belief")]')=1
--notice we don't get a hit from 3 where belief is in the submitdate column
WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)

WITH XMLNAMESPACES
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescr
iption'
AS pd)
SELECT CatalogDescription.query('
<pd:Product ProductModelID="{
(/pd:ProductDescription/@ProductModelID)[1] }">
<Picture>
{ /pd:ProductDescription/pd:Picture/pd:Angle }
{ /pd:ProductDescription/pd:Picture/pd:Size }
</Picture>
</pd:Product>
') as Result
FROM Production.ProductModel
WHERE CatalogDescription.exist('/pd:ProductDescription/pd:Picture') = 1
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Angle)[1]',
'varchar(20)') = 'front'
AND
CatalogDescription.value('(/pd:ProductDescription/pd:Picture/pd:Size)[1]',
'varchar(20)') = 'small'

WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[1]/pd:submitdate[contains(.,"belief")]')=1

WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page[2]/pd:pagetext[contains(.,"belief")]')=1

WITH XMLNAMESPACES ('http://www.plays.com/plays' AS pd)
SELECT pk,*
FROM XMLFulltext
where contains(XMLDOC, 'belief')
and
xmldoc.exist('/pd:plays[1]/pd:folder[1]/pd:page/pd:pagetext[contains(.,"belief")]')=1

Signature

Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Thanks Dan
>
[quoted text clipped - 7 lines]
>
> Regards, David

Reply to this Message