SQL 'for xml' and C# example??

[Drebin]

I'm missing some piece of the puzzle. I'm using 'for xml' type queries from
SQL2K - and now trying to change the front-end from ADO to C#, I can't quite
figure it out..

It seems that I could use an XmlReader.. and somehow use that instead of a
DataReader.. but that didn't work.. I tried using a DataReader and get the
byte array - but that wasn't right.

SQL returns formatted XML, I need to load that XML into an XmlDocument
somehow without ANY molestation of that dom. Any ideas? Examples? Missing
links????

thanks

[Drebin]

THANKS!! I've been looking for the answer to these for weeks. I always do
things with System.Data.Ole Db.* - and this actually dawned on my a little
while ago - so I stumbled across the same answer.

I have one followup, with this code:

XmlReader xmlReader = objCmd.ExecuteX mlReader();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xml Reader);
xmlReader.Close ();
string strText = xmlDoc.OuterXml ;

And xmlDoc.OuterXml at a breakpoint at the last line, is
"<CLIENTINFO><C LIENTID>1234123 41234</CLIENTID> (bunch of elements
deleted)</CLIENTINFO>" - which is correct and valid.

But if I try to read OuterXml or InnerText into a variable (like the last
line of code), I get and exception of "This document already has a
DocumentElement node."

Any ideas? This same stored procedure works with legacy ADO... Thanks
again!!
"SQL Server Development Team [MSFT]" <sq****@microso ft.com> wrote in message
news:Ow******** ******@tk2msftn gp13.phx.gbl...

The SqlCommand class has an ExecuteXmlReade r method.
See
http://msdn.microsoft.com/library/de...eaderTopic.asp
Here's the cut a pasted example

[C#]
public void CreateMyXmlRead er(string myXmlQuery, SqlConnection myConnection) {
// For the myXmlQuery append the "for XML" statement to the end of the SQL
Query
SqlCommand myCommand = new SqlCommand(myXm lQuery, myConnection);
try
{
myConnection.Op en();

System.Xml.XmlR eader myXmlReader = myCommand.Execu teXmlReader();

// Always close the XmlReader when finished.
myXmlReader.Clo se();
}
catch(Exception e)
{
System.Diagnost ics.EventLog log = new System.Diagnost ics.EventLog();
log.Source = "My Application";
log.WriteEntry( e.ToString());
Console.WriteLi ne("Exception of type {0} occurred.", e.GetType());
}
finally
{
myConnection.Cl ose();
}
}

Mark Fussell
PM - System.Xml

--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Drebin" <tR************ *@hotmail.com> wrote in message
news:iS******** *********@newss vr31.news.prodi gy.com...

I'm missing some piece of the puzzle. I'm using 'for xml' type queries

from

SQL2K - and now trying to change the front-end from ADO to C#, I can't

quite

figure it out..

It seems that I could use an XmlReader.. and somehow use that instead of a DataReader.. but that didn't work.. I tried using a DataReader and get the byte array - but that wasn't right.

SQL returns formatted XML, I need to load that XML into an XmlDocument
somehow without ANY molestation of that dom. Any ideas? Examples? Missing links????

thanks

[SQL Server Development Team [MSFT]]

Ummm. I am surprised that it let you load the returned XML. By default the
For XML query returns XML fragments with no "wrapping" document element
node. This will fail when loaded into the XmlDocument class. Usually you
have one of three choices;

1) Create an document element node before setting the InnerXml property on
the XmlDocument from the XmlReader's InnerXml.
2) Create a document element node for the FOR XML query using the &root
append. See http://www.sqlxml.org/faqs.aspx?faq=16
3) Use the XPathDocument class that can load XML fragements if you do not
want a document element node i.e You want an XML fragment. The only issue
here is that you have to save this out via an XPathNavigator which takes
more code.

I would look though examples that Dan has from his book.
http://www.xmlforasp.net/codeSection.aspx?csID=1
See CH8 approx - SQL Server and XML.

Thanks. Mark
PM - System.Xml
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Drebin" <tR************ *@hotmail.com> wrote in message
news:HT******** *********@newss vr31.news.prodi gy.com...

THANKS!! I've been looking for the answer to these for weeks. I always do
things with System.Data.Ole Db.* - and this actually dawned on my a little
while ago - so I stumbled across the same answer.

I have one followup, with this code:

XmlReader xmlReader = objCmd.ExecuteX mlReader();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xml Reader);
xmlReader.Close ();
string strText = xmlDoc.OuterXml ;

And xmlDoc.OuterXml at a breakpoint at the last line, is
"<CLIENTINFO><C LIENTID>1234123 41234</CLIENTID> (bunch of elements
deleted)</CLIENTINFO>" - which is correct and valid.

But if I try to read OuterXml or InnerText into a variable (like the last
line of code), I get and exception of "This document already has a
DocumentElement node."

Any ideas? This same stored procedure works with legacy ADO... Thanks
again!!
"SQL Server Development Team [MSFT]" <sq****@microso ft.com> wrote in message news:Ow******** ******@tk2msftn gp13.phx.gbl...

The SqlCommand class has an ExecuteXmlReade r method.
See

http://msdn.microsoft.com/library/de...eaderTopic.asp

Here's the cut a pasted example

[C#]
public void CreateMyXmlRead er(string myXmlQuery, SqlConnection

myConnection)

{
// For the myXmlQuery append the "for XML" statement to the end of the SQL

Query
SqlCommand myCommand = new SqlCommand(myXm lQuery, myConnection);
try
{
myConnection.Op en();

System.Xml.XmlR eader myXmlReader = myCommand.Execu teXmlReader();

// Always close the XmlReader when finished.
myXmlReader.Clo se();
}
catch(Exception e)
{
System.Diagnost ics.EventLog log = new System.Diagnost ics.EventLog(); log.Source = "My Application";
log.WriteEntry( e.ToString());
Console.WriteLi ne("Exception of type {0} occurred.", e.GetType());
}
finally
{
myConnection.Cl ose();
}
}

Mark Fussell
PM - System.Xml

--
This posting is provided "AS IS" with no warranties, and confers no

rights.

Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Drebin" <tR************ *@hotmail.com> wrote in message
news:iS******** *********@newss vr31.news.prodi gy.com...

I'm missing some piece of the puzzle. I'm using 'for xml' type queries

from

SQL2K - and now trying to change the front-end from ADO to C#, I can't

quite

figure it out..

It seems that I could use an XmlReader.. and somehow use that instead

of a DataReader.. but that didn't work.. I tried using a DataReader and get the byte array - but that wasn't right.

SQL returns formatted XML, I need to load that XML into an XmlDocument
somehow without ANY molestation of that dom. Any ideas? Examples? Missing links????

thanks

[Drebin]

Actually - my problem is just the opposite, the query DOES wrap everything
in a root element... And we already have a bunch of complex stored
procedures that already work this way...

It's almost like XmlDocument is trying to put a wrapper tag and there is
already one. Any other ideas?? And thanks VERY much for your help - even if
I don't get this working - you've already advanced me quite a bit, because I
was at a dead end!!
"SQL Server Development Team [MSFT]" <sq****@microso ft.com> wrote in message
news:Ox******** ******@TK2MSFTN GP11.phx.gbl...

Ummm. I am surprised that it let you load the returned XML. By default the
For XML query returns XML fragments with no "wrapping" document element
node. This will fail when loaded into the XmlDocument class. Usually you
have one of three choices;

1) Create an document element node before setting the InnerXml property on
the XmlDocument from the XmlReader's InnerXml.
2) Create a document element node for the FOR XML query using the &root
append. See http://www.sqlxml.org/faqs.aspx?faq=16
3) Use the XPathDocument class that can load XML fragements if you do not
want a document element node i.e You want an XML fragment. The only issue
here is that you have to save this out via an XPathNavigator which takes
more code.

I would look though examples that Dan has from his book.
http://www.xmlforasp.net/codeSection.aspx?csID=1
See CH8 approx - SQL Server and XML.

Thanks. Mark
PM - System.Xml
--
This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Drebin" <tR************ *@hotmail.com> wrote in message
news:HT******** *********@newss vr31.news.prodi gy.com...

THANKS!! I've been looking for the answer to these for weeks. I always do

things with System.Data.Ole Db.* - and this actually dawned on my a little while ago - so I stumbled across the same answer.

I have one followup, with this code:

XmlReader xmlReader = objCmd.ExecuteX mlReader();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xml Reader);
xmlReader.Close ();
string strText = xmlDoc.OuterXml ;

And xmlDoc.OuterXml at a breakpoint at the last line, is
"<CLIENTINFO><C LIENTID>1234123 41234</CLIENTID> (bunch of elements
deleted)</CLIENTINFO>" - which is correct and valid.

But if I try to read OuterXml or InnerText into a variable (like the last line of code), I get and exception of "This document already has a
DocumentElement node."

Any ideas? This same stored procedure works with legacy ADO... Thanks
again!!
"SQL Server Development Team [MSFT]" <sq****@microso ft.com> wrote in message

news:Ow******** ******@tk2msftn gp13.phx.gbl...

The SqlCommand class has an ExecuteXmlReade r method.
See

http://msdn.microsoft.com/library/de...eaderTopic.asp

Here's the cut a pasted example

[C#]
public void CreateMyXmlRead er(string myXmlQuery, SqlConnection

myConnection)

{
// For the myXmlQuery append the "for XML" statement to the end of the SQL Query
SqlCommand myCommand = new SqlCommand(myXm lQuery, myConnection);
try
{
myConnection.Op en();

System.Xml.XmlR eader myXmlReader = myCommand.Execu teXmlReader();

// Always close the XmlReader when finished.
myXmlReader.Clo se();
}
catch(Exception e)
{
System.Diagnost ics.EventLog log = new System.Diagnost ics.EventLog(); log.Source = "My Application";
log.WriteEntry( e.ToString());
Console.WriteLi ne("Exception of type {0} occurred.", e.GetType()); }
finally
{
myConnection.Cl ose();
}
}

Mark Fussell
PM - System.Xml

--
This posting is provided "AS IS" with no warranties, and confers no

rights.

Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"Drebin" <tR************ *@hotmail.com> wrote in message
news:iS******** *********@newss vr31.news.prodi gy.com...
> I'm missing some piece of the puzzle. I'm using 'for xml' type queries from
> SQL2K - and now trying to change the front-end from ADO to C#, I can't quite
> figure it out..
>
> It seems that I could use an XmlReader.. and somehow use that instead of

a

> DataReader.. but that didn't work.. I tried using a DataReader and

get the

> byte array - but that wasn't right.
>
> SQL returns formatted XML, I need to load that XML into an

XmlDocument > somehow without ANY molestation of that dom. Any ideas? Examples?

Missing

> links????
>
> thanks
>
>

[fasdfsa]

>-----Original Message-----

I'm missing some piece of the puzzle. I'm using 'for xml' type queries fromSQL2K - and now trying to change the front-end from ADO to C#, I can't quitefigure it out..

It seems that I could use an XmlReader.. and somehow use that instead of aDataReader.. but that didn't work.. I tried using a DataReader and get thebyte array - but that wasn't right.

SQL returns formatted XML, I need to load that XML into an XmlDocumentsomehow without ANY molestation of that dom. Any ideas? Examples? Missinglinks????

thanks
.