I'm trying to create a SQLCLR scalar function that parses a nvarchar(max) string and returns XML. The data is always being returned as a snippet without the xml declaration, rather than a full document. The relevant code is-
XmlDocument doc = new XmlDocument( );
XmlDeclaration xDec = doc.CreateXmlDeclaration( "1.0", "UTF-8", null );
doc.AppendChild(xDec);
XmlElement rootnode = doc.CreateElement("Root Node");
doc.AppendChild(rootnode);
... snipped ...
XmlReader xread = new XmlNodeReader(doc);
XmlReaderSettings xsetRead = new XmlReaderSettings();
xsetRead.ConformanceLevel = System.Xml.ConformanceLevel.Document;
return new SqlXml(XmlReader.Create(xread,xsetRead));
I can't determine if I'm misusing the XmlReader or if this is a consequence of the SQLCLR environment. The only thing I can get back in a SQL query is the root node and it's children.
Limitations of the xml Data Type
The XML declaration PI, for example,
<?xml version='1.0'?>, is not preserved when storing XML data in an xml data type instance. This is by design. The XML declaration (<?xml ... ?>) and its attributes (version/encoding/stand-alone) are lost after data is converted to type xml. The XML declaration is treated as a directive to the XML parser. The XML data is stored internally as ucs-2. All other PIs in the XML instance are preserved.
XML Data Type and Columns (SQL Server)
The data is stored in an internal representation that preserves the XML content of the data. This internal representation includes information about the containment hierarchy, document order, and element and attribute values. Specifically, the InfoSet content of the XML data is preserved. For more information about InfoSet, visit http://www.w3.org/TR/xml-infoset. The InfoSet content may not be an identical copy of the text XML, because the following information is not retained: insignificant white spaces, order of attributes, namespace prefixes, and XML declaration.