Build XML from dynamic sql.

• s-sql

  SSCommitted

  Points: 1970

  More actions

  May 6, 2008 at 4:44 pm

  #207115

  My dynamic sql is as follows.

  I want to build the resulting resultset of this dynamic SQL, into XML.

  For the normal SQL Statements, we would achive this by using FOR XML

  Clause on the SQL Statement.

  ====================

  select * FROM table1 AS t FOR XML AUTO, ELEMENTS

  ====================

  How can i achive this in case of dynamic SQL.??

  ================================

  SELECT @MyNewSql = '

  select ''' + @Type + ''',''' + @TableName + ''','

  + @PKSelect+ ','''

  + @fieldname + ''''+ ',convert(varchar(1000),d.' + @fieldname + ')'

  + ',convert(varchar(1000),i.'

  + @fieldname + ')' + ',''' + @UpdateDate + ''''+ ',''' + @UserName + ''''

  + ' from #ins i full outer join #del d' + @PKCols

  + ' where i.' + @fieldname + ' <> d.' + @fieldname

  + ' or (i.' + @fieldname + ' is null and d.'

  + @fieldname

  + ' is not null)'

  + ' or (i.' + @fieldname + ' is not null and d.'

  + @fieldname + ' is null)'

  EXEC (@MyNewSql)

  ========================

• Max Yasnytsky

  SSCrazy

  Points: 2462

  More actions

  May 6, 2008 at 5:31 pm

  #811522

  how about this

  declare table #tmpTable (... put all your columns in here)

  SELECT @MyNewSql = '

  select ''' + @Type + ''',''' + @TableName + ''','

  + @PKSelect+ ','''

  + @fieldname + ''''+ ',convert(varchar(1000),d.' + @fieldname + ')'

  + ',convert(varchar(1000),i.'

  + @fieldname + ')' + ',''' + @UpdateDate + ''''+ ',''' + @UserName + ''''

  + ' from #ins i full outer join #del d' + @PKCols

  + ' where i.' + @fieldname + ' <> d.' + @fieldname

  + ' or (i.' + @fieldname + ' is null and d.'

  + @fieldname

  + ' is not null)'

  + ' or (i.' + @fieldname + ' is not null and d.'

  + @fieldname + ' is null)'

  insert into #tmpTable (... put all your columns in here)

  EXEC (@MyNewSql)

  select * FROM #tmpTable AS t FOR XML AUTO, ELEMENTS