SQL XML Stored Procedure using XML Explicit Returing XML to ASP Page

SQL XML Stored Procedure using XML Explicit Returing XML to ASP Page

Post by Phi » Sat, 31 May 2003 23:25:06



I have a SP that uses XML Explicit,  For example say this SP returns
the following XML

<person first="john" last="doe">
   <City>Some City</City>
   <Age>222</Age>
</person>

If i run the Sp from Query Analyzer it returns this data fine, but i
am having trouble executing the SP from an ASP page and getting the
same data back.

I need to be able to take this XML and Transform it using an XSL
stylesheet.

How can this be done from ASP.

Thanks for any help.

 
 
 

SQL XML Stored Procedure using XML Explicit Returing XML to ASP Page

Post by Mike Shar » Sun, 01 Jun 2003 01:59:16


One way would be to create a virtual directory, using SQLXML's Internet
Support.  Call the SP from a template in the virtual directory, like:

<LocReports xmlns:sql="urn:schemas-microsoft-com:xml-sql">
 <sql:header>
  <sql:param name="myParam"></sql:param>
 </sql:header>
 <sql:query>

  </sql:query>
</LocReports>

If the SQL Server is on your network, you can use an XML DOM object to load
the XML in your ASP page.  You'll probably have to configure the XML DOM
object with the property "ServerHTTPRequest" set to true:


<%
var xmlDoc;
xmlDoc = Server.CreateObject("Msxml2.DomDocument");
xmlDoc.async = false;
xmlDoc.setProperty("ServerHTTPRequest", true);
xmlDoc.load(http://myserver.com/virDirectory/virTemplate/myTemplate.xml);
%>

Regards,
Mike Sharp


Quote:> I have a SP that uses XML Explicit,  For example say this SP returns
> the following XML

> <person first="john" last="doe">
>    <City>Some City</City>
>    <Age>222</Age>
> </person>

> If i run the Sp from Query Analyzer it returns this data fine, but i
> am having trouble executing the SP from an ASP page and getting the
> same data back.

> I need to be able to take this XML and Transform it using an XSL
> stylesheet.

> How can this be done from ASP.

> Thanks for any help.


 
 
 

SQL XML Stored Procedure using XML Explicit Returing XML to ASP Page

Post by Phil » Sun, 01 Jun 2003 04:23:15


Vikrant,
Thanks for the Reply.  But how can i access the sample
code you attached (menu.asp,menu.xsl)??

If you could email them to me that would be great.

Thanks Again
Phil

Quote:>-----Original Message-----

>Hello Philb,

>I am attaching some sample code where the menu.asp calls
the getCategories
>stored proc . Menu.xsl is the
>style sheet applied to the XML returned data.

>CREATE PROC getCategories
>AS
>SELECT CategoryID, CategoryName FROM Categories
>FOR XML AUTO, ELEMENTS

>The getCategories procedure retrieves the list of
categories in the
>following XML format:

><categorylist xmlns:sql="urn:schemas-microsoft-com:xml-
sql">
>    <Categories>
>        <CategoryID>1</CategoryID>
>        <CategoryName>Computers</CategoryName>
>    </Categories>.......and so on..

>Also,

>There are several samples on this:

>272269 HOWTO: Retrieve XML Data by Using a SQL XML Query
in an ASP Client

>http://support.microsoft.com/?id=272269

>272266 HOWTO: Retrieve XML Data by Using a Template File
in an ASP Client

>http://support.microsoft.com/?id=272266

>http://support.microsoft.com/default.aspx?

scid=/servicedesks/webcasts/wc0606

- Show quoted text -

Quote:>02/wcblurb060602.asp

>Based on the above samples, you  should be able to work
with SP to return
>XML data.

>Thanks,

>Vikrant Dalwale

>Microsoft SQL Server Support Professional

>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Get secure !! For info, please visit

http://www.microsoft.com/security.
>Please reply to Newsgroups only.

>--------------------

>| Newsgroups: microsoft.public.sqlserver.xml
>| Subject: SQL XML Stored Procedure using XML Explicit

Returing XML to ASP
Quote:>Page
>| Date: 30 May 2003 07:25:06 -0700
>| Organization: http://groups.google.com/
>| Lines: 19
>| Message-ID:


>| NNTP-Posting-Host: 141.189.251.1
>| Content-Type: text/plain; charset=ISO-8859-1
>| Content-Transfer-Encoding: 8bit
>| X-Trace: posting.google.com 1054304707 16707 127.0.0.1
(30 May 2003
>14:25:07 GMT)

>| NNTP-Posting-Date: 30 May 2003 14:25:07 GMT
>| Path:
>cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!

newsfeed00.sul.t-online.de!t-onlin
Quote:>e.de!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!sn-

xit-03!sn-xit-06!sn-
Quote:>xit-09!supernews.com!postnews1.google.com!not-for-mail
>| Xref: cpmsftngxa06.phx.gbl

microsoft.public.sqlserver.xml:18655

- Show quoted text -

Quote:>| X-Tomcat-NG: microsoft.public.sqlserver.xml
>|
>| I have a SP that uses XML Explicit,  For example say
this SP returns
>| the following XML
>|
>| <person first="john" last="doe">
>|    <City>Some City</City>
>|    <Age>222</Age>
>| </person>
>|
>|
>| If i run the Sp from Query Analyzer it returns this
data fine, but i
>| am having trouble executing the SP from an ASP page and
getting the
>| same data back.
>|
>| I need to be able to take this XML and Transform it
using an XSL
>| stylesheet.
>|
>| How can this be done from ASP.
>|
>| Thanks for any help.
>|

 
 
 

1. Broken XML when running queries with FOR XML AUTO and FOR XML EXPLICIT

Hi,
I am currently running SQL Server 2000 SP 1 on Windows NT 5.0
When I try to run a query with either FOR XML AUTO or FOR XML EXPLICIT everything
goes fine, except for one thing:

The resulting XML comes in a series of rows each long 2033 characters. At the end
of each row there is an EOL charachter and is invalid XML.

When I try to read the XML geneated this way, IE stops at the first EOL.
I tried using all the possible encodings,needless to say, I do not manage to
identify the character code.

If I manually edit the file deleting all these characters, everything works.

Any Idea?

Thanks,

Stefano

2. ebu 2.2.0.7 patch question

3. Generating Schema compliant XML from SQL Server Database using SQLXML and XML Explicit -- [Performance/Maintenance issues]

4. 24405-IA-Eddyville-LAN/WAN-PC-Communication Skills-ORACLE-Client/Server-PC-Techn

5. problem in creating XML using For XML explicit

6. xbase to sybase

7. Q: Hiding/not showing a column in the resulting xml using xml explicit

8. Can not connect to database using jdbc

9. Facing problem in creating XML using FOR XML EXPLICIT

10. SQL Server 2K, XML/XSL for Explicit problem with passing XML paramters values

11. SQL Query with FOR XML Explicit results in errors in XML

12. XML FILE from FOR XML EXPLICIT

13. XML Order (For xml Explicit)