0

We have a data table that uses sparse columns, and a column set. When we query data from this table, we are only bringing back a couple of key columns plus the columnset column.

The columnset column is returned as an SqlXml field type, and I want to parse through the XML to convert it to a dictionary. But, the XML data doesn't seem to be well formed; as whenever I try this:

var columnSet = rdr.GetSqlXml(rdr.GetOrdinal("ColSet"));
if (!columnSet.IsNull)
{
    var xml = XDocument.Load(xmlData.CreateReader());

it throws an exception:

System.InvalidOperationException: This operation would create an incorrectly structured document.
  at System.Xml.Linq.XDocument.ValidateDocument(XNode previous, XmlNodeType allowBefore, XmlNodeType allowAfter)
  at System.Xml.Linq.XDocument.ValidateNode(XNode node, XNode previous)\r\n   at System.Xml.Linq.XContainer.AddNodeSkipNotify(XNode n)
  at System.Xml.Linq.XContainer.ReadContentFrom(XmlReader r)
  at System.Xml.Linq.XContainer.ReadContentFrom(XmlReader r, LoadOptions o)
  at System.Xml.Linq.XDocument.Load(XmlReader reader, LoadOptions options)   
  at System.Xml.Linq.XDocument.Load(XmlReader reader)

If I use GetString() instead of GetSqlXml(), I definitely get back invalid XML -- it has no root element, and I have to perform string manipulation to get it to load into an XDocument.

Is there some way to get the SqlXml value for a columnset to produce a valid XML document?

Michael Edenfield
  • 28,070
  • 4
  • 86
  • 117
  • It is simple to just add "" + GetString() + "" – jdweng May 28 '15 at 16:38
  • Yes as I said in my question, that's what I'm doing now. But since I *want* the XML as XML, it seems silly to have the DataReader construct an XmlReader to convert the XML to string to turn around and convert back to XML for parsing. – Michael Edenfield May 28 '15 at 16:47
  • No datareader : XDocument doc = XDocument.Parse("" + GetString() + ""); – jdweng May 28 '15 at 16:53
  • GetString *makes* an XmlReader internally, that's my point. – Michael Edenfield May 28 '15 at 16:54
  • Try here: http://stackoverflow.com/questions/5042902/xml-error-there-are-multiple-root-elements or here: http://stackoverflow.com/questions/2374426/linq-to-xml-load-xml-fragments-from-file – dbc May 28 '15 at 19:28
  • That second one actually gets pretty close to what I need. Though, at this point I realized it would probably be just as easy to navigate the XML using the `XmlReader` itself. – Michael Edenfield May 28 '15 at 20:10

0 Answers0