How do I use SQL 'FOR XML' with .NET?

[keith chadwick]

The system I am converting use a lot of sql that makes use of the for xml
clause provided for in sql server 2000. Below is an example of how this is
done in ASP:

set dataxml=server.CreateObject("MSXML2.DOMDocument.4.0")
dataxml.setProperty "ServerHTTPRequest", false
dataxml.async=false

dim sSQL
sSQL = "<?xml version=""1.0"" ?><ROOT
xmlns:sql=""urn:schemas-microsoft-com:xml-sql"">"
sSQL = sSQL & "<clients><sql:query>select * from client order by name for
xml auto</sql:query></clients>"
sSQL = sSQL & "<events><sql:query>select * from event order by name for xml
auto</sql:query></events>"
sSQL = sSQL & "</ROOT>"

set cmd=server.CreateObject("ADODB.COMMAND")
cmd.ActiveConnection=application("DBCONNECTION")
cmd.CommandText=sSQL
cmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
cmd.Properties("Output Encoding")="ISO-8859-1"
cmd.Properties("XSL")="myxsltfile.xslt"
cmd.Properties("Output Stream")=dataxml
cmd.Execute ,,1024

In the above example a transformation is being applied and the result being
placed inset the msxsml object for further processing.
In other cases we do not immediately apply a xsl and simply set the Output
Streeam to Response.

How would I go about this in .NET with the same results?

Thanks in Advance
Keith

 

[Irwin Dolobowsky [MSFT]]

For doing straight FOR XML queries you'd probably want SqlCommand and the
"ExecuteXmlReader" function. There are a lot of great articles up on MSDN on
how XML integrates with ADO.Net.

The example below is actually using an Xml Template to do the FOR XML query.
For that you'd have to use the managed classes available as part of SqlXml.
They are included in the latest release.

--
Thanks,
Irwin

Irwin Dolobowsky
Program Manager, SqlXml
http://blogs.msdn.com/irwando

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

 

[keith chadwick]

Yes i found that stuff, had forgotten to install the some stuff i needed.

I have a remaing problem. I have to get my web service to return the xml
returned from the SQL Server and I can not seem to get from the command
execute to the point of return 'something' in the web service?

Any suggestions?
Keith

 

[Dino Chiesa [Microsoft]]

In one app I did this:

private System.Xml.XmlDocument GetEntries(string strSQL) {
System.Xml.XmlTextReader xtr= null;
System.Xml.XmlDocument doc= null;
System.Data.SqlClient.SqlConnection dbconn= null;
try {
dbconn= GetDbConn();
System.Data.SqlClient.SqlCommand cmd= new
System.Data.SqlClient.SqlCommand(strSQL, dbconn);
cmd.Connection.Open();

xtr = (System.Xml.XmlTextReader) cmd.ExecuteXmlReader();
//xtr.WhitespaceHandling = System.Xml.WhitespaceHandling.None;
doc = new System.Xml.XmlDocument();
doc.Load(xtr);
}

catch (System.Exception e3) {
// SQL error
System.Console.WriteLine("Exception: e= " + e3 );
}

finally {
if (xtr!=null) xtr.Close();
if (dbconn!=null) dbconn.Close();
}
return doc;
}

Where the query was something like this:

SELECT 1 AS Tag,
NULL AS Parent,
'' as [FaqList!1!],
NULL as [Category!2!CategoryName!element],
NULL as [FAQ!3!ID!element],
NULL as [FAQ!3!timestamp!element],
NULL as [FAQ!3!Question!element],
NULL as [FAQ!3!Answer!element]

UNION ALL
select 2 as Tag,
1 as Parent,
'',
rtrim(c.[category name]),
NULL, NULL, NULL, NULL
FROM faq_categories c

UNION ALL
SELECT 3 AS Tag,
2 AS Parent,
'',
rtrim(c.[category Name]),
rtrim(f.ix),
f.timestamp,
rtrim(f.question),
rtrim(f.answer)

FROM faq_categories c, faq f
WHERE c.ix = f.category_ix
ORDER BY [Category!2!CategoryName!element], [FAQ!3!Question!element]
FOR XML EXPLICIT