How to make a Stored Procedure that takes in XML and uses that xml as an Update + call this stored procedure with ado.net?

Question

Hi

I am using ms sql server 2005 and I want to do a mass update. I am thinking that I might be able to do it with sending an xml document to a stored procedure.

So I seen many examples on how to do it for insert

CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData XML)
AS 
   INSERT INTO 
      dbo.UserTable(CreateDate)
      SELECT
         @UpdatedProdData.value('(/ArrayOfUserTable/UserTable/CreateDate)[1]', 'DATETIME')

But I am not sure how it would look like for an update.

I am also unsure how do I pass in the xml through ado.net? Do I pass it as a string through a parameter or what?

I know sqlDataApater has a batch update method but I am using linq to sql. So I rather keep using it. So if this works I would be able to grab all records with linq to sql and have them as objects. Then manipulate the objects and use xml seralization.

Finally I could just use ado.net simple to send the xml to the server. This might be slower then the sqlDataAdapter but I am willing to take that hit if I can keep using objects.

Edit

Ok I have this so far

This is my XML

<?xml version="1.0" encoding="utf-16"?>
<ArrayOfUserTable xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;
  <UserTable>
    <CreateDate>2011-05-21T11:04:55.0584669-07:00</CreateDate>
    <id>0</id>
    <AnotherField>false</AnotherField>
  </UserTable>
  <UserTable>
    <CreateDate>2015-05-21T11:04:55.061467-07:00</CreateDate>
    <id>0</id>
    <AnotherField>true</AnotherField>
  </UserTable>
</ArrayOfUserTable>

Two problems occurs with this the first one is

XML parsing: line 1, character 39, unable to switch the encoding

Second problem is with dates.

Conversion failed when converting datetime from character string.

Here is my C# code.

using (TestDataContext db = new TestDataContext())
{
   UserTable[] testRecords = new UserTable[2];
   for (int count = 0; count < 2; count++)
   {
      UserTable testRecord = new UserTable();

      if (count == 1)
      {
         testRecord.CreateDate = DateTime.Now.AddYears(5);
         testRecord.AnotherField = true;
      }
      else
      {
         testRecord.CreateDate = DateTime.Now.AddYears(1);
         testRecord.AnotherField = false;
      }

      testRecords[count] = testRecord;
  }

  StringBuilder sBuilder = new StringBuilder();
  System.IO.StringWriter sWriter = new System.IO.StringWriter(sBuilder);
  XmlSerializer serializer = new XmlSerializer(typeof(UserTable[]));
  serializer.Serialize(sWriter, testRecords);             

  using (SqlConnection con = new SqlConnection(connectionString))
  {
      string sprocName = "spTEST_UpdateTEST_TEST";

      using (SqlCommand cmd = new SqlCommand(sprocName, con))
      {
         cmd.CommandType = CommandType.StoredProcedure;

         cmd.CommandType = System.Data.CommandType.StoredProcedure;

         SqlParameter param1 = new SqlParameter("@UpdatedProdData", SqlDbType.VarChar, int.MaxValue);
         param1.Value = sBuilder.ToString();
         cmd.Parameters.Add(param1);

         con.Open();
         int result = cmd.ExecuteNonQuery();
         con.Close();
      }
   }
}

So to get around those 2 issues I just hand coded a small xml file that did not have the xml tag on top of it and only had MM/DD/YYYY for all dates to make that happy.

But it still does not work

USE [Test]
GO
/****** Object:  StoredProcedure [dbo].[spTEST_UpdateTEST_TEST]    Script Date: 05/21/2010 11:10:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spTEST_UpdateTEST_TEST](@UpdatedProdData XML)
AS 
   UPDATE dbo.UserTable
   SET CreateDate =  @UpdatedProdData.value('(/ArrayOfUserTable/UserTable/CreateDate)[1]', 'DATETIME')
   WHERE AnotherField =  @UpdatedProdData.value('(/ArrayOfUserTable/UserTable/AnotherField)[1]', 'bit')

This does not even update any records. Also I still think this can only handle one record so I am not sure how to alter it to update many records.

Answer 1

To call the stored proc from straight ADO.NET, you'd use the standard ADO.NET stuff as any programming book on .NET data access or ADO.NET tutorial (just Google for that!) will teach you:

using(SqlConnection con = new SqlConnection(your-connection-string-here))
{
     string sprocName = "spTEST_InsertXMLTEST_TEST";

     using(SqlCommand cmd = new SqlCommand(sprocName, con))
     {
         cmd.CommandType = CommandType.StoredProcedure;

         cmd.CommandType = System.Data.CommandType.StoredProcedure;

         SqlParameter param1 = new SqlParameter("@UpdatedProdData", SqlDbType.VarChar, int.MaxValue);
         param1.Value = YourXmlValueHere;
         cmd.Parameters.Add(param1);

         con.Open();
         int result = cmd.ExecuteNonQuery();
         con.Close();
     }    
}

Of course, you might want to wrap this into a try...catch block for exception handling and so forth - but that's basically the code you'd need to call that stored proc using straight ADO.NET.

UPDATE: in order to update your table from the XML, you should check out the .nodes() function in XQuery and write your update statement something like this:

UPDATE 
   dbo.UserTable
SET 
   CreateDate =  tbl.UPD.value('(CreateDate)[1]', 'DATETIME')
FROM
    @UpdatedProdData.nodes('/ArrayOfUserTable/UserTable') AS tbl(UPD)
WHERE 
    AnotherField =  tbl.UPD.value('(AnotherField)[1]', 'bit')

Basically, you're shredding your XML into a "virtual" table called tbl(UPD) - each entry of an <UserTable> tag now is a "row" in that virtual table (thus you can handle many rows), and you grab data from that virtual row to update your base table.

For a really good introduction to SQL-XML XQuery in SQL Server 2005 and up, check out this article on 15 Seconds - it's helped me tremendously to get a grasp on what is possible with XQuery, and how to do it in SQL Server's XQuery implementation.

Answer 2

I think you could get the XML into temp table and then use it into update part of the stored procedure. Not sure if that answers the questions though!

Answer 3

for that pass the XMLDOC1 as your parameter in procedure. in you .net code write dataset.Writexml and that will give to string variable and pass that string to the procedure as parameter. below is the example of how you get data in procedure. @XMLDoc1 as text

DECLARE @idoc1 as int

EXEC sp_xml_preparedocument @idoc1 OUTPUT, @XMLDoc1

Select * into #TableName
FROM OPENXML(@idoc1,'/NewDataSet/Tablename',2)
WITH (structure of Table like below) (PrefDetailID int
,PrefID int )

--select * from #TableName

EXEC sp_xml_removedocument @idoc1
now you got all your data in the #TableName and manipulte as you like in procedure do any thing insert or update or check below link this my own blog http://sqlmca.wordpress.com/2009/07/29/how-to-get-data-from-dataset-into-sqlserver-table-by-using-openxml-method/