March 29, 2010 at 5:01 pm
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?
March 29, 2010 at 6:20 pm
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
March 29, 2010 at 6:56 pm
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)), '<', '<'), '>', '>');
March 29, 2010 at 7:58 pm
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.
March 29, 2010 at 8:01 pm
... 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.
March 30, 2010 at 8:18 am
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!
March 30, 2010 at 10:00 am
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;', '>');
March 30, 2010 at 10:28 am
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;', '>')
March 30, 2010 at 11:57 am
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.
March 30, 2010 at 12:09 pm
YOU ROCK!!! Am using 2005 so that should work!!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply