How can I create an XMLDocument from SqlCommand.ExecuteXmlReader

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Here is what I have tried, including the lines I commented in and out...


SqlConnection cnn = new SqlConnection("Data Source=(local); database=Pubs;
Integrated Security=SSPI");
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText= "SELECT * FROM jobs FOR XML AUTO, ELEMENTS, XMLDATA";
cnn.Open();
XmlReader xr = cmd.ExecuteXmlReader();

DataSet ds = new DataSet();
ds.ReadXml(xr);
//ds.ReadXmlSchema(xr);
XmlDataDocument xd = new XmlDataDocument(ds);
xr.Close();
xr = cmd.ExecuteXmlReader();
//xr.MoveToContent();
ds.ReadXml(xr);
//ds.ReadXml(xr,XmlReadMode.IgnoreSchema);
//xd.Load(xr);
ds.WriteXml(@"c:\pubjobs.xml",XmlWriteMode.DiffGram);

Thanks in advance for your help...

Dave
 
Hi,


Dave Boal said:
Here is what I have tried, including the lines I commented in and out...


SqlConnection cnn = new SqlConnection("Data Source=(local); database=Pubs;
Integrated Security=SSPI");
SqlCommand cmd = cnn.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText= "SELECT * FROM jobs FOR XML AUTO, ELEMENTS, XMLDATA";
cnn.Open();
XmlReader xr = cmd.ExecuteXmlReader();
Click to expand...

create a XmlDocument & load the data into it as :-

XmlDocument mydoc = new XmlDocument();
mydoc.Load(xr);

// do what you want else.

Regards
Joyjit
 
The basic problem is that the FOR XML clause returns rows of XML data without
enclosing them in an outer root element. So instead of:

<root>
<rec>...</rec>
<rec>...</rec>
</root>

it only returns:

<rec>...</rec>
<rec>...</rec>

Which is why you get the error messages.

Use the SQLXML managed classes. That has an SqlXmlCommand object which in
turn has a RootTag property which sets the default root element. That should
fix it.

Sujit D'Mello
 
Back
Top