SQL2000 XML

SQL2000 XML

Post by Samuel Goldenbau » Wed, 06 Jun 2001 23:40:56



I see that SQL2000 lets me retrieve XML using FOR XML AUTO

How do I call this from ASP/COM? If I open a recordset that runs "SELECT *
FROM Table FOR XML AUTO"
How do access the XML in the recordset ie
Response.write rsTemp.**** ?

Hope this makes sense.

Sam

 
 
 

SQL2000 XML

Post by Martin Forsber » Thu, 07 Jun 2001 00:53:14


Hi,

I had some problems retrieving the xmldata at first but this code works
fine:

Replace(RS.GetString, Chr(13), "")

The replacefunction is used to delete the linefeed that for some reason is
inserted when the xmlstring becomes to long...

/Martin


Quote:> I see that SQL2000 lets me retrieve XML using FOR XML AUTO

> How do I call this from ASP/COM? If I open a recordset that runs "SELECT *
> FROM Table FOR XML AUTO"
> How do access the XML in the recordset ie
> Response.write rsTemp.**** ?

> Hope this makes sense.

> Sam


 
 
 

SQL2000 XML

Post by Samuel Goldenbau » Thu, 07 Jun 2001 00:14:58


Thanks for the answer Martin

How do I refernce the XML in the recordset. If I wanted to write it out, how
would I reference the XML in the recordset to manipulate it?

ie
Dim rsTemp
Set rsTemp = oConn.execute("SELECT * FROM Table FOR XML AUTO")
response.write rsTemp??????

Thanks

Sam


> Hi,

> I had some problems retrieving the xmldata at first but this code works
> fine:

> Replace(RS.GetString, Chr(13), "")

> The replacefunction is used to delete the linefeed that for some reason is
> inserted when the xmlstring becomes to long...

> /Martin



> > I see that SQL2000 lets me retrieve XML using FOR XML AUTO

> > How do I call this from ASP/COM? If I open a recordset that runs "SELECT
*
> > FROM Table FOR XML AUTO"
> > How do access the XML in the recordset ie
> > Response.write rsTemp.**** ?

> > Hope this makes sense.

> > Sam

 
 
 

SQL2000 XML

Post by Martin Forsber » Thu, 07 Jun 2001 01:19:49


Hi

Like this:

response.write Replace(RS.GetString, Chr(13), "")
Or just:
response.write RS.GetString '(but look out for linefeeds!)


> Thanks for the answer Martin

> How do I refernce the XML in the recordset. If I wanted to write it out,
how
> would I reference the XML in the recordset to manipulate it?

> ie
> Dim rsTemp
> Set rsTemp = oConn.execute("SELECT * FROM Table FOR XML AUTO")
> response.write rsTemp??????

> Thanks

> Sam



> > Hi,

> > I had some problems retrieving the xmldata at first but this code works
> > fine:

> > Replace(RS.GetString, Chr(13), "")

> > The replacefunction is used to delete the linefeed that for some reason
is
> > inserted when the xmlstring becomes to long...

> > /Martin



> > > I see that SQL2000 lets me retrieve XML using FOR XML AUTO

> > > How do I call this from ASP/COM? If I open a recordset that runs
"SELECT
> *
> > > FROM Table FOR XML AUTO"
> > > How do access the XML in the recordset ie
> > > Response.write rsTemp.**** ?

> > > Hope this makes sense.

> > > Sam

 
 
 

SQL2000 XML

Post by Greg Nor » Thu, 07 Jun 2001 01:30:28


| How do I call this from ASP/COM? If I open a recordset that runs "SELECT *
| FROM Table FOR XML AUTO"
| How do access the XML in the recordset ie
| Response.write rsTemp.**** ?

I don't think you're supposed to use a Recordset object but rather a Stream
object. I'm not a VB person (VC instead), but this is what I'm seeing from
my digs through MSDN. Now if someone could post how this needs to be done in
VC, that would be great. The docs in MSDN are pretty sparse when in comes to
VC++ and ADO/SQL Server 2K programming.

greg

-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----==  Over 80,000 Newsgroups - 16 Different Servers! =-----

 
 
 

SQL2000 XML

Post by Mikhail Berlyan » Thu, 07 Jun 2001 07:29:06


I have answered this already in microsoft.public.xml.

You can retrieve XML stream only through stream object.
Example:

    Set conn = New ADODB.Connection
    conn.Open strYourConnectionString
    Set str = New ADODB.Stream
    str.Mode = adModeRead
    str.Open
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = conn
    cmd.CommandText = "Your_stored_proc_with_XML_AUTO"
    cmd.CommandType = adCmdStoredProc
    cmd.Properties("Output Stream").Value = str
    cmd.Execute , , adExecuteStream
    strXML = str.ReadText(-1)

--
Mikhail Berlyant
Brainbench MVP for Visual Basic
http://www.brainbench.com
Distribution Systems Lead, Data Systems
http://www.launch.com


Quote:> I see that SQL2000 lets me retrieve XML using FOR XML AUTO

> How do I call this from ASP/COM? If I open a recordset that runs "SELECT *
> FROM Table FOR XML AUTO"
> How do access the XML in the recordset ie
> Response.write rsTemp.**** ?

> Hope this makes sense.

> Sam

 
 
 

SQL2000 XML

Post by Andy Cheun » Sat, 09 Jun 2001 19:47:22


Hi I have problems at this line:

cmd.Properties("Output Stream").Value = str

the message is:

ADODB.Command Error '800a0cc1'
item cannot be found in the collection corresponding to the requested name or ordinal.

How should I overcome this? Thanks!

Please Visit My *!!
http://www.veryComputer.com/

*** Sent via Developersdex http://www.veryComputer.com/ ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. Question on SQL2000 XML Output to OSQL...

I'm trying to get a text file that consists of XML output
from a SQL Server Stored Procedure.  WHen I run the stored
procedure call in Query Analyzer, the XML comes back as
expected, however if I try to run the same SP call from
OSQL, the output file contains a great deal of whitespace
put into the file.  DOes anyone have any idea why this
might be the case?

Here (Setup.sql) is a file to set up the Table, data and
stored procedure (for SQL Server 2000, any addition).

and here is a sample of the following OSQL call...

osql /E /d tempdb -h-1 /Q "EXEC sp_Print_Prod_Info2
388732" >test.txt

Any help on this would be most appreciated, this is
getting frustrating....

Scott Coutant

  Setup.sql
9K Download

2. DateTime

3. sql2000 - XML output

4. Iteration

5. problems to run demo of SQL2000 XML Demo in the Web Services Toolkit

6. 18504-FL-ORLANDO-ORACLE-ORACLE Financials-ORACLE Reports 2.X-Developer 2000-ORAC

7. SQL2000, XML, ADO and SOAP

8. Help in number conversion

9. SQL2000 XML Question

10. SQL2000/XML - XSL Issue

11. SQL2000, XML, ADO and SOAP

12. xml and sql2000