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 (6)

  1. Bill says:

    Sushil:

    I got everything here working this time. I had the weirdest thing happen to. I ported that code that woudln’t compile under to full framework to the Compact Framework and even though Xml doesn’t appear to be a valid SqlCe type – it compiles fine (blows up on execution but that’s to be expected). I have the same using/includes and all. Very weird.

    Also, what’ the deal with Passing it (the xml param) in as the top most node? I understand ‘why’ you’d need to do it, but the insertion mechanism if you don’t do it – it seems like it’s arbitrary in that I can’t seem to get it to put in the expected ‘partial’ part when playing with it. I guess it’s one of those "then don’t do it that way" issues but i was wondering if there is a predictable way to do insertions there w/out it being at the topmost node?

  2. Sushil says:

    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.

  3. Bill says:

    Got it working! Thanks man!

  4. Sushil says:

    You are welcome. Thanks for your interest with the ADO.Net features.