Send a XML to a sp for insertion

I have开发者_StackOverflow a collection of myClass. I want to send it to a sp using XML. I dont know how to convert a collection to a xml. After converting the collection to a xml, I want to send it to a stored procedure for bulk insertion

Please help

---

You can serialize your class to XML using the XmlSerializer, then pass that XML value into a stored procedure and then in the stored procedure iterate through the nodes. Another option to pass in multiple rows of data besides XML would be to use Table-Valued Parameters .

The XML approach would look something like this:

MyClass myClass = new MyClass();

XmlSerializer serializer = new XmlSerializer(typeof(MyClass));

XmlWriterSettings settings = new XmlWriterSettings()
{
    Encoding = new UnicodeEncoding(false, false)
};

StringBuilder xml = new StringBuilder();

using (XmlWriter xw = XmlWriter.Create(xml, settings))
{
    serializer.Serialize(xw, myClass);
}

...

SqlCommand cmd = new SqlCommand()
{
    CommandText = "InsertMyClass",
    CommandType = CommandType.StoredProcedure
};

SqlParameter sqlParam = new SqlParameter()
{
    ParameterName = "@x",
    SqlDbType = SqlDbType.Xml,
    Value = xml.ToString()
};

cmd.Parameters.Add(sqlParam);

int count = cmd.ExecuteNonQuery();

For the sake of an example, assume that your XML looks like this:

<MyClass>
  <Item>
    <ID>1</ID>
    <Property1>prop 1</Property1>
    <Property2>prop 2</Property2>
  </Item>
</MyClass>

Then a stored procedure could do the inserts:

CREATE PROCEDURE InsertMyClass
( 
    @x XML 
) 
AS
BEGIN

  INSERT INTO table (ID, Prop1, Prop2)
  SELECT 
      CAST(CAST(r.i.query('./ID/text()') AS VARCHAR(20)) AS INT) ,
      CAST(r.i.query('./Property1/text()') AS VARCHAR(20)),
      CAST(r.i.query('./Property2/text()') AS VARCHAR(20))
  FROM   
      @x.nodes('/MyClass/Item') R(i)

END

---

You can use XmlSerializer from serializing classes to xml. You can control serialization schema to some extent by use of attributes - see the MSDN link for more information.

On Sql Server side, you may use OPENXML for converting xml document to a row set that you can use to insert into tables. See this (2000 version) and this (2008 version) for info about OpenXml - both links contains example that should get you started.

Lastly, Bulk Insert from XML data is possible but that would typically insert data from file and I doubt if it fits into your scenario.