March 1, 2017 at 12:54 pm
Hello,
I am trying to generate XML in a Stored Procedure that will generate a clickable link. (I know this is not idea, but need it for a short term work around) When using XML Path, is there a way to get a URL to work? I have code sample below, but after translating, the Comma and "<>" signs do not convert. Is there a way to do this correctly?
-- DROP TABLE #Test
CREATE TABLE #Test (TrackID int)
INSERT INTO #Test (TrackID) VALUES ('101')
INSERT INTO #Test (TrackID) VALUES ('102')
INSERT INTO #Test (TrackID) VALUES ('103')
-- SELECT * FROM #Test
DECLARE
@URL varchar(100),
@GetData nvarchar(MAX)
SET @URL = 'http://test.aspx?TrackID='
SET @GetData =
(
SELECT
(select TrackID as 'td' for XML PATH (''), TYPE),
(select '<a href=' + @URL + cast(TrackID as varchar(5)) + '>Approve</a>' as 'td' for XML PATH (''), TYPE)
FROM #Test
FOR XML PATH('tr')
)
SELECT @getData
March 1, 2017 at 7:15 pm
Just treat the href value as an attribute of the <a/> element, instead of turning the entire element into a string:DECLARE
@URL varchar(100),
@GetData nvarchar(MAX)
SET @URL = 'http://test.aspx?TrackID='
SET @GetData =
(
SELECT
(select TrackID as 'td' for XML PATH (''), TYPE),
-- this is XML, so the href value is an attribute of an <a/> element
(select @URL + cast(TrackID as varchar(5)) AS [a/@href], 'Approve' AS [a] for XML PATH ('td'), TYPE)
FROM #Test
FOR XML PATH('tr')
)
SELECT @getData
Eddie Wuerch
MCM: SQL
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply