Reputation: 439
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 Auditlog
table.
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 audit
column. To test this out I simulated the behaviour
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 =
'<Root>
<CallerInformation>
<LastName>' + @LastName + '</LastName>
<FirstName>' + @FirstName + '</FirstName>
<Relationship>' + @RelationShip + '</Relationship>
<CallDate>' + @CallDate + '</CallDate>
<CallStartTime>' + @CallStartTime + '<CallStartTime/>
<HomePhone>' + @HomePhone + '</HomePhone>
<WorkPhone>' + @WorkPhone + '</WorkPhone>
<WorkPhone>' + @WorkPhone + '<WorkPhone/>
<UserName>' + @UserName + '</UserName>
</CallerInformation>
</Root>'
SELECT @SearchXML
I get two errors when I do this
varchar
and date
are incompatible in the add operator. CallDate
and CallStartTime
to see if I am getting proper XML. But running the above query returns null. Can someone help me out with this?
Thanks Neha
Upvotes: 1
Views: 3557
Reputation: 31248
Are you certain that none of your parameters will contain reserved characters such as <, &, etc.?
Try something like this instead:
SET @SearchXML = (SELECT
@LastName As LastName,
@FirstName As FirstName,
@RelationShip As Relationship,
@CallDate As CallDate,
@CallStartTime As CallStartTime,
@HomePhone As HomePhone,
@WorkPhone As WorkPhone,
@UserName As UserName
FOR XML RAW ('CallerInformation'), ROOT ('Root'), ELEMENTS);
Upvotes: 5
Reputation: 3252
Here is a working example of the problem section:
SET @SearchXML =
'<Root>
<CallerInformation>
<LastName>' + @LastName + '</LastName>
<FirstName>' + @FirstName + '</FirstName>
<Relationship>' + @RelationShip + '</Relationship>
<CallDate>' + CONVERT(VARCHAR,@CallDate) + '</CallDate>
<CallStartTime>' + CONVERT(VARCHAR,@CallStartTime) + '</CallStartTime>
<HomePhone>' + ISNULL(@HomePhone,'') + '</HomePhone>
<WorkPhone>' + ISNULL(@WorkPhone,'') + '</WorkPhone>
<WorkPhone>' + ISNULL(@WorkPhone,'') + '</WorkPhone>
<UserName>' + @UserName + '</UserName>
</CallerInformation>
</Root>'
Upvotes: 2