Help needed with Ntekst and searching

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help needed with Ntekst and searching

Author

Topic

mpjjansen
Starting Member

3 Posts

Posted - 2014-11-04 : 03:45:03

Hello,

I have a tables called Contentdata and in the table there are 2 colums XML (ntekst) and Recordid (nvarchar)

I spent hours of searching the net for a solution to my problem but can't find.

for example a part off the xml field

not usefull 
not usefull tekst <a href="http://dummylink.nl/index?page=content&id=on25480">not usefull</a> (PDF). some not usefull tekst <a target="_blank" href="http://dummylink.nl/C12570CF00508B57?Opendatabase">klantenkaart</a>.]]></INHOUD><INHOUD_INTERN SECURITY=""/>

No i want a query that search the entire table and only shows the href links and the recordid (if there are more then one href show them all).

does any know how to do this.
Thanks so much for any ideas

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-04 : 09:48:40

Questions:

1. It looks like the html is inside an XML CDATA section. What XML element contains the CDATA?
2. Can your HTML with the CDATA section contain more than one href?

mpjjansen
Starting Member

3 Posts

Posted - 2014-11-05 : 04:14:50

quote:
Originally posted by gbritton

Questions:

1. It looks like the html is inside an XML CDATA section. What XML element contains the CDATA?
2. Can your HTML with the CDATA section contain more than one href?

i have the following code and now i can select the first record in the CDATA but i can't figure out how to keep looking in the same record to search for more entries.

SELECT ct.DOCUMENTID as 'DocumentID',
'Link Library'= CASE
WHEN CONVERT(VARCHAR(MAX),cd.XML) LIKE '%href="%'
THEN SUBSTRING(CONVERT(VARCHAR(MAX),cd.XML), PATINDEX('%href="%',CONVERT(VARCHAR(MAX),cd.XML)), 150)
ELSE ''
END,
'Afbeelding Library'= CASE
WHEN CONVERT(VARCHAR(MAX),cd.XML) LIKE '%src="/library/%'
THEN SUBSTRING(CONVERT(VARCHAR(MAX),cd.XML), PATINDEX('%src="/library/%',CONVERT(VARCHAR(MAX),cd.XML)), 150)
ELSE ''
END
FROM #actief as cd
inner join CONTENTTEXT as ct on ct.RECORDID = cd.RECORDID
where CONVERT(VARCHAR(MAX),cd.XML) LIKE '%="/library/%'

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-05 : 09:29:33

This is a text-book example of the need for this function: http://www.sqlservercentral.com/articles/String+Manipulation/94365/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources