How can I create an XMLDocument from SqlCommand.ExecuteXmlReader

[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

 

[Joyjit Mukherjee]

Hi,

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();

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

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

// do what you want else.

Regards
Joyjit

 

[Guest]

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