html tag

Question

Post reply

html tag

iklektic

Ten Centuries

Points: 1236
More actions
March 29, 2010 at 5:01 pm

#222563

Hello, I have a query that I'm trying to get to work;
select
AllNames = SubString (( SELECT '</li><li>' + (FirstName + ' ' + LastName )
FROM Staff
WHERE IsActive = 1
FOR XML PATH ( '' ) ), 3, 1000)
from staff
order by StaffID
however whenever I go to run it, I get the actual html code... I need it to look just like this (</li><li>'). Can someone help me?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it's not clear what you are after; if you run the code, it's going to produce results like this:

<li>Bob Smith</li><li>Bill Smith</li><li>Bill Jones</li><li>Bob Jones</li>

you did run the code, right? you know what kind of output you will receive, or was that the confusing part? the FOR XML can be misleading if you never used it before.

if you do not want html tags, that's one thing, but you seemed to say you only want html tags?

show us your desired output; this was really misleading:

I need it to look just like this (</li><li>').

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

K Cline Ten Centuries Points: 1391 More actions · Answer 2

Select AllNames = REPLACE(REPLACE(CAST(

(

Select N'<li>' + FirstName + N' ' + LastName + + N'</li>'

From Staff

Order By StaffId

For XML Path('')

) As NVarChar(1000)), '& lt;', '<'), '& gt;', '>');

To use the code, remove the spaces from '& lt' and '& gt' arguments. The site automatically converted it to the greater and less than signs when I originally posted. Made the last line look like:

) As NVarChar(1000)), '<', '<'), '>', '>');

iklektic Ten Centuries Points: 1236 More actions · Answer 3

Thank you for the response!

What I am trying to accomplish with that query is create an html list for an email blast that I need to send out...

So... in the email merge letter I have:

"... the list of affiliates under your account are:</p>

<UL>

<LI>(%AllNames%)</LI>

</UL>

So, when I created the query, I intended to export the list with the middle part of the tag (..</li><li>..) to complete the list...

Does this help explain it a little better?

Thank you and I apologize for having been vague earlier.

iklektic Ten Centuries Points: 1236 More actions · Answer 4

... and another thing about the html code.

What I meant was that when I put in the query return this "</LI><LI>", the actual result I got was: .</li><li>... which didn't translate on the mail merge letter.

iklektic Ten Centuries Points: 1236 More actions · Answer 5

This solution is exactly what I need! Thank you!

I tried the php route and didn't get far at all...

Thank you for your expertise!

K Cline Ten Centuries Points: 1391 More actions · Answer 6

Thanks, now I understand why you were looking for that format. Based on that I would change the previous query slightly.

Select AllNames = REPLACE(REPLACE(STUFF(

(

Select N'</LI><LI>' + FirstName + N' ' + LastName

From Staff

Order By StaffId

For XML Path('')

), 1, 21, ''), '& lt;', '<'), '& gt;', '>');

iklektic Ten Centuries Points: 1236 More actions · Answer 7

Thank you!

I tried that query but when I ran it... I got the 'completed' statement instead of an actual result?

On a separate note, is there a way to return an unlimited amount of results in that string?

Right now I know that i can set the limit up to 4000 (see below) but there are cases where there are way more than 4,000...

AllNames = REPLACE(REPLACE(CAST(

(

Select N'<li>' + FirstName + N' ' + LastName + + N'</li>'

From Staff AS Staff_1

WHERE IsActive = 1

Order By username

For XML Path('')

) As NVarChar(4000)), '& lt;', '<'), '& gt;', '>')

K Cline Ten Centuries Points: 1391 More actions · Answer 8

I'm a bit surprised to hear that the last one didn't return any results for you. Sorry about that. Thought I did a quick sample test before posting code...

If you are using SQL Server 2005 or later then you can make use of NVarChar(MAX). Its not unlimited, but it should suffice. It allows up to about 2GB.

I'm not positive, but if still using SQL Server 2000 then you may be stuck with the 4,000 / 8,000 limits for NVarChar and VarChar respectively. You may have to switch to something like a Text or NText data type to exceed those limits.

iklektic Ten Centuries Points: 1236 More actions · Answer 9

iklektic

Ten Centuries

Points: 1236

March 30, 2010 at 12:09 pm

#1142545

YOU ROCK!!! Am using 2005 so that should work!!