Using XML Path in SQL Stored Procedure need URL

  • 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

  • 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