0

I have a CLR stored procedure that receives raw XML as a parameter, for example:

SET @xml = (SELECT * FROM titles FOR XML RAW)

Will give

<row id="1" description="Mr." />
<row id="2" description="Mrs." />

The above @xml is passed to a generic CLR stored procedure that in turn needs to parse the data it receives.

Is there a simple way to transform the raw XML into a list in C#?

Ivan-Mark Debono
  • 15,500
  • 29
  • 132
  • 263
  • Will it contain a root node of any description or just the individual row nodes? – Yannick Meeus Mar 11 '15 at 10:23
  • @YannickMeeus It's just the raw xml as shown above. I'd rather not transform into full xml in order to keep the size small. – Ivan-Mark Debono Mar 11 '15 at 10:26
  • You'll need to either use an XmlReader with a conformance type of fragment or add a root node and use XmlDocument, See http://stackoverflow.com/questions/18186225/c-sharp-xdocument-load-with-multiple-roots. – Dan Guzman Mar 11 '15 at 10:48

1 Answers1

0

Edited based on comments below.

var xml =  XDocument.Parse(string.Format("<root>{0}</root>", @"<row id=""1"" description=""Mr."" /><row id=""2"" description=""Mrs."" />"));

var rows = from node in xml.Descendants("row")
           select new
           {
               Id = node.Attribute("id").Value,
               description = node.Attribute("description").Value
           };

The above will first wrap your xml in a mandatory root, it doesn't need to be called root but XDocument requires there to be a root node, otherwise it'll throw a wobbly.

Then using linq-to-XML, we're parsing the attributes of every row node into a list of anonymous objects. You can also create a typed object and parse it that way, but this really depends on your requirements.

Yannick Meeus
  • 5,643
  • 1
  • 35
  • 34