XML from a dataset

VBAHole22

VBAHole22

Well-known member
Joined
Oct 21, 2003
Messages
432
Location
VA
Im trying to generate some valid xml from a SQL Server dataset.
I can get it to crank out the standard xml using WriteXml but I would like to add some elements and nodes before this part. Can I do this? Also can I modify the default tags that the WriteXML kicks out?

I have some code that I am trying to use to write start and end elements but it keeps saying invalid xml

// Build the DataSet
DataSet myDs = new DataSet();
adapter.Fill(myDs);
FileStream myFs = null;
// Get a FileStream object
myFs = new FileStream(filename+".xml", FileMode.OpenOrCreate, FileAccess.Write);
XmlTextWriter xmlWriter = new XmlTextWriter(myFs, Encoding.UTF8);
xmlWriter.Indentation = 85;
xmlWriter.Namespaces = false;
xmlWriter.Formatting = Formatting.Indented;
xmlWriter.WriteStartDocument();
xmlWriter.WriteStartElement("Request");
myDs.WriteXml(xmlWriter,System.Data.XmlWriteMode.IgnoreSchema);

xmlWriter.WriteEndElement();
xmlWriter.WriteEndDocument();



// It is always good housekeeping to close a file.
myFs.Close();
 
VBA --

I dont know if this is exactly what you are looking for, but it is certainly possible to do something like what you are looking for. I would suggest using the xmldocument object to manipulate the xml. This code is probably not the absolute best way to do what you want, but with limited time I have wipped something up for you to look at, and you can play with the document object to do more or be more efficient if you feel so led. Anyway, I hope this will help you find a temp solution if nothing else:

make sure to include the directive!
[CS]
//at top:
using System.Xml;

//inside your worker function
DataSet myDS = new DataSet("results");
SqlDataAdapter myDA = new SqlDataAdapter(sqlCommand1);
XmlDocument myDoc = new XmlDocument();
XmlDocument myDoc2 = new XmlDocument();
XmlElement myElement;
XmlAttribute myAttribute;
XmlNode myNode;
string myFormerXML = "";

try
{
if (sqlConnection1.State!=ConnectionState.Open)
{
sqlConnection1.Open();
}
//load the dataset
myDA.Fill(myDS);
//load the xml from the dataset into the xml document object
myDoc.LoadXml(myDS.GetXml());
//load the old xml to string for use later
myFormerXML = myDoc.OuterXml;

//Create the output document with the first element here:
myElement = myDoc2.CreateElement("Request");
myElement.InnerText = "RequestElementText";
myAttribute = myDoc2.CreateAttribute("FirstAttribute");
myAttribute.InnerText = "FirstAttributeText";
myElement.Attributes.Append(myAttribute);
myDoc2.AppendChild(myElement);

//tag the original xml into the new document here:
myNode = myDoc2.CreateNode(XmlNodeType.Element, "OriginalXML", "SomeURI");
myNode.InnerXml = myFormerXML;
myDoc2.FirstChild.AppendChild(myNode);

//Set the final element here:
myElement = myDoc2.CreateElement("Final");
myElement.InnerText = "FinalElementText";
myAttribute = myDoc2.CreateAttribute("FinalAttribute");
myAttribute.InnerText = "FinalAttributeText";
myElement.Attributes.Append(myAttribute);
myDoc2.DocumentElement.AppendChild(myElement);

//for fun, show the new xml
MessageBox.Show(myDoc2.OuterXml);

FileStream myFs = new FileStream(filename+".xml", FileMode.OpenOrCreate, FileAccess.Write);
XmlTextWriter xmlWriter = new XmlTextWriter(myFs, System.Text.Encoding.UTF8);
myDoc2.WriteTo(xmlWriter);

///...etc and Good Luck! :)
}
catch (System.Exception ex)
{
MessageBox.Show("ERROR: " + ex.Message);
}
finally
{
if (sqlConnection1.State==ConnectionState.Open)
{
sqlConnection1.Close();
}
}
[/CS]

good luck,
Brian
 
Back
Top