Insert multiple rows of Data in SQL server using XML

The XML must be like:

<surveys>
    <survey SurveyId="#SurveyId#"
            QuestionId="#QuestionId#"
            Answer="#Answer#"
            UserId="#UserId#"
            UserName="#UserName#"/>
    <survey SurveyId="#SurveyId#"
            QuestionId="#QuestionId#"
            Answer="#Answer#"
            UserId="#UserId#"
            UserName="#UserName#"/>
    <survey SurveyId="#SurveyId#"
            QuestionId="#QuestionId#"
            Answer="#Answer#"
            UserId="#UserId#"
            UserName="#UserName#"/>
</surveys>

This XML must be passed as argument to stored procedure. Create a stored procedure and pass the XML document as a parameter. In the example below @XMLDOC is a string variable with the actual XML passed to it.

DECLARE @xml_hndl INT

--prepare the XML Document by executing a system stored procedure
EXEC SP_XML_PREPAREDOCUMENT @xml_hndl OUTPUT, @XMLDOC

BEGIN TRY
    BEGIN TRANSACTION
        INSERT INTO SurveyResponse

        SELECT SurveyId, 
               QuestionId, 
               Answer, 
               UserId, 
               UserName, 
               GETDATE(), 
               GETDATE()
        FROM
        OPENXML(@xml_hndl, '/surveys/survey', 1)
        WITH
         (
             SurveyId BIGINT '@SurveyId',
             QuestionId BIGINT '@QuestionId',
             Answer INT '@Answer',
             UserId VARCHAR(100) '@UserId',
             UserName VARCHAR(200) '@UserName'
         )

        SET @return = 1
    COMMIT
END TRY
BEGIN CATCH
    ROLLBACK
    SET @return = 0
END CATCH

Omniture Tutorial

Good tutorial for understanding how Omniture works.

http://www.slideshare.net/nablerwa/an-introduction-to-omniture