I have following CLR stored procedure, which is throwing error on this command
xmlDoc.LoadXml(inputXml);
code
public static int spGetTaxOfficeXML(SqlXml _inputXml)
{
// this procedure rename Row elements name with NodeName attribute value
string inputXml = _inputXml.ToString();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(inputXml);
// procedure Logic
SqlContext.Pipe.Send(inputXml);
return 0;
}
it works well, when the XML text is inside variable inside visual studio as a text. But when i upload code to the SQL server as CLR, and try to execute it from SQL Management Studio:
DECLARE @XML XML
SET @XML = '<NodeA><NodeB></NodeB><NodeC AttributeX=""><Row NodeName="RowA" AttributeA="" AttributeB="abcd" AttributeC="efgh" /><Row NodeName="RowB" AttributeA="wxyz" /><Row NodeName="RowC" AttributeB="qwer" AttributeC="tyui" /><Row NodeName="RowD" AttributeA="stuv" AttributeB="erty" AttributeC="fghj" /></NodeC></NodeA>'
EXEC dbo.spGetTaxOfficeXML @XML
then this error is throwed:
Msg 6522, Level 16, State 1, Procedure spGetTaxOfficeXML, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "spGetTaxOfficeXML":
System.Xml.XmlException: Data at the root level is invalid. Line 1, position 1.
System.Xml.XmlException:
at System.Xml.XmlTextReaderImpl.Throw(Exception e)
at System.Xml.XmlTextReaderImpl.ParseRootLevelWhitespace()
at System.Xml.XmlTextReaderImpl.ParseDocumentContent()
at System.Xml.XmlLoader.Load(XmlDocument doc, XmlReader reader, Boolean preserveWhitespace)
at System.Xml.XmlDocument.Load(XmlReader reader)
at System.Xml.XmlDocument.LoadXml(String xml)
at StoredProcedures.spGetTaxOfficeXML(String inputXml)
I tried to solve the error with following code, because i though the utf8 byte could cause the error, but it did not help.
// if byte mark exception happens
string _byteOrderMarkUtf8 = Encoding.UTF8.GetString(Encoding.UTF8.GetPreamble());
if (inputXml.StartsWith(_byteOrderMarkUtf8))
{
inputXml = inputXml.Remove(0, _byteOrderMarkUtf8.Length);
}
What is wrong here ?
Best Answer
The problem is that you made a bad assumption about what value is returned by the
SqlXml.ToString()
method, and then in testing in Visual Studio, you injected a value but did not check to see whatToString()
was actually returning (which is: "System.Data.SqlTypes.SqlXml"). Though you probably would have seen this had you placed theSqlContext.Pipe.Send(inputXml);
line above thexmlDoc.LoadXml(inputXml);
line ;-).When working with the
Sql*
types, you want to almost always access them via theValue
property, which they all have. TheValue
property does return the string version of the XML as you were wanting to get. However, this is less than ideal since you will be converted the XML to a string, only to convert that back to XML again when you callXmlDocument.LoadXml()
.A better way is to use the
SqlXml.CreateReader()
method which returns anXmlReader
, and that can be used with theXmlDocument.Load()
method.I reworked your test code (below) to be an example showing all 3 of these variations:
Running the code above with the original test SQL returns the following in the "Messages" tab:
P.S. You don't need to worry about UTF-8, especially for NCHAR / NVARCHAR / XML data generated from within SQL Server, since SQL Server is UTF-16 LE.