SQL Server 2008: Null Return in Dynamic XML Query

Question

I have a set of dynamic queries which return XML as varchars, see below.

Example query:

set @sqlstr = 'Select ''<?xml version="1.0" encoding="windows-1252" ?>'' + ''<File_Name><Location>'' + (Select a,b,c from table for xml path(''Row'')) + </Location></File_name>'''

exec(@sqlstr)

This works a treat until the select a,b,c ... query is NULL. Then I don't receive the outside elements as you'd expect like:

<?xml version="1.0" encoding="windows-1252"><File_Name><Location><Row></Row></Location></File_name>

All I receive is NULL

After a bit of Googling I find the issue is the concatenation of NULL results is a complete NULL Result. However I cannot find one solution gives me what I'd expect to be the result.

I've tried (not to say I have tried correctly)

IsNull(Exec(@sqlstring),'*blank elements*')
xsnil (doesn't seem to work in dynamic queries)
@result = exec(@sqlstring) then isnull and select

Anyone have a better solution? (preferably small due to multiple such queries)

Neo · Accepted Answer · 2016-11-28 12:01:18Z

1

I think you need something like this:

set @sqlstr = 'Select ''<?xml version="1.0" encoding="windows-1252" ?><File_Name><Location>'' + (Select IsNull(a, '') as a, IsNull(b, '') as b,IsNull(c, '') as c from table for xml path(''Row'')) + </Location></File_name>'''

exec(@sqlstr)

answered Nov 28, 2016 at 12:01

Neo

3,4478 gold badges37 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

L Riley Over a year ago

That's my mistake, I did a,b,c for the example its actually * as its in some cases 20 fields. your answer will work and if there is not a shorter method then having to list each one then I will accept as an answer thank you

Neo Over a year ago

If there is a shorter way, let me know so I can learn too @LRiley

L Riley Over a year ago

Will do i'll post whatever answer I find

L Riley Over a year ago

Seems there are no other options Thanks

Collectives™ on Stack Overflow

SQL Server 2008: Null Return in Dynamic XML Query

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related