How To Add Xml Records to Sql Server

 Try the following code.
*/
-- Step 1: Create the following table Create Table Temp (partNo Varchar(50), error varchar(100))
Go
-- Step 2: Create the following procedure 
create procedure sp_UpdatePartInfo 
        @xmlData xmlas
begin
        insert  into dbo.temp (partNo, error)
        select  p.i.value('partNo[1]', 'varchar(50)'), p.i.value('error[1]', 'varchar(100)')
        from    @xmlData.nodes('data/partInfo/part') as p(i)
end
Go
-- Step 3: Use the following code in SQL query analyzerDeclare @xmlData xml
set @xmlData = '<data>
        <partInfo>
                <part>
                        <partNo>AA</partNo>
                        <error>None</error>
                </part>
                <part>
                        <partNo>BB</partNo>
                        <error>None</error>
                </part>
                <part>
                        <partNo>CC</partNo>
                        <error>Not Found</error>
                </part>
        </partInfo>
</data>'
Exec sp_UpdatePartInfo @xmlData

CASE2

-----------

Try this way:
Create the following tables:
CREATE TABLE Ord (
         OrderID     int identity(1,1)  PRIMARY KEY,
         CustomerID  varchar(5))
GO
CREATE TABLE Product (
         ProductID   int identity(1,1) PRIMARY KEY,
         ProductName varchar(20))
GO
CREATE TABLE OrderDetail (
       OrderID     int FOREIGN KEY REFERENCES Ord(OrderID),
       ProductID   int FOREIGN KEY REFERENCES Product(ProductID),
                   CONSTRAINT OD_key PRIMARY KEY (OrderID, ProductID))
GO

Save the following schema in a file (schema.xml):
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:annotation>
  <xsd:appinfo>
    <sql:relationship name="OrderOD"
          parent="Ord"
          parent-key="OrderID"
          child="OrderDetail"
          child-key="OrderID" />

    <sql:relationship name="ODProduct"
          parent="OrderDetail"
          parent-key="ProductID"
          child="Product"
          child-key="ProductID"
          inverse="true"/>
  </xsd:appinfo>
</xsd:annotation>

  <xsd:element name="Order" sql:relation="Ord"
                            sql:key-fields="OrderID" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="Product" sql:relation="Product"
                     sql:key-fields="ProductID"
                     sql:relationship="OrderOD ODProduct">
          <xsd:complexType>
             <xsd:attribute name="ProductID" type="xsd:int" />
             <xsd:attribute name="ProductName" type="xsd:string" />
          </xsd:complexType>
        </xsd:element>
     </xsd:sequence>
        <xsd:attribute name="OrderID"   type="xsd:integer" />
        <xsd:attribute name="CustomerID"   type="xsd:string" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>


Save the following sample XML document in a file (data.xml)：
<ROOT> 
  <Order OrderID="11" CustomerID="ALFKI">
    <Product ProductID="11" ProductName="Chai" />
    <Product ProductID="22" ProductName="Chang" />
  </Order>
  <Order OrderID="22" CustomerID="ANATR">
     <Product ProductID="33" ProductName="Aniseed Syrup" />
    <Product ProductID="44" ProductName="Gumbo Mix" />
  </Order>
</ROOT>

1.  Start Visual Studio.
   
   
    Create a C# console application.
   
   
    From the Project menu, select Add  Reference.
   
   
    In the COM tab, select Microsoft  SQLXML Bulkload 4.0 Type Library (xblkld4.dll) and click OK. You will see the Interop.SQLXMLBULKLOADLib assembly created in the  project.
   
   
    Replace the Main() method with the following code. Update the ConnectionString property and the file path to the schema and  data files. 
   
    
   [STAThread]
      static void Main(string[] args)
      {   
            try
            {
               SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
               objBL.ConnectionString = "Provider=sqloledb;server=server;database=databaseName;integrated security=SSPI";
               objBL.ErrorLogFile = "error.xml";
               objBL.KeepIdentity = false;
               objBL.Execute ("schema.xml","data.xml");
            }
            catch(Exception e)
            {
            Console.WriteLine(e.ToString());
            }
      }
   For more please see this:
   http://msdn.microsoft.com/en-us/library/ms171878.aspx