CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user240141
user240141

Reputation:

Returning HTML tags from Select query in MSSQL 2008

Consider my Select query, I m using

Select Convert(xml, '<b>Grand Total</b>'),
Convert(xml,'<b>'+Convert(varchar,Sum(amt))+'</b>')as Amt
From myTable
For xml path(''),type  //with and without results are same

Also I have more HTML tags coming from a local variable in this form:

Set @loc_var = '<Table><TR><TD>This is some dummy Text</TD></TR></TABLE>' and so on. The above result set is finally then gets embedded within the above HTML string in somewhere. But after the embedding final result contains the Select statements result as below and rest are fine, no problems. Kindly help.

Output:

   <Table><TR><TD>This is some dummy Text</TD></TR></TABLE> &lt;b&gt;Grand Total&lt;/b&gt;

DESIRED OUTPUT

<Table><TR><TD>This is some dummy Text</TD></TR></TABLE><b>Grand Total</b>

Upvotes: 1

Views: 9282

Answers (2)

felisleo119
felisleo119

Reputation: 51

Hard to find:

SELECT @DATA = CONCAT('<br><br>',(SELECT X ,',' From MYTABLE For XML PATH (''));

After select XML path in a variable use, REPLACE:

SELECT @DATA = REPLACE(REPLACE(@DATA,'&lt ;','<'),'&gt ;','>');--THERE IS SPACE CHARACTERS, YOU SHOULD CLEAN "&gt ;" AND "&lt ;"

and then you can INSERT or SELECT @DATA

Upvotes: 2

Devart
Devart

Reputation: 122032

Try this one -

DECLARE @temp TABLE (value INT)
INSERT INTO @temp (value)
VALUES (1),(2)

SELECT '<Table><TR><TD>This is some dummy Text</TD></TR></TABLE>' + (
   SELECT SUM(value) AS [text()]
   FROM @temp
   FOR XML PATH('b')
)

Output -

----------------------------------------------------------------
<Table><TR><TD>This is some dummy Text</TD></TR></TABLE><b>3</b>

Upvotes: 0

Related Questions