title: Create XML from the input parameter in stored procedure
body: <p>I have a stored procedure that does a search based on the input parameters. Now for auditing reasons the business requirement is to save what is searched and who search as a XML column in the <code>Auditlog</code> table. </p>
  
  <p>I have simplified what I am trying to do in this example. All the declares are my input parameter. I am creating XML in my stored procedure and inserting it into the <code>audit</code> column. To test this out I simulated the behaviour </p>
  
  <pre><code>DECLARE @LastName varchar(50)
  DECLARE @FirstName varchar(50)
  DECLARE @RelationShip varchar(50)
  DECLARE @CallDate date
  DECLARE @CallStartTime time(7)
  DECLARE @AdminID int 
  DECLARE @HomePhone varchar(15) = null
  DECLARE @WorkPhone varchar(15) = null 
  DECLARE @MobilePhone varchar(15) = null
  DECLARE @SearchXML xml
  DECLARE @UserName  varchar(50)
  
  SET @LastName = 'rayan'
  SET @FirstName = 'Meg'
  SET @RelationShip = 'Friend'
  SET @CallDate = (SELECT GETDATE())
  SET @CallStartTime = (SELECT CONVERT (time, SYSDATETIME()))
  SET @AdminID = 74
  SET @HomePhone = null
  SET @WorkPhone = null 
  SET @MobilePhone = null
  SET @UserName = 'nojha'
  
  SET @SearchXML = 
          '&lt;Root&gt;
              &lt;CallerInformation&gt;
                  &lt;LastName&gt;' + @LastName + '&lt;/LastName&gt;
                  &lt;FirstName&gt;' + @FirstName + '&lt;/FirstName&gt;
                  &lt;Relationship&gt;' + @RelationShip + '&lt;/Relationship&gt;
                  &lt;CallDate&gt;' + @CallDate + '&lt;/CallDate&gt;
                  &lt;CallStartTime&gt;' + @CallStartTime + '&lt;CallStartTime/&gt;
                  &lt;HomePhone&gt;' + @HomePhone + '&lt;/HomePhone&gt;
                  &lt;WorkPhone&gt;' + @WorkPhone + '&lt;/WorkPhone&gt;
                  &lt;WorkPhone&gt;' + @WorkPhone + '&lt;WorkPhone/&gt;
                  &lt;UserName&gt;' + @UserName + '&lt;/UserName&gt;                  
              &lt;/CallerInformation&gt;
          &lt;/Root&gt;'
  
  SELECT @SearchXML
  </code></pre>
  
  <p>I get two errors when I do this </p>
  
  <ol>
  <li>The data types <code>varchar</code> and <code>date</code> are incompatible in the add operator. </li>
  <li>I figured it some error due to date. So I removed <code>CallDate</code> and <code>CallStartTime</code> to see if I am getting proper XML. But running the above query returns null. </li>
  </ol>
  
  <p>Can someone help me out with this?</p>
  
  <p>Thanks
  Neha </p>