November 18, 2014 at 9:37 am
I am trying to replace names found in 'xml' fieldname as hyperlinks, by matching with the names in database_tags.
I am using the following cursor function and but it does not perform the correct output:
ALTER FUNCTION [dbo].[ReplaceTags2](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Name VARCHAR(MAX)
DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT name
FROM [dbo].[database_tags]
Where UploadDate >= '2014-09-01'
and @XML LIKE '%' + Name + '%'
OPEN CUR
WHILE 1 = 1
BEGIN
FETCH cur INTO @name
--IF @Name IS NOT NULL
IF @@fetch_status <> 0
BREAK
BEGIN
SELECT @XML = REPLACE(@XML,
@Name,
'<a href="<a href="pagename.aspx?tag='+@Name+'">'+@Name+'</a>')
END
--FETCH NEXT FROM CUR INTO @Name
END
CLOSE CUR;
DEALLOCATE CUR;
RETURN @XML
END
I pass the following XML Input to the UDF:
<Body><p align="justify">One is a 1m block of AIREM 2006-1X 2A3, which has never appeared on SMO.
</Body>
the function above, outputs the following (which is incorrect).
One is a £1m block of <a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a></a>"><a href="<a href="pagename.aspx?tag=<a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>"><a href="<a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>
The desired output should be :
<Body><p align="justify">One is a 1m block of <a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>, which has never appeared on SMO.
</Body>
I have attached an example of my dataset in the following link below, for further reference as to what my dataset types are.
http://sqlfiddle.com/#!6/96cac8/2
Please advice further, where I may be possible going wrong.
Thank you for your help and time
November 18, 2014 at 9:43 am
Why does your replacement text have <a href ... twice?
What is the value of @Name when you get the incorrect output?
Gerald Britton, Pluralsight courses
November 18, 2014 at 9:50 am
Thank you for your reply. Apology for the typo error and here is the updated code:
ALTER FUNCTION [dbo].[ReplaceTags2](@XML VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Name VARCHAR(MAX)
DECLARE CUR CURSOR FAST_FORWARD FOR
SELECT name
FROM [dbo].[database_tags]
Where UploadDate >= '2014-09-01'
and @XML LIKE '%' + Name + '%'
OPEN CUR
WHILE 1 = 1
BEGIN
FETCH cur INTO @name
--IF @Name IS NOT NULL
IF @@fetch_status <> 0
BREAK
BEGIN
SELECT @XML = REPLACE(@XML,
@Name,
'<a href="pagename.aspx?tag='+@Name+'">'+@Name+'</a>')
END
--FETCH NEXT FROM CUR INTO @Name
END
CLOSE CUR;
DEALLOCATE CUR;
RETURN @XML
END
I have attached an example of my dataset in the following link below, for further reference as to what my dataset types are.
Please follow the link below for the data sample set:
http://sqlfiddle.com/#!6/96cac8/2
Please advice further, if possible. Many thanks
November 18, 2014 at 2:39 pm
Why are you using a cursor for this? And why do you have a variable named @XML that is not XML?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 22, 2014 at 1:51 am
Quick solution of the "first coffee in the morning" kind, probably not the most efficient but it works
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID('dbo.database_tags') IS NOT NULL DROP TABLE dbo.database_tags;
create table dbo.database_tags
(
id nvarchar(50) not null,
name nvarchar(100) null,
type nvarchar(50) null,
dateCreated datetime null
)
IF OBJECT_ID('dbo.article') IS NOT NULL DROP TABLE dbo.article;
create table dbo.article
(
id nvarchar(50) not null,
XML ntext null,
title nvarchar(50) null,
dateCreated datetime null
)
INSERT INTO dbo.article (id, xml, title,
dateCreated)
VALUES ('1', '<Body><p align="justify">One is a 1m block of AIREM 2006-1X 2A3, which has never appeared on SMO.
</Body> ','article1', ''),
('2',' <body>It consists of: BANKP, BCJA, BCJAM, BFTH, BFTH, and EMPOP.</body>','article2','');
INSERT INTO dbo.database_tags (id, name, type,
dateCreated)
VALUES
('1', 'BANKP', '', ''),
('2','BCJA','',''),
('3','BCJAM','',''),
('5','BFTH','',''),
('6','EMPOP','',''),
('7','BFTH','',''),('9','AMPT','',''),
('8','AIREM 2006-1X 2A3','','');
DECLARE @TEMPLATE VARCHAR(200) = '<a href="pagename.aspx?tag={{NAME}}">{{NAME}}</a>';
;WITH DISTINCT_TAGS AS
(
SELECT DISTINCT
T.name
FROM dbo.database_tags T
)
,ORDERED_TAGS AS
(
SELECT
ROW_NUMBER() OVER
(
ORDER BY LEN(T.name)
) AS TAGID
,T.name AS TAG
FROM DISTINCT_TAGS T
)
,ART_TAGS AS
(
SELECT
A.id AS AID
,ROW_NUMBER() OVER
(
PARTITION BY A.id
ORDER BY T.TAGID
) AS TAGID
-- ,T.TAGID
,T.TAG
FROM dbo.article A
CROSS APPLY ORDERED_TAGS T
WHERE CONVERT(NVARCHAR(MAX),A.XML,0) LIKE '%' + T.TAG + '%'
)
,ART_PARSED AS
(
SELECT
A.id AS AID
,CONVERT(NVARCHAR(MAX),A.XML,0) AS ARTICLE
,CONVERT(INT,1,0) AS TAGID
FROM dbo.article A
UNION ALL
SELECT
AP.AID
,REPLACE(SUBSTRING(AP.ARTICLE,1,DATALENGTH(AP.ARTICLE)),AT.TAG,REPLACE(@TEMPLATE,'{{NAME}}',AT.TAG)) AS ARTICLE
,CONVERT(INT,AT.TAGID,0) + 1 AS TAGID
FROM ART_PARSED AP
INNER JOIN ART_TAGS AT
ON AT.AID = AP.AID
AND AT.TAGID = AP.TAGID
)
,PARSE_FILTER AS
(
SELECT
AP.AID
,AP.TAGID
,ROW_NUMBER() OVER
(
PARTITION BY AP.AID
ORDER BY AP.TAGID DESC
) AS RID
,AP.ARTICLE
FROM ART_PARSED AP
)
SELECT
PF.AID
,PF.ARTICLE
FROM PARSE_FILTER PF
WHERE PF.RID = 1;
Results (note that the html content may not display correctly)
AID ARTICLE
--- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 <Body><p align="justify">One is a 1m block of <a href="pagename.aspx?tag=AIREM 2006-1X 2A3">AIREM 2006-1X 2A3</a>, which has never appeared on SMO.
</Body>
2 <body>It consists of: <a href="pagename.aspx?tag=BANKP">BANKP</a>, <a href="pagename.aspx?tag=BCJA">BCJA</a>, <a href="pagename.aspx?tag=BCJA">BCJA</a>M, <a href="pagename.aspx?tag=BFTH">BFTH</a>, <a href="pagename.aspx?tag=BFTH">BFTH</a>, and <a href="pagename.aspx?tag=EMPOP">EMPOP</a>.</body>
Edit: typo
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply