Problems retriving XML from SQL

  • Thread starter Thread starter Mark Goldin
  • Start date Start date
M

Mark Goldin

I am using that code to execute SP that returns xml auto:
SqlConnection conn = new SqlConnection("Initial Catalog=manpower;Data
Source=devsrv01;uid=sa;pwd=;");

XmlReader xreader = SqlHelper.ExecuteXmlReader(conn,
CommandType.StoredProcedure, "test" );

XmlWriter w = new XmlTextWriter(@"c:\foo.xml", Encoding.UTF8);

xreader.MoveToContent();

while(xreader.Read())

w.WriteNode(xreader, false);

the foo.xml has nothing.

If SP has for xml explicit then the file has some xml but not all. It's cut
somewhere in a middle.

Any idea?
 
Mark:

In your SP, right after FOR XML AUTO , add, ", Elements" so it reads "FOR
XML AUTO, ELEMENTS" Now, you may need to make some other mods to get
exactly what you wnat, XML is quite flexible after all, but add that
predicate and see if that doesn't fix the first part of your problem. Let
me know and well move from there.

HTH,

Bill
 
OK, it works if I remove ", elements"
Now how can I convert XmlReader' xml data into an xml string
 
Mark:

You were writing a node but that's another issue. If you want this as a
string...

SqlConnection conn = new SqlConnection("Initial Catalog=appsupport;Data
Source=appdev01;trusted_connection=yes;uid=;pwd=;");
XmlReader xreader = SqlHelper.ExecuteXmlReader(conn, CommandType.Text,
"select * from tiptreecat for xml auto,elements" );
XmlWriter w = new XmlTextWriter(@"c:\foo.xml", Encoding.UTF8);
StringBuilder sb = new StringBuilder();
xreader.MoveToContent();
while(xreader.Read())
sb.Append(xreader.Value);

MessageBox.Show(sb.ToString(), "IT Worked");
 
Back
Top