9

How would you construct and pass XML as an argument to a stored procedure on an MS SQL 2005 server? And how would you INSERT the XML into a table?

The data is in the form of key/value pairs:

[
    0: [key, value],
    1: [key, value],
    2: [key, value]
]
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
cllpse
  • 21,396
  • 37
  • 131
  • 170

2 Answers2

17

Here's one example:

/* Create the stored procedure */
create procedure ParseXML (@InputXML xml)
as
begin
    declare @MyTable table (
        id int,
        value int
    )

    insert into @MyTable 
        (id, value)
        select Row.id.value('@id','int'), Row.id.value('@value','int') 
            from @InputXML.nodes('/Rows/Row') as Row(id)        

    select id, value
        from @MyTable
end
go

/* Create the XML Parameter */
declare @XMLParam xml
set @XMLParam = '<Rows>
                     <Row id="1" value="100" />
                     <Row id="2" value="200" />
                     <Row id="3" value="300" />
                 </Rows>'

/* Call the stored procedure with the XML Parameter */
exec ParseXML @InputXML = @XMLParam

/* Clean up - Drop the procedure */
drop procedure ParseXML
go
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
0

I sent a comma separated string of ids of users by ajax post request to action method.

 public ActionResult EnableSelectedUser(string userIds)
        {
            List<int> users = new List<int>();

I converted string of comma separated ids to list.

            var userList = userIds.TrimEnd(',').Split(',').ToList();

        foreach (var u in userList)
        {
            var data = new NotificationUsers()
            {
                UserId = Int32.Parse(u)
            };
            users.Add(data);
        }

Converted list to xml

            string xml = UBConvert.ListToXML(users, "DS", "DT");

Method that convert list to xml

 public static string ListToXML(object list, string dataSetName, string dataTableName, 
 StringCollection fieldNames)
        {
            if (list == null)
            {
                throw new ArgumentNullException("list");
            }

            var ds = new DataSet { Locale = CultureInfo.InvariantCulture };
            ds.Tables.Add(GenericListToDataTable(list, fieldNames));
            ds.DataSetName = dataSetName;
            ds.Tables[0].TableName = dataTableName;

            return ds.GetXml();
        }

Pass xml as parameter to Store procedure

 Parameter[] lstParams = new Parameter[]
                                    {
                                        new Parameter("@CurrentUserId", currentUserId),
                                        new Parameter("@Xml", xml)
                                    };
            return UBConvert.ToInt32(DataBaseHelper.ExecuteScalar("Admin_EnableBulkUser", lstParams));

Store Procedure that accept xml as parameter

    
CREATE PROCEDURE [dbo].[Admin_EnableBulkUser]      
(      
 @CurrentUserId INT = 0,      
 @Xml xml      
)      
AS      
BEGIN      
 DECLARE @Result INT = -1      
 DECLARE @Users table (UserId int, RowId int)      
    
      
 INSERT INTO @Users(UserId)      
 Select       
 T.Item.value('UserId[1]','int')      
 from @Xml.nodes('//DS/DT') AS T(Item)      
      
 BEGIN TRY      
  BEGIN TRAN      
      
   UPDATE dbo.[User]      
   SET  UserStatus = 1,      
     DeletedBy = @CurrentUserId,      
     DeletedOn = GETUTCDATE(),      
     SyncStatus = 0      
   FROM dbo.[User] u INNER JOIN @Users us on u.UserId = us.UserId      
      
  set @Result =1    
  select @Result;    
    
  COMMIT      
  SELECT @Result      
 END TRY      
      
 BEGIN CATCH      
  ROLLBACK      

    Select  @Result =0;    
 
 END CATCH      
      
END
Tayyeb
  • 127
  • 7