Convert Text to XML Data in SQL Server.

Convert Text to XML Data in SQL Server.

In the following Stored Procedure we can Use XML Data as Follows.
Send XML Data to Stored Procedure as "Text" Data Type.
In SP Convert this "Text" (XML Text)  to "XML" Data
Read and Fetch the XML Data into Table.
Use the Cursor to Traverse through Data.

Code :

/**************************************************************************
 
-----------------------  
Developers Note   
-----------------------  
Object          : StoredProcedure [dbo].[canon_UpdateValidUserFromXML]      
Script Date     : 19/11/2012   
Developed By    : Rohit Shram  
Used For        : Update Valid  Users 
Implementaion   :   
    SELECT *  FROM [USER] where ID in ( 80309 ,80310)
    GO
    UpdateValidUserFromXML
    '<UsersList>
        <Users>
            <ID>80309</ID>
            <UserName>KARL TYLER</UserName>
            <Email>CRAIG.WASHINGTON80309@testmail.com</Email>
        </Users>
        <Users>
            <ID>80310</ID>
            <UserName>CHAD MARTIN</UserName>
            <Email>LEWIS.CUNNINGHAM80310@testmail.com</Email>
        </Users>
    </UsersList>',
    'File1234',
    80311
    GO
    SELECT *  FROM [USER] where ID in ( 80309 ,80310)

*************************************************************************************/  

CREATE PROCEDURE [dbo].[UpdateValidUserFromXML] 

    @UsersXml TEXT  
--'<UsersList><Users><ID>12</ID><UserName>KAYR</UserName><Email>CRAI@test.com</Email></Users></UsersList>' 
AS  
BEGIN  
    SET NOCOUNT ON;  
    
    BEGIN TRANSACTION Update
    
    BEGIN TRY
    
    DECLARE @idoc INT
    DECLARE @ID INT                  
    DECLARE @UserName NVARCHAR(500)                
    DECLARE @Email NVARCHAR(500)                
 
    -- Used to Keep ErrorList for all Users Data as Table
    DECLARE @UsersErrorList TABLE                  
    (                  
        ID INT,                 
        Name NVARCHAR(500),                
        Email NVARCHAR(500),                 
        Error NVARCHAR(MAX)       
    ) 
 
    -- Used to Keep XML Data as Table
    DECLARE @UsersList TABLE                  
    (                  
        ID INT,                 
        UserName NVARCHAR(500),                
        Email NVARCHAR(500)                
    ) 

    -- Prepare XML Doc
    EXEC sp_xml_preparedocument @idoc OUTPUT, @UsersXml                  
          
    -- Insert into Table Variable From XML
    INSERT @UsersList                
    (                 
        ID,                
        UserName,                 
        Email                
    )                  
    SELECT                   
        ID,                
        UserName,                 
        Email               
       
    FROM                  
    OPENXML (@idoc, '/UsersList/Users')                  
    WITH                 
    (                  
        ID INT 'ID',                 
        UserName NVARCHAR(500) 'UserName',                
        Email NVARCHAR(500) 'Email'               
    )                  
           
    -- Remove XML Do after getting Data
    EXEC sp_xml_removedocument @idoc           
   
    -- Declare Cursor
    DECLARE Cursor CURSOR FOR                  
    SELECT                  
    ID,                
    UserName,                 
    Email                
    FROM @UsersList                   
         
    -- Open Cursor
    OPEN Cursor                  
         
    -- Fetch Cursor
    FETCH NEXT FROM Cursor INTO                   
    @ID,                  
    @UserName,                  
    @Email       
         
    -- Traverse through Data                
    WHILE @@FETCH_STATUS = 0                
    BEGIN  
  
        -- Update User's Name and Email    
        UPDATE [USER] 
        SET Email = @Email, UserName = @UserName 
        WHERE ID = @ID 
  
        -- Fetch Cursor
        FETCH NEXT FROM Cursor INTO                   
        @ID,                  
        @UserName,                  
        @Email                 
                  
    END  -- WHILE @@FETCH_STATUS = 0                
                 
        CLOSE Cursor                  
        DEALLOCATE Cursor 
 
    END TRY
    BEGIN CATCH

        -- Rollback Transaction If any Transaction Occured
        IF @@ROWCOUNT > 0
            ROLLBACK TRANSACTION Update
  
        -- Insert Into Temp table to Keep Error Details for all Users
        INSERT INTO @UsersErrorList
        VALUES ( @ID , @UserName , @Email , ERROR_MESSAGE())

    END CATCH
 
    -- Returns Error ResultSet
    SELECT * FROM @UsersErrorList;  

    -- Commit Transaction If any Transaction Occured
    IF @@TRANCOUNT > 0
        COMMIT TRANSACTION Update
    
    SET NOCOUNT OFF;  

END