SQL select statement

I am trying to build a transact-SQL statment that I can paste into an xml document.
This Transact-SQL statement gives me what I want:

Select
'<wp:comment_author>'+name+'</wp:comment_author>'+  CHAR(13)+CHAR(10) +
'<wp:comment_author_IP>'+source_ip+'</wp:comment_author_IP>'+  CHAR(13)+CHAR(10)

Result:
 <wp:comment_author>john Doe</wp:comment_author>  <wp:comment_author_IP>170.12.14.133</wp:comment_author_IP>  

However when the source_IP field is null all I get returned is
NULL
I would like to get the output below: (so if source_ip is null it will list out everything else, with nothing at all as the value where the source_ip normally goes.
Below is what I want:
 <wp:comment_author>john Doe</wp:comment_author>  
<wp:comment_author_IP></wp:comment_author_IP>
cbeverlyAsked:
Who is Participating?
 
ZberteocConnect With a Mentor Commented:
When you build text from values of SQL variables or columns you will always have to wrap all the columns in ISNULL(col/variable, '<null_replacement_value>') especially when you concatenate. NULL involved in any oeration will result in NULL. SO just in case you should have the name wrapped in ISNULL as well:

Select
'<wp:comment_author>'+ISNULL(name,'')+'</wp:comment_author>'+  CHAR(13)+CHAR(10) +
'<wp:comment_author_IP>'+isnull(source_ip,'')+'</wp:comment_author_IP>'+  CHAR(13)+CHAR(10) 

Open in new window

0
 
Surendra NathTechnology LeadCommented:
you have to do this

Select
'<wp:comment_author>'+name+'</wp:comment_author>'+  CHAR(13)+CHAR(10) +
'<wp:comment_author_IP>'+isnull(source_ip,'')+'</wp:comment_author_IP>'+  CHAR(13)+CHAR(10) 

Open in new window

0
 
cbeverlyAuthor Commented:
Thank you so much!!!
0
 
Surendra NathTechnology LeadCommented:
Hey I posted the same solution a day before....
0
 
ZberteocCommented:
I agree, however is one thing to drop a solution "as is" and another thing to to give the solution along with some explanations of why and what. Split points could have worked though.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.