0

I need to execute the following:

 SELECT * FROM [MY_TVF](9186)
 FOR XML AUTO, ELEMENTS

And replace all NULL values with an empty string '' to include them in the XML. I know I can spit out the elements with an xsi:nil="true" attribute by setting ELEMENTS XSINIL, but I don't want that.

I found this question: Convert NULL to Empty String SQL Server, where the answer says I can use ISNULL() around my query. I tried it like so:

ISNULL((SELECT * FROM [MY_TVF](9186)),'')
FOR XML AUTO,ELEMENTS

But I can't get it to work. I get the following error:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','.

What can I do to simply replace all NULL values with an empty string so they are included in the XML?

Edit

There is no way to replace the * by column names. There is a reason we use a TVF, there are about 40 columns and they might change over time. My query is just to select everything and return it as XML.

Community
  • 1
  • 1
MarioDS
  • 12,895
  • 15
  • 65
  • 121
  • 2
    You can use `isnull()` or `coalesce()` if you list the columns individually. They do not process multiple columns at the same time. – Gordon Linoff Aug 08 '14 at 11:31
  • @GordonLinoff I know that, but there are about 40 columns and some have rather long names. More importantly, they can change and hence they are specified as a TVF. I need that `*`! – MarioDS Aug 08 '14 at 11:32
  • I'd say `xsi:nil="true" is the way to go according to this topic: http://stackoverflow.com/questions/774192/what-is-the-correct-way-to-represent-null-xml-elements Or you could write dynamic SQL to get the column names? – NickyvV Aug 08 '14 at 11:38
  • 2
    @MDeSchaepmeester . . . Then you need to use dynamic SQL to get all the column names. – Gordon Linoff Aug 08 '14 at 11:44
  • 1
    Why don't you change the TVF (or make a copy) which uses COALESCE or ISNULL against the columns, instead of stomping your feet? – Aaron Bertrand Aug 08 '14 at 12:05
  • 1
    _"There is no way to replace the * by column names. There is a reason we use a TVF, there are about 40 columns and they might change over time."_ If the columns change just go in and change your SQL statement. That's a pretty common practice in the development world. Doing `SELECT *` is a bad idea for a number of reasons, and you've run into one of them now. – Dave.Gugg Aug 08 '14 at 13:11

2 Answers2

5

I have deleted my previous answer and here is the latest one:

Declare @ColName as Varchar(max)
SEt @ColName=''
SELECT  @ColName= COALESCE( @ColName + ' ISNULL(' +c.name + ','''') ','', '') +
c.name + ', ' 
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
WHERE o.xtype = 'U'
AND (o.name ='tbName')
SEt @ColName=  ( 'Select ' + SUBSTRING(@ColName,0,LEN(@ColName)-1) + ' FROM tbName')
print @colname
EXEC(@ColName)
Dev
  • 1,130
  • 10
  • 21
  • This is the preferred method for almost all SQL. The OP might never have had to do this for left/right/full joins which is bizarre for 2014. ISNULL is not as portable, nor as extensible. – mckenzm Aug 21 '17 at 01:14
0

Get out of the habit of SELECT *

See if this works.

SELECT Col1,Col2,Col3,Col4,Col5
FROM
(
SELECT 
ISNULL(Col1,'') Col1,
ISNULL(Col2,'') Col2,
ISNULL(Col3,'') Col3,
ISNULL(Col4,'') Col4,
ISNULL(Col5,'') Col5
FROM [MY_TVF](9186)
) T
FOR XML AUTO,ELEMENTS
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • Sorry, see my comment as a resonse to Gordon Linoff. I need the `*`. There are 40+ columns and I really need all the data. If this is not a good situation to justify the use of `*`, I don't know what is. – MarioDS Aug 08 '14 at 11:34
  • 1
    I see. If you have a source with changing column names you're in a bad place. – Nick.Mc Aug 08 '14 at 11:38
  • 1
    I'm not sure what feature you mean - renaming columns? There's nothing wrong with renaming columns it's just that if you have a data source where you can't count on a particular column having the same name or data type, or the same number of columns, it generally causes problems. But there isn't enough info in your question to really make any firm statement about that. Anyway sorry I couldn't help any further. If it's a TVF, can't you put the ISNULL logic in there? – Nick.Mc Aug 08 '14 at 11:42