locked
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:

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

    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!

    Thanks,

    tinac99

    Wednesday, October 14, 2015 1:50 PM

Answers

  • 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
    AS 
    select
        xt.JobID
        , x.m.value( '(text())[1]', 'varchar(max)' ) XMLMessages
    from
        JobQueue xt
        outer apply xt.XMLResults.nodes( 'Results/Messages/Message' ) x(m)
    GO
    

    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.
                    cnn.Open();
                    // 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
                                errorlist.Add(objJobDisplay);
                            }
                        }
                    }
                }

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