Store XML data into database using stored procedure :

XML variables in SQL Server 2005 make it easy to "shred" XML strings into relational data. The main new methods you'll need to use are value() and nodes() which allow us to select values from XML documents.

DECLARE @productIds xml
SET @productIds ='<Products><id>3</id><id>6</id><id>15</id></Products>'

SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)

Which gives us the following three rows:
3
6
15

Here's a proc which takes a single XML parameter. We first declare a table variable (@Products) and load the XML values into it. Once that's done, we can join against the @Products table as if it were any other table in the database.

Alter PROCEDURE SelectByIdList(@productIds xml) AS
begin
DECLARE @Products TABLE (ID int)

INSERT INTO @Products (ID) SELECT ParamValues.ID.value('.','VARCHAR(20)')
FROM @productIds.nodes('/Products/id') as ParamValues(ID)
select * from @Products
end

To test the stored procedure run the below command:

     EXEC SelectByIdList @productIds='<Products><id>3</id><id>6</id><id>15</id></Products>'

In the page:
//To create a xml file write the below code

public static string BuildXmlString(string xmlRootName, string[] values)
{
    StringBuilder xmlString = new StringBuilder();
    xmlString.AppendFormat("<{0}>", xmlRootName);
    for (int i = 0; i < values.Length; i++)
    {
    xmlString.AppendFormat("<value>{0}</value>", values[i]);
    }
    xmlString.AppendFormat("</{0}>", xmlRootName);
    return xmlString.ToString();
}