Retreive data from SQL and put into XML secondary data source

Irianna — Mon, 19 Apr 2010 14:20:03 GMT

Hi Folks,

I'm trying to pull data out of my SQL database into a secondary datasource which is XML.

Unfortunatly, I'm getting this error:

System.InvalidOperationException was unhandled by user code
Message="Token StartElement in state EndRootElement would result in an invalid XML document. Make sure that the ConformanceLevel setting is set to ConformanceLevel.Fragment or ConformanceLevel.Auto if you want to write an XML fragment. "
Source="System.Xml"
StackTrace:
       at System.Xml.XmlWellFormedWriter.ThrowInvalidStateTransition(Token token, State currentState)
       at System.Xml.XmlWellFormedWriter.AdvanceState(Token token)
       at System.Xml.XmlWellFormedWriter.WriteStartElement(String prefix, String localName, String ns)
       at System.Xml.XPath.XPathNavigator.BuildSubtree(XmlReader reader, XmlWriter writer)
       at System.Xml.XPath.XPathNavigator.AppendChild(XmlReader newChild)
       at System.Xml.XPath.XPathNavigator.AppendChild(String newChild)

---

Does anyone know why this would be happneing?

My XML is:

<?xml version="1.0" encoding="utf-8"?>
<tk:Speciality
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:tk="http://mywebspace/Speciality.xsd"
>
      <SpecialityValues>
          <SpecialityKey/>
          <SpecialityName/>
   </SpecialityValues>
    <SpecialityValues>
          <SpecialityKey/>
          <SpecialityName/>
   </SpecialityValues>
</tk:Speciality>

----

My code in the form is:

public void Directorate_Changing(object sender, XmlEventArgs e)

{

StringBuilder builder = new StringBuilder();

XPathNavigator navigator = this.MainDataSource.CreateNavigator();

string Cmp = navigator.SelectSingleNode("/my:MainForm/my:Campus", this.NamespaceManager).Value;

string Dir = navigator.SelectSingleNode("/my:MainForm/my:Directorate", this.NamespaceManager).Value;

navigator.SelectSingleNode("/my:MainForm/my:Speciality", this.NamespaceManager).SetValue(string.Empty);using (SqlConnection connection = new SqlConnection(helper.db_conn))

{

using (SqlCommand command = new SqlCommand("SP_InfoPath_FindSpeciality", connection))

{

command.CommandType = CommandType.StoredProcedure;

command.Parameters.Add("@Campus", SqlDbType.VarChar, (10)).Value = Cmp;command.Parameters.Add("@Dir", SqlDbType.VarChar, (10)).Value = Dir;

connection.Open();

command.Transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

SqlDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);

while (reader.Read())

{

String Key = reader[0].ToString();builder.Append("<SpecialityKey>");

builder.Append(Key.ToString());

builder.Append("</SpecialityKey>");

builder.Append("<SpecialityName>");

builder.Append(reader[1].ToString());

builder.Append("</SpecialityName>");

}

reader.Close();

command.Transaction.Commit();

}

XPathNavigator navigator2 = this.DataSources["SpecialityValues"].CreateNavigator();

int count = navigator2.Select("/tk:Speciality/SpecialityValues", this.NamespaceManager).Count;

if (count > 0)

{

string str2 = "/tk:Speciality/SpecialityValues[XXX]";

string xpath = str2.Replace("XXX", "1");

string str4 = str2.Replace("XXX", count.ToString());

XPathNavigator navigator3 = navigator2.SelectSingleNode(xpath, this.NamespaceManager);

XPathNavigator lastSiblingToDelete = navigator2.SelectSingleNode(str4, this.NamespaceManager);

navigator3.DeleteRange(lastSiblingToDelete);

}

if (builder.Length > 0)

{

navigator2.MoveToRoot();

navigator2.MoveToFirstChild();

navigator2.AppendChild(builder.ToString());

}

-----

The form fails on the last line "navigator2.AppendChild(builder.ToString());"

Builder has the full string in, however when you go to .ToString() is when the above error message comes.

If anyone could help that would be fantastic

Thank you, Irianna

SQL Database Integration

Retreive data from SQL and put into XML secondary data source