CODEWITHSUNDEEP
sqlsql-serversql-server-2008
DotNetBeginner
DotNetBeginner

Reputation: 439

Create XML from the input parameter in stored procedure

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

  1. The data types varchar and date are incompatible in the add operator.
  2. I figured it some error due to date. So I removed 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

Answers (2)

Richard Deeming
Richard Deeming

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

JBond
JBond

Reputation: 3252

  1. DATE and VARCHAR are not compatible when using '+'. Therefore you must convert the DATE to a VARCHAR to be able to concatenate them.
  2. Some of your tags do not match, e.g, you have WorkPhone/ instead of /WorkPhone on the closing tag.
  3. You are concatenating value's that are NULL. This causes the whole string to become NULL. You need to wrap an ISNULL(@value,'') to place an empty string when you have no value

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

Related Questions