Re: Display SQL XML Column

Terry wrote:
How do I display an SQL XML data type (column) within classic ASP?

Here's an example I wrote (err, adapted from a SQL BOL topic ... ahem) a
while ago. It applies to using the For XML clause, but hopefully it applies
(or can be adapted) to the the new XML datatype as well. I have not had
occasion to utilize that functionality in classic ASP so I have no examples
written. You might want to check SQL2005 BOL but IIRC, the examples in it
only applied to .Net <grrr>

Anyways, I hope it helps:

<%@ Language=VBScript %>
<%
option explicit
Response.ContentType="text/xml"
dim cn, rs,sQuery, cmd
Const adExecuteStream = &H00000400
Const adCmdText = &H0001
set cn=server.CreateObject("adodb.connection")
cn.open "provider=sqloledb;data source=????;" & _
"user id = ???; password=????;initial catalog=northwind"
Set cmd = Server.CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn
sQuery = "SELECT * FROM PRODUCTS ORDER BY PRODUCTNAME FOR XML auto"
cmd.CommandText = sQuery
cmd.Properties("xml root").Value = "root"
cmd.Properties("Output Stream") = Response
cmd.Execute , , adExecuteStream + adCmdText
set cmd=nothing
cn.close
set cn=nothing

%>

I put the "Response.ContentType ..." line in so you could see that the
results were XML. You don't need that line in your code. Instead of
"cmd.Properties("Output Stream") = Response", you can instantiate a dom
object and have the results stream into it instead: "cmd.Properties("Output
Stream") = xmldoc"



--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


.

Relevant Pages

  • Re: Data table text I/O package?
    ... regular and homogenuous data in mind. ... Given a data format much like in, ... I said XML is good for parsing of data if you cannot tell in advance that the data stream is totally free of errors. ... However, if some data item contains a separator due to an error, you loose the whole stream, or use the wrong data without noticing this, in the worst case. ...
    (comp.lang.ada)
  • RE: Unparsed Interchange
    ... but have you tried setting your stream position back ... from the received one (flat recieved -> xml out with some new parameters). ... // Get the original filename ... string filename = inmsg.Context.Read("ReceivedFileName", ...
    (microsoft.public.biztalk.general)
  • Error message when opening a recordset with a strem containing xml
    ... I have a problem when trying to open a recordset with a stream including xml ... System.String schemaText = null; ...
    (microsoft.public.data.ado)
  • Re: Processing Word documents
    ... where it can take an already opened stream. ... to Biztalk do that to convert to XML which BizTalk then reads, ... Second option is to develop a custom pipeline component that would ...
    (microsoft.public.biztalk.general)
  • Updategram with ADO and C++...parse error
    ... I'm trying to use the SQLXML Updategram feature to put XML data into a SQL ... Server 2000 database in conjunction with using ADO Stream objects. ...
    (microsoft.public.sqlserver.xml)