Retrieve XML column in a list within a class RRS feed

  • Question

  • User-309523270 posted

    Hi all,

    I have this database table structure JobsQueue:
    Table:  JobsQueue

    JobId int PK
    Status char(1)
    JobType char(1)
    XMLCriteria xml
    XMLResults xml

    XMLResults column contains this schema:

      <Statistics />
        <Message>Error Processing - Object reference not set to an instance of an object.</Message>
      <Items />

    I want to put the job id, and the messages tag in the XMLResults column in JobDisplay class:

    JobDisplay class

        public class JobDisplay
            public int JobId { get; set; }
            public List<string> MessagesList { get; set; }

    Any help is appreciated!



    Wednesday, October 14, 2015 1:50 PM


  • User2103319870 posted

    Create a stored procedure like below to read the values from XML column

    CREATE PROCEDURE uspGetErrorMessageDetails 
        --Add any paramters if you need to pass
        , x.m.value( '(text())[1]', 'varchar(max)' ) XMLMessages
        JobQueue xt
        outer apply xt.XMLResults.nodes( 'Results/Messages/Message' ) x(m)

    Above procedure read value of all records if you need for a particular row add a where condition

    I have modified the your class like given below because you have JOBID as Primary key and there wont be any duplicate Error message for a particular ID

     public class JobDisplay
                public int JobId { get; set; }
                public string MessagesList { get; set; }

    You can use the below code to read values from database and Populate the list

     //List to hold the Error Message Details
                List<JobDisplay> errorlist = new List<JobDisplay>();
                //Change the connection string name as per your data base
                using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["SampleConnectionString"].ConnectionString))
                    // Open the SqlConnection.
                    // The following code uses an SqlCommand based on the SqlConnection.
                    //Create the SQL Command object
                    using (SqlCommand cmd = new SqlCommand("uspGetErrorMessageDetails", cnn))
                        //Set the command Type to Stored Procedure
                        cmd.CommandType = CommandType.StoredProcedure;
                        //Read the values
                        using (SqlDataReader reader = cmd.ExecuteReader())
                            //read the data
                            while (reader.Read())
                                //Create object of class
                                JobDisplay objJobDisplay = new JobDisplay();
                                objJobDisplay.JobId = int.Parse(reader["JobID"].ToString());
                                objJobDisplay.MessagesList = reader["XMLMessages"].ToString();
                                //Add the value to list

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 14, 2015 3:44 PM