locked
store xml in SQL Server 2008 from C#

    Question

  • What is the easiest way to save down an XML /XSD DataSet in SQL Server 2008 from C#?

    I want to make as little effort as possible to update, insert and select XML stored in the database.

    Ideas on the top of my head:

    1. Use DataSets and hold a copy in-memory of what I want to store down.

    2. Can I create a stored procedure that takes in an XML string and then inerts it into the database?

    3. Use SOAP and put a server in-between that manages the persistent data.

    Lars


     


     

    Saturday, February 19, 2011 3:57 PM

Answers

  • This example will store XML as the SQL XML data type.  You can retrieve the date in your code as an XML string blob (SqlCommand.ExecuteReader) or using an XmlReader (SqlCommand.ExecuteXmlReader or SqlDataReader.CreateReader).  Fopr example:

          command.CommandType = CommandType.Text;
          command.CommandText = "SELECT XmlColumn FROM dbo.XMLTable WHERE XmlTableID = XmlTableID;";
          command.Parameters.Add("@XMLTableID", SqlDbType.Int);
          command.Parameters["@XMLTableID"].Value = 1;
          var reader = command.ExecuteXmlReader();
          while(reader.Read())
          {
            System.Diagnostics.Trace.WriteLine(reader.NodeType.ToString());
          }
    

    I should add that if you plan on just using a string blob in your application, you could use varchar(max) or nvarchar(max) instead.  The advantages of the XML type is that it ensures only XML documents and fragments can be stored, allows you to query in T-SQL using XQuery and XML data type methods, and enables you to easily process XML data in your code using an XmlReader (e.g. deserialize objects).

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Sunday, February 20, 2011 8:42 PM
  • Regarding #2, you can generally store XML in a SQL Server table using normal CRUD procs.  The example below uses an XmlDocument as the source but XML from any source may be used. 

    CREATE TABLE dbo.XMLTable(
      XMLTableID int NOT NULL IDENTITY(1, 1) CONSTRAINT PK_XMLTable PRIMARY KEY
      ,XMLColumn xml
    	);
    GO
    
    CREATE PROC dbo.InsertXML
    	@XmlParameter XML
    AS
    INSERT INTO dbo.XMLTable (XMLColumn) VALUES(@XmlParameter);
    SELECT CAST(SCOPE_IDENTITY() AS int) AS XMLTableID;
    GO
    
    var xmlObject = new XmlDocument();
    xmlObject.LoadXml("<Root><Clild>child text</Clild></Root>");
    var connection = new SqlConnection(@"Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI");
    connection.Open();
    var command = new SqlCommand("dbo.InsertXML", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@XMLParameter", SqlDbType.Xml);
    command.Parameters["@XMLParameter"].Value = xmlObject.OuterXml;
    int XmlTableID = (int)command.ExecuteScalar();
    connection.Close();
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, February 19, 2011 6:20 PM

All replies

  • Regarding #2, you can generally store XML in a SQL Server table using normal CRUD procs.  The example below uses an XmlDocument as the source but XML from any source may be used. 

    CREATE TABLE dbo.XMLTable(
      XMLTableID int NOT NULL IDENTITY(1, 1) CONSTRAINT PK_XMLTable PRIMARY KEY
      ,XMLColumn xml
    	);
    GO
    
    CREATE PROC dbo.InsertXML
    	@XmlParameter XML
    AS
    INSERT INTO dbo.XMLTable (XMLColumn) VALUES(@XmlParameter);
    SELECT CAST(SCOPE_IDENTITY() AS int) AS XMLTableID;
    GO
    
    var xmlObject = new XmlDocument();
    xmlObject.LoadXml("<Root><Clild>child text</Clild></Root>");
    var connection = new SqlConnection(@"Data Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI");
    connection.Open();
    var command = new SqlCommand("dbo.InsertXML", connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.Add("@XMLParameter", SqlDbType.Xml);
    command.Parameters["@XMLParameter"].Value = xmlObject.OuterXml;
    int XmlTableID = (int)command.ExecuteScalar();
    connection.Close();
    

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, February 19, 2011 6:20 PM
  • Thank you Dan, so this will allow me to store XML as blobs on the database?

    What is I later on what to select rows from the table?

    Sunday, February 20, 2011 7:06 PM
  • This example will store XML as the SQL XML data type.  You can retrieve the date in your code as an XML string blob (SqlCommand.ExecuteReader) or using an XmlReader (SqlCommand.ExecuteXmlReader or SqlDataReader.CreateReader).  Fopr example:

          command.CommandType = CommandType.Text;
          command.CommandText = "SELECT XmlColumn FROM dbo.XMLTable WHERE XmlTableID = XmlTableID;";
          command.Parameters.Add("@XMLTableID", SqlDbType.Int);
          command.Parameters["@XMLTableID"].Value = 1;
          var reader = command.ExecuteXmlReader();
          while(reader.Read())
          {
            System.Diagnostics.Trace.WriteLine(reader.NodeType.ToString());
          }
    

    I should add that if you plan on just using a string blob in your application, you could use varchar(max) or nvarchar(max) instead.  The advantages of the XML type is that it ensures only XML documents and fragments can be stored, allows you to query in T-SQL using XQuery and XML data type methods, and enables you to easily process XML data in your code using an XmlReader (e.g. deserialize objects).

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Sunday, February 20, 2011 8:42 PM
  • This example will store XML as the SQL XML data type.  You can retrieve the date in your code as an XML string blob (SqlCommand.ExecuteReader) or using an XmlReader (SqlCommand.ExecuteXmlReader or SqlDataReader.CreateReader).  Fopr example:

       command.CommandType = CommandType.Text;
    
       command.CommandText = "SELECT XmlColumn FROM dbo.XMLTable WHERE XmlTableID = XmlTableID;";
    
       command.Parameters.Add("@XMLTableID", SqlDbType.Int);
    
       command.Parameters["@XMLTableID"].Value = 1;
    
       var reader = command.ExecuteXmlReader();
    
       while(reader.Read())
    
       {
    
        System.Diagnostics.Trace.WriteLine(reader.NodeType.ToString());
    
       }
    Hi Duzman
    I am also expirancing similar problem on XML
    mine is different scenario because I am able to read from my SQLServer Database nicely but the main issue is how to update ids from column eg like updating name,ID and description.Let me try to give you clear picture of what I am trying to do: this is XML Column from Table:

    <

    setting

    >

    <

    OccupationsVersion Id="27"

    />

    <

    ConstructionTypesVersion Id="8"

    />

    <

    IndustryTypesVersion Id="8"

    />

    <

    LocationsVersion Id="15"

    />

    <

    SurveyStatusVersion Id="1"

    />

    <

    SurveyTemplatesVersion Id="96"

    />

    <

    BusinessUnitsVersion Id="8"

    />

    <

    ReportsVersion Id="4"

    />

    <

    BranchesVersion Id="11"

    />

    <

    ManagersVersion Id="29"

    />

    <

    InsuranceMajorClassesVersion Id="68"

    />

    <

    ContactTypesVersion Id="2"

    />

    <

    AddressPurposesVersion Id="1"

    />

    <

    AddressTypesVersion Id="1"

    />

    <

    RecommendationPrioritiesVersion Id="1"

    />

    <

    FireBrigadeAreaRatingVersion Id="1"

    />

    <

    RecommendationTemplateVersion Id="1"

    />

    <

    SystemUsersVersion Id="44"

    />

    <

    SurveyPrioritiesVersion Id="1"

    />

    <

    TaskPriorityVersion Id="1"

    />

    <

    SurveyTypesVersion Id="160"

    />

    <

    TaskTypesVersion Id="4"

    />

    <

    TaskStatussVersion Id="4"

    />

    <

    TaskTypeVersion Id="5"

    />

    <

    TaskStatusVersion Id="2"

    />

    </

    setting

    >

    I want to update SystemUsersVersion Id="44"  to 45 ,TaskTypeVersion Id="5" to TaskTypeVersion Id="6" I am still newbie in C#

    I should add that if you plan on just using a string blob in your application, you could use varchar(max) or nvarchar(max) instead.  The advantages of the XML type is that it ensures only XML documents and fragments can be stored, allows you to query in T-SQL using XQuery and XML data type methods, and enables you to easily process XML data in your code using an XmlReader (e.g. deserialize objects).

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Friday, March 18, 2011 10:45 AM