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.OleDb.* - 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.ExecuteXmlReader();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xmlReader);
xmlReader.Close();
string strText = xmlDoc.OuterXml;

And xmlDoc.OuterXml at a breakpoint at the last line, is
"<CLIENTINFO><CLIENTID>123412341234</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****@microsoft.com> wrote in message
news:Ow**************@tk2msftngp13.phx.gbl...

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

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

System.Xml.XmlReader myXmlReader = myCommand.ExecuteXmlReader();

// Always close the XmlReader when finished.
myXmlReader.Close();
}
catch(Exception e)
{
System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();
log.Source = "My Application";
log.WriteEntry(e.ToString());
Console.WriteLine("Exception of type {0} occurred.", e.GetType());
}
finally
{
myConnection.Close();
}
}

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*****************@newssvr31.news.prodigy.co m...

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*****************@newssvr31.news.prodigy.co m...

THANKS!! I've been looking for the answer to these for weeks. I always do
things with System.Data.OleDb.* - 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.ExecuteXmlReader();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xmlReader);
xmlReader.Close();
string strText = xmlDoc.OuterXml;

And xmlDoc.OuterXml at a breakpoint at the last line, is
"<CLIENTINFO><CLIENTID>123412341234</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****@microsoft.com> wrote in message news:Ow**************@tk2msftngp13.phx.gbl...

The SqlCommand class has an ExecuteXmlReader method.
See

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

Here's the cut a pasted example

[C#]
public void CreateMyXmlReader(string myXmlQuery, SqlConnection

myConnection)

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

Query
SqlCommand myCommand = new SqlCommand(myXmlQuery, myConnection);
try
{
myConnection.Open();

System.Xml.XmlReader myXmlReader = myCommand.ExecuteXmlReader();

// Always close the XmlReader when finished.
myXmlReader.Close();
}
catch(Exception e)
{
System.Diagnostics.EventLog log = new System.Diagnostics.EventLog(); log.Source = "My Application";
log.WriteEntry(e.ToString());
Console.WriteLine("Exception of type {0} occurred.", e.GetType());
}
finally
{
myConnection.Close();
}
}

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*****************@newssvr31.news.prodigy.co m...

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****@microsoft.com> wrote in message
news:Ox**************@TK2MSFTNGP11.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*****************@newssvr31.news.prodigy.co m...

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

things with System.Data.OleDb.* - 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.ExecuteXmlReader();
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.Load(xmlReader);
xmlReader.Close();
string strText = xmlDoc.OuterXml;

And xmlDoc.OuterXml at a breakpoint at the last line, is
"<CLIENTINFO><CLIENTID>123412341234</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****@microsoft.com> wrote in message

news:Ow**************@tk2msftngp13.phx.gbl...

The SqlCommand class has an ExecuteXmlReader method.
See

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

Here's the cut a pasted example

[C#]
public void CreateMyXmlReader(string myXmlQuery, SqlConnection

myConnection)

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

System.Xml.XmlReader myXmlReader = myCommand.ExecuteXmlReader();

// Always close the XmlReader when finished.
myXmlReader.Close();
}
catch(Exception e)
{
System.Diagnostics.EventLog log = new System.Diagnostics.EventLog(); log.Source = "My Application";
log.WriteEntry(e.ToString());
Console.WriteLine("Exception of type {0} occurred.", e.GetType()); }
finally
{
myConnection.Close();
}
}

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*****************@newssvr31.news.prodigy.co m...
> 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
.