Create XML from the input parameter in stored procedure

Question

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

The data types varchar and date are incompatible in the add operator.
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

If you concatenate together a string with a NULL - you'll always get NULL. You need to use ISNULL(@HomePhone, '') to replace NULL with an empty string (and apply this to all variables that might be NULL) — marc_s
– marc_s, Commented Jun 18, 2013 at 18:40
Nice handdling null worked. Now do you have any idea about how to work on dates — DotNetBeginner
– DotNetBeginner, Commented Jun 18, 2013 at 18:47

Richard Deeming · Accepted Answer · 2013-06-18 18:49:01Z

5

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);

answered Jun 18, 2013 at 18:49

Richard Deeming

31.5k11 gold badges86 silver badges167 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

DotNetBeginner Over a year ago

No i will not have any reserved characters in my parameters. But for my knowledge what difference will having reserved characters make

Richard Deeming Over a year ago

@Neha: If you had any reserved characters, you'd get an error when you tried to convert the string to XML: Msg 9421, Level 16, State 1, Line 37 XML parsing: line 4, character 32, illegal name character

Richard Deeming Over a year ago

@Neha: Whereas the FOR XML RAW version will encode the reserved characters to generate a valid XML node: <FirstName>Meg&<></FirstName>

JBond · Accepted Answer · 2013-06-18 18:45:16Z

DATE and VARCHAR are not compatible when using '+'. Therefore you must convert the DATE to a VARCHAR to be able to concatenate them.
Some of your tags do not match, e.g, you have WorkPhone/ instead of /WorkPhone on the closing tag.
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>'

Collectives™ on Stack Overflow

Create XML from the input parameter in stored procedure

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related