XML Data type in ADO.Net v2.0 - Part II
In this section we will look at ways ADO.Net v2.0 (Code named 'Whidbey') allows us to insert/delete/update Xml data values in a table containing Xml column in SQL Server 2005 (Code named 'Yukon'). With earlier versions of the .Net Framework (1.0 and 1.1) the System.Data.SqlClient namespace will consume and emit XML as String values.Because of this, it should not be a surprise that they would behave the same way with Whidbey. This was made so as to not break customers using this new datatype of the Server with older versions of the framework. Note: The examples below use the Customer table defined in the Part I of the blog.
Xml Values from DataReader: XML values in SqlDataReader are surfaced like any other type. The GetValue() method will get the Xml Value as String where as the GetSqlValue() method will get the value as an instance of SqlString. The following examples show both of the retrieval methods.
Get Value as XmlReader
using (SqlCommand Cmd = Conn.CreateCommand())
{
Cmd.CommandText= "SELECT OrderXml FROM Customer WHERE CustomerId = 2";
SqlDataReader Reader = Cmd.ExecuteReader();
if (Reader.Read())
{
string XmlValue = (string) Reader.GetValue(0); //Gets the XML value as string
}
}
Get Value as SqlXml: With ADO.Net 2.0, we have introduced a new class SqlXml. This new class adds SQL-specific feature like null semantics to the XML type. This is consistent with other SqlTypes (like SqlString for VARCHAR, SqlInt16 for Integer). This class allows the user to create an XmlReader (not to be confused with DataReader) on the SqlXml value using the SqlXml.CreateReader method. An example is shown below:
using (SqlCommand Cmd = Conn.CreateCommand())
{
Cmd.CommandText= "SELECT OrderXml FROM Customer WHERE CustomerId = 2";
SqlDataReader Reader = Cmd.ExecuteReader();
if (Reader.Read())
{
SqlXml SqlXmlValue = Reader.GetSqlXml(0);
PrintXmlReader(SqlXmlValue.CreateReader()); //Gets the XmlReader from SqlXml object and prints it using a Helper function
}
}
Note:
1. In the above examples, SqlClient Managed Provider internally converts the Binary formatted XML that it receives from the server into string representation.
2. To get the XmlReader object for the XML data type, use the SqlXml.CreateReader method.
Parameterized Xml Values
XML values can also be used in parameters. The value itself can be expressed as a string, an XmlReader-derived type instance or a SqlXml object. Examples below show how to set the parameters for each of these values.
Xml Value as String In this example, the string is directly used to set the Parameter's Value using (SqlConnection Conn = new SqlConnection (ConnectionString))
{
Conn.Open();
using (SqlCommand Cmd = Conn.CreateCommand())
{
//INSERT VALUES using PARAMS
XmlReader XmlValue = new XmlTextReader("Sample.Xml");
Cmd.CommandText = "INSERT INTO Customer VALUES (4,'Chris Andrews',@XmlFile)";
Cmd.Parameters.AddWithValue("@XmlFile", "<order><item><id>20</id><name>Widgets</name><units>3</units></item></order>");
Cmd.ExecuteNonQuery();
}
}
Xml Value as SqlXml
Just like other types, SQL-specific type for XML can be used to set the Parameter's value for XML. SqlXml ctor() takes XmlReader or a Stream. The following example creates an instance of XmlTextReader and then sets the value of the parameter. An instance of XmlTextReader is created by opening a file with name:"Sample.Xml".
using (SqlConnection Conn = new SqlConnection (ConnectionString))
{
Conn.Open();
using (SqlCommand Cmd = Conn.CreateCommand())
{
//INSERT VALUES using PARAMS
Cmd.CommandText = "INSERT INTO Customer VALUES (4,'Chris Andrews',@XmlFile)";
SqlXml SqlXmlValue = new SqlXml( new XmlTextReader ("SampleInfo.Xml")); // This passes the file SampleInfo.Xml as the parameter's value
Cmd.Parameters.AddWithValue("@XmlFile", SqlXmlValue);
Cmd.ExecuteNonQuery();
}
}
Note: You have to make sure that the XmlReader that is passed as an argument is on the top most node, else partial results will be inserted in the table, depending on the current position of the XmlReader.
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
Comments
- Anonymous
August 04, 2004
The comment has been removed - Anonymous
August 04, 2004
gggd - Anonymous
August 04, 2004
gggd - Anonymous
August 05, 2004
Bill, You should be successfully insert partial result. This ofcourse assumes that the partial results that you intend to insert is a valid XML too. Here is an example:
Consider that you want to add the XML "<node1> test </node1> <node2> this </node2>". Here is code snippet that reads the top most nodes and inserts the parital result ("<node2> this </node2>"):
XmlTextReader xmltextreader1 = new XmlTextReader("<node1> test </node1> <node2> this </node2>", XmlNodeType.Element, (XmlParserContext)null);
xmltextreader1.Read(); // this reads <node1>
xmltextreader1.Read(); // this reads test
xmltextreader1.Read(); // this reads </node1>
SqlParameter sqlparameter1 = sqlcommand1.ParameterCollection.Add("@Xml", SqlDbType.Xml);
SqlXml sqlxml1 = new SqlXml((XmlReader)xmltextreader1); //initialize with partial results
sqlparameter1.SqlValue = (Object)sqlxml1;
sqlcommand1.CommandText = "insert into test values (10,@Xml)n";
Int32 int324 = sqlcommand1.ExecuteNonQuery();
Now if you look at "SELECT * from test", you will see <node2> this </node2> inserted successfully. - Anonymous
August 10, 2004
Got it working! Thanks man! - Anonymous
August 10, 2004
You are welcome. Thanks for your interest with the ADO.Net features.