Valid for Environment: .NET 1.1, .NET 2.0, SQL Server 2000 or above
SQL Server 2000 supports XML. SELECT * FROM table FOR XML AUTO. Syntax sounds familiar, right? Yes, SQL Server 2000 supports XML. It not only supports returning the data in XML format, it also supports reading the XML string and parsing it. Before going to the implementation of Multiple Insert using this approach. To understand it a little bit, copy the code below and execute it in SQL Query Analyzer SQL Window :
DECLARE @intDocHandle int
DECLARE @xmlString varchar(4000)
SET @xmlString ='
< root>
< person PersonId="1" PersonName="AA"/>
< person PersonId="2" PersonName="BB"/>
< person PersonId="3" PersonName="CC"/>
< person PersonId="4" PersonName="DD"/>
< /root> '
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @xmlString
SELECT * FROM OPENXML(@intDocHandle,
'/root/person')
WITH
( PersonId INT,
PersonName VARCHAR(100)
)
-- Remove the internal representation.
exec sp_xml_removedocument @intDocHandle
I am leaving further interpretation and understanding part up to you. It is quite easy to observe that it revolves around two important stored procs : sp_xml_preparedocument, sp_xml_removedocument and a key word OPENXML Let us now see how can we exploit this for Multiple Insert Scenario.
First of all create a simple Person Table in the database as below :
CREATE TABLE Person
(
PersonId INT PRIMARY KEY,
PersonName VARCHAR(100)
)
With the Logic mentioned above, the code for desired Stored Procedure looks like:
CREATE PROCEDURE sp_InsertByXML ( @strXML VARCHAR(4000) )
AS
BEGIN
DECLARE @intDocHandle int
--Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @intDocHandle OUTPUT, @strXML
INSERT INTO Person ( PersonId, PersonName )
SELECT * FROM OPENXML(@intDocHandle,
'/PersonData/Person', 2)
WITH
( PersonId INT,
PersonName VARCHAR(100)
)
-- Remove the internal representation.
EXEC sp_xml_removedocument @intDocHandle
END
Now we need to form the XML at front end, which we can pass to this stored proc. I am sure, there can be various ways to do it. You can form by concatenating and forming XML or by using XMLDocument object of System.XML namespace and get the XML string out of it. Since most of times we play around DataSet and DataTables, I chose to get the XML out from the DataSet. First of all get the desired data in DataSet object. If you have trouble forming DataSet at runtime, refer to the “private string GetXml()” method in the sample code attached and then use the following code to get the string out of it :
System.IO.StringWriter sw = new System.IO.StringWriter ( );
dsPersonData.WriteXml (sw);
string strXml = sw.ToString();
With this I have the desired XML string. Now only job left is to call the stored procedure from my front end code, which is as follows:
private void btnInsertByXMLInput_Click(object sender, System.EventArgs e)
{
string strXml = GetXml();
SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("sp_InsertByXML", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@strXML",SqlDbType.VarChar, 4000);
command.Parameters[0].Value = strXml;
connection.Open();
int recordsInserted = command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("Number of records affected : " + recordsInserted.ToString());
}
It also returns the message “Number of records affected : 4 “. Multiple records inserted, and Mission Accomplished yet again.
Key Notes:
1. XML is Case Sensitive. For example, in the OPENXML statement ‘/root/person’ is not equal to ‘/ROOT/person’.
2. You can form the Attribute Centric as well as Element Centric XML. In the code above, it is Element Centric XML thus we have “2” in OPEN XML syntax, else default is 1 which is used for Attribute Centric XML
3. In ADO.NET 2.0, you can get the XML out of DataTable itself. In such case modify the XPath in OPENXML appropriately.
4. SQL Server 2000 supports XML processing, but in SQL Server 2005 we have xml as datatype. It has got lot more ways to support DML and DDL for xml. Choose as per your need.
References:
1. Books Online for details of sp_xml_preparedocument, sp_xml_removedocument, OPENXML
|
| Author: Sebastian 13 Jun 2008 | Member Level: Gold Points : 1 |
I was looking for the details based on this topic.Thanks for sharing it with me.
|
| Author: Jessie 04 Jul 2008 | Member Level: Gold Points : 0 |
Excellent
|