Insert or Update rows Via XML in SQL Server (Bulk)

Hi I Can not seem to wrap my head around how to parse an Xml document and the decide if it needs inserting or updating.

I Have an existing procedure that loks like this:

CREATE PROCEDURE [dbo].[SavePrice]
-- Add the parameters for the stored procedure here
@Groupid varchar(150), @Price varchar(150), @CustomerID varchar(150)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

IF (EXISTS(SELECT * FROM dbo.Prices WHERE groupid = @Groupid AND CustomerID = @CustomerID))
    UPDATE 
        dbo.Prices
    SET 
        price = @Price
    WHERE
        groupid = @Groupid 
    AND
        CustomerID = @CustomerID;

ELSE
    INSERT INTO 
        dbo.Prices
    (
        price,
        groupid,
        customerid
    )
    VALUES
    (
        @price,
        @groupid,
        @customerid
    );
End

Now I Would like this to run this in bulk preferably from an XML Argument

XML

<ArrayOfPrice xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Price>
  <Undergroup>111</Undergroup> 
  <Gain>10</Gain> 
  <CustomerID>14001099</CustomerID> 
  </Price>
  </ArrayOfPrice>

Something like this

foreach(Row r in XmlDoc)
{    
    Procedure(r)
}

I'm sorry but I really suck at SQL (which is why the above is written in pseudo C#), so I really hope you can help me. Any ideas?

Regards Kenneth

EDIT

The Solution was not to use XML to pass the bulk data, but a TableParameter

TableType

CREATE TYPE [dbo].[PriceDataType] As Table
(
--This type has structure similar to the DB table 
GroupID varchar(50) Not Null,
Price varchar(50) Not Null,
CustomerID varchar(50) Not Null
)

Stored Procedure

ALTER PROCEDURE [dbo].[SavePrice]
    -- Add the parameters for the stored procedure here
    @Dt PriceDataType READONLY
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

MERGE Prices AS Prices
    USING (SELECT * FROM @Dt) AS Source
    ON (Prices.GroupId = Source.GroupId) AND (Prices.CustomerId= Source.CustomerId)
    WHEN MATCHED THEN 
        UPDATE SET Prices.Price = Source.Price
    WHEN NOT MATCHED THEN   
        INSERT (Price, GroupId, CustomerId)
        VALUES (开发者_Python百科Source.Price, Source.GroupId, Source.CustomerId);
End

C#

        public void SavePrices(Price[] prices)
        {


            SqlCommand cmd = new SqlCommand("SavePrice", conn);
            cmd.CommandType = CommandType.StoredProcedure;

            conn.Open();


            cmd.Parameters.Add(new SqlParameter("@Dt", SqlDbType.Structured));
            cmd.Parameters["@Dt"].Value = GetTableFromList(prices);

            cmd.ExecuteScalar();


            conn.Close();
            cmd.Dispose();


        }


        private DataTable GetTableFromList(Price[] ps)
        {
            DataTable tbl = new DataTable("PriceDataType");
            tbl.Columns.Add("GroupID", typeof(string));
            tbl.Columns.Add("Price", typeof(string));
            tbl.Columns.Add("CustomerID", typeof(string));

            foreach (Price p in ps)
                tbl.Rows.Add(p.Undergroup, p.Gain, p.CustomerID);
            return tbl;
        }

Thanks for the help

---

Since you're using SQL Server 2008, instead of building your stored procedure with an if block you can utilize the MERGE statement to perform an INSERT or UPDATE depending on the existence of a record. There is MSDN documentation on utilizing the statement.

Edit:

Here is an example of what your MERGE statement could look like:

MERGE Prices AS Prices
    USING (SELECT @GroupId, @CustomerId) AS Source
    ON (Prices.GroupId = Source.GroupId) AND (Prices.CustomerId= Source.CustomerId)
    WHEN MATCHED THEN 
        UPDATE SET Prices.Price = @Price
    WHEN NOT MATCHED THEN   
        INSERT (Price, GroupId, CustomerId)
        VALUES (@Price, @GroupId, @CustomerId)

Also, if you want to truly insert your records in bulk, you could look into using a table valued parameter. If you have 5 "rows" from your XML document that you want to insert into your database, you can call your stored procedure 5 times by passing in the individual values (as you are currently doing). By using the table valued parameter you could call your stored procedure once by handing it a table parameter and then utilize that table in your stored procedure.