Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP .Net Web Services > How do I use SQL 'FOR XML' with .NET?

Reply
Thread Tools

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

 
 
keith chadwick
Guest
Posts: n/a
 
      10-27-2004
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-comml-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





 
Reply With Quote
 
 
 
 
Irwin Dolobowsky [MSFT]
Guest
Posts: n/a
 
      10-27-2004
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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-comml-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
>
>
>
>
>



 
Reply With Quote
 
 
 
 
keith chadwick
Guest
Posts: n/a
 
      10-27-2004
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

"Irwin Dolobowsky [MSFT]" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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-comml-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
>>
>>
>>
>>
>>

>
>



 
Reply With Quote
 
Dino Chiesa [Microsoft]
Guest
Posts: n/a
 
      10-28-2004
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







"keith chadwick" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> "Irwin Dolobowsky [MSFT]" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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-comml-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
>>>
>>>
>>>
>>>
>>>

>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Re: How include a large array? Edward A. Falk C Programming 1 04-04-2013 08:07 PM
SQL Reference, SQL Queries, SQL help ecoolone ASP .Net 0 01-03-2008 10:58 AM
Do the Self-Paced Training Kits: Microsoft SQL Server 2000 include Eval copy of SQL Server? Brian Whiting Microsoft Certification 2 12-29-2005 04:24 AM
DBI SQL column datatype not jiving with SQL statement requirement dna Perl 1 01-18-2004 04:15 PM
Re: SQL statement working in SQL Server but not in .aspx.cs page William \(Bill\) Vaughn ASP .Net 0 08-21-2003 10:41 PM



Advertisments