For Xml Raw Issue Please Help

[Mham2k]

I am trying to write a extremely simple Sql Server 2000 Query through VS 2003 Query. I made a view and now I am trying to use For XML RAW to bring back xml from that view.

If I type this:
SELECT * FROM ViewName
WHERE (EMPLOYEEID = '46109')
It brings back 14 beautiful rows

If I type this
SELECT * FROM ViewName
WHERE (EMPLOYEEID = '46109')
FOR XML RAW
It brings back 2 lines that say <Binary>
When I go directly against my Sql Server those lines turn out to be one full xml line <raw attributes /> and one half line <raw attributes

What am I doing wrong here... I have been at work trying to figure this out for the past 2 days! It seems that Sql Server 2000 doesn't like for XML and views together! It works if I query all 13 tables but that will take entirely to long.

 

[philhege]

Have you tried the other flavors of FOR XML?

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Pity the insomniac dyslexic agnostic. He stays up all night, wondering if there really is a dog.

 

[Mham2k]

Yes I have tried For XML Raw and Auto but not explicit. I would try explicit but I dont fully understand how to right the code yet. What I dont get is when I am making sites for others at home on my server 2005 it works fine but at work on server 2000 it doesn't. Makes no sense to me.

 

[philhege]

XML handling was greatly enhanced in 2K5. You actually get "real" XML from 2K5 queries; 2K was a bit hamstrung.

 

[Mham2k]

Yes I just noticed this. It is not an issued with sql server 2000 at all, all I did was change the documents width to the maximmum and all the columns showed up. Lol.. Idiot error right... But I still have another problem I will put into a .Net forum. My code still only returns 2 lines of xml and not all 14. Here it is if anyone knows about it here.

SqlConnection sqlc = new SqlConnection();
SqlCommand sqlPoints = new SqlCommand();
string sSqlConn = ConfigurationSettings.AppSettings.Get("KronosConnectionString").ToString();
string strEventQ = "SELECT * FROM dbo.VP_WATATTEDANCEDTL WHERE (EMPLOYEEID = '" + personID + "') FOR XML RAW";
sqlc = new SqlConnection(sSqlConn);
sqlc.Open();
sqlPoints = new SqlCommand(strEventQ, sqlc);
//string strDate = sqlPoints.ExecuteScalar().ToString().Split(' ')[0];
XmlReader xr = sqlPoints.ExecuteXmlReader();
sqlc.Close();
XmlDocument xmlDoc = new XmlDocument();

xmlDoc.LoadXml("<Root></Root>");
XmlNode node = xmlDoc.ReadNode(xr);
while (node != null)
{
xmlDoc.DocumentElement.AppendChild(node);
try
{
node = xmlDoc.ReadNode(xr);
}
catch
{
}
}
xr.Close();
return xmlDoc;