CODEWITHSUNDEEP
sql-serversql-server-2008for-xml-path
L Riley
L Riley

Reputation: 337

SQL Server 2008: Null Return in Dynamic XML Query

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)

Upvotes: 3

Views: 39

Answers (1)

Neo
Neo

Reputation: 3399

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)

Upvotes: 1

Related Questions