Difficulty in reading XML values using XDocument

Question

I have an xml file as under

<ScriptFileNames>
  <SqlEye>
    <ScriptFile Name='_ws_CommandHistory_AllHistory.sql' Type='SP' SqlEyeAnalysisTime='00:00:01.7817594' FxCopAnalysisTime='00:00:00.2253670' FxCopWarningCount='0' SqlEyeWarningCount='2'>
          <SqlEyeWarnings>
            <SqlEyeWarning message='SD004: Check for existence object then Drop statement before create statement' />
            <SqlEyeWarning message='SP001: Set NoCount statement missing or it should be ON.' />
          </SqlEyeWarnings>
        </ScriptFile>
  </SqlEye>
</ScriptFileNames>

I want the output to be

FileName WarningMessage format

e.g.

_ws_CommandHistory_AllHistory.sql   SD004: Check for existence object then Drop statement before create statement
_ws_CommandHistory_AllHistory.sql   SP001: Set NoCount statement missing or it should be ON.

My attempt

string input = @"<ScriptFileNames>
  <SqlEye>
    <ScriptFile Name='_ws_CommandHistory_AllHistory.sql' Type='SP' SqlEyeAnalysisTime='00:00:01.7817594' FxCopAnalysisTime='00:00:00.2253670' FxCopWarningCount='0' SqlEyeWarningCount='2'>
          <SqlEyeWarnings>
            <SqlEyeWarning message='SD004: Check for existence object then Drop statement before create statement' />
            <SqlEyeWarning message='SP001: Set NoCount statement missing or it should be ON.' />
          </SqlEyeWarnings>
        </ScriptFile>
  </SqlEye>
</ScriptFileNames>";
XDocument doc = XDocument.Parse(input);
            XElement scriptFileNames = doc.Element("ScriptFileNames");

            var xx = (from x1 in scriptFileNames.Element("SqlEye").Elements("ScriptFile")
                      select new
                          {
                              Name = x1.Attribute("Name").Value                              
                          }).ToList();

I also have I more section

<SqlEyeRemarks>
        <SqlEyeRemark message='SD001: Set QuotedIdentifier ON statement is missing or order mismatch or it should be ON.' />
        <SqlEyeRemark message='SD002: Set AnsiiNullsOn ON statement is missing or order mismatch or it should be ON.' />
        <SqlEyeRemark message='SD009: Missing or order mismatch of Grant statement.' />
      </SqlEyeRemarks>

How can I get them along?

And what's the output? Note that your current code wouldn't compile (you're trying to parse x rather than input) and you should indent it more appropriately for readability. — Jon Skeet
– Jon Skeet, Commented Jun 5, 2013 at 11:15
desired output: FileName WarningMessage ..it will be in a collection as i was attempting — priyanka.sarkar
– priyanka.sarkar, Commented Jun 5, 2013 at 11:26
And the current output? (I notice that you've fixed the variable name, but the code is still horribly indented.) As a hint: you're currently ignoring the fact that you've got multiple SqlEyeWarning elements. You're just creating one entry per ScriptFile. — Jon Skeet
– Jon Skeet, Commented Jun 5, 2013 at 11:34
For every warning message the corresponding filename will come. — priyanka.sarkar
– priyanka.sarkar, Commented Jun 5, 2013 at 11:36
I don't think so - at the moment, I think you'll get a single entry per ScriptFile element... — Jon Skeet
– Jon Skeet, Commented Jun 5, 2013 at 11:38

Jon Skeet · Accepted Answer · 2013-06-05 11:37:18Z

Your current code is only iterating to the ScriptFile level. I suspect the simplest approach would be:

var warnings = doc.Descendants("SqlEyeWarning")
                  .Select(x => new {
                      FileName = (string) x.Parent.Parent.Attribute("Name"),
                      Message = (string) x.Attribute("message")
                  })
                  .ToList();

This uses x.Parent.Parent to go up from the SqlEyeWarning element past SqlEyeWarnings to ScriptFile.

I'm assuming that all SqlEyeWarning elements here are within the same sort of structure. If they are, the above is simplest. Otherwise, you could use:

var warnings = doc.Root
                  .Element("SqlEye")
                  .Elements("ScriptFile")
                  .Elements("SqlEyeWarnings")
                  .Elements("SqlEyeWarning")
                  .Select(x => new {
                      FileName = (string) x.Parent.Parent.Attribute("Name"),
                      Message = (string) x.Attribute("message")
                  })
                  .ToList();

DeveloperInDevelopment · Accepted Answer · 2013-06-05 11:48:23Z

Please see the code below that will write to the console the file name and associated warning message.

This approach will deserialise your XML into .NET objects that are ready to use.

A small tip - try to avoid using too many attributes in your XML. Consider converting the ScriptFile attributes to elements to increase readability =] (You will need to modify the code to reflect the change if you choose to make it - change the XmlAttribute attributes to XmlElement).

Good luck!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using System.Xml.Serialization;

namespace ConsoleApplication1
{
    public class Program
    {
        static void Main(string[] args)
        {
            string input = @"<ScriptFileNames>
                              <SqlEye>
                                <ScriptFile Name='_ws_CommandHistory_AllHistory.sql' Type='SP' SqlEyeAnalysisTime='00:00:01.7817594' FxCopAnalysisTime='00:00:00.2253670' FxCopWarningCount='0' SqlEyeWarningCount='2'>
                                      <SqlEyeWarnings>
                                        <SqlEyeWarning message='SD004: Check for existence object then Drop statement before create statement' />
                                        <SqlEyeWarning message='SP001: Set NoCount statement missing or it should be ON.' />
                                      </SqlEyeWarnings>
                                    </ScriptFile>
                              </SqlEye>
                            </ScriptFileNames>";

            XDocument doc = XDocument.Parse(input);
            XmlSerializer serialiser = new XmlSerializer(typeof(ScriptFileNames));
            ScriptFileNames scriptNames = (ScriptFileNames)serialiser.Deserialize(doc.CreateReader());

            foreach (SqlEyeWarning warning in scriptNames.SqlEye.ScriptFile.SqlEyeWarnings)
            {
                Console.WriteLine(scriptNames.SqlEye.ScriptFile.Name + "\t" + warning.Message);
            }

            Console.ReadLine();
        }

        [XmlRoot]
        public class ScriptFileNames
        {
            [XmlElement("SqlEye")]
            public SqlEye SqlEye { get; set; }
        }

        public class SqlEye
        {
            [XmlElement("ScriptFile")]
            public ScriptFile ScriptFile { get; set; }
        }

        public class ScriptFile
        {
            [XmlArray("SqlEyeWarnings")]
            [XmlArrayItem("SqlEyeWarning", typeof(SqlEyeWarning))]
            public SqlEyeWarning[] SqlEyeWarnings { get; set; }

            [XmlAttribute("Name")]
            public string Name { get; set; }

            [XmlAttribute("Type")]
            public string Type { get; set; }

            [XmlAttribute("SqlEyeAnalysisTime")]
            public string SqlEyeAnalysisTime { get; set; }

            [XmlAttribute("FxCopAnalysisTime")]
            public string FxCopAnalysisTime { get; set; }

            [XmlAttribute("FxCopWarningCount")]
            public string FxCopWarningCount { get; set; }

            [XmlAttribute("SqlEyeWarningCount")]
            public string SqlEyeWarningCount { get; set; }
        }

        public class SqlEyeWarning
        {
            [XmlAttribute("message")]
            public string Message { get; set; }
        }
    }
}

Collectives™ on Stack Overflow

Difficulty in reading XML values using XDocument

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related