Very Computer

Any help will be appreciated! Thanks in advance.

1. Open IIS Virtual Directory Managment snapin:

start->programs->sqlxml 3->Configure IIS Support

2. Select the default web site.
3. Right click the right pane, select New->Virtual Directory
4. Name it vpubs
5. Click Browse, and create a new directory under inetpub/wwwroot called
vpubs
6. Click the security tab
7. Provide your security credentials (for example, User Name sa, and
password)
8. Click the Datasource tab
9. (local) should be the local SQL Server, but if SQL Server is elsewhere,
enter it's name, or select it using the "..." button.
10. Uncheck the Use Default database for current login checkbox.
11. From the dropdown, select "Pubs"
12. Click the Settings tab
13. *temporarily* check allow sql= or template= URL queries.  I say
temporarily, because this is clearly a security issue.  But it will prove to
you  that everything is working.
14. Click OK.

Once that's all done, open IE6 (or IE5/IE5.5, for that matter).

In the address bar, type:

http://myServerName/vpubs?root=foo&sql=SELECT a.au_fname, a.au_lname,
p.pub_name FROM authors AS a RIGHT OUTER JOIN publishers AS p ON a.city =
p.city ORDER BY p.pub_name ASC, a.au_lname ASC, a.au_fname ASC for XML AUTO

Hit "Enter"

You should see your XML.

Regards,
Mike Sharp

Quote:> I am looking for an example .... from soup to nuts... of
> executing a query against 2 tables (a RIGHT OUTER JOIN). I
> want the output in a xml format ... I want to know how I
> can view this result set this in IE6.x.

> Any help will be appreciated! Thanks in advance.

Greetings and thank you so very much for your reply.

I did follow the steps you outlined and it works for that
query (which works as suggested.) Then, I tried to modify
it a little for my own query at hand, and I give below the
error message that I received. Will appreciate very much
if you can help me understand that. Also, can you tell me
how would I proceed next to get the same results using ADO
in an VB6 application.[I am using Win XP Pro, SQL2K +
SQLZML 3.0 SP1]

The eror message I received is:

<?xml version="1.0" encoding="utf-8" ?>
- <iis_supremenet>
  <?MSSQLError HResult="0x80004005" Source="Microsoft XML
Extensions to SQL Server" Description="Streaming not
supported over multiple column result"?>
  </iis_supremenet>

I hope this will help you some in helping me some more.

Again thank you very much and appreciate it a whole lot!

kishan

Quote:>-----Original Message-----
>Set up a virtual directory using "Configure IIS Support"
in SQLXML 3 (you'll
>need to install SQLXML 3, if you haven't already).  It
does not have to be
>on the same machine as SQL Server, but it does have to be
installed on a
>machine with IIS.  BOL and the documentation with SQLXML
3 cover this pretty
>well, but here's the basic steps:

>1. Open IIS Virtual Directory Managment snapin:

>start->programs->sqlxml 3->Configure IIS Support

>2. Select the default web site.
>3. Right click the right pane, select New->Virtual
Directory
>4. Name it vpubs
>5. Click Browse, and create a new directory under

Quote:>vpubs
>6. Click the security tab
>7. Provide your security credentials (for example, User
Name sa, and
>password)
>8. Click the Datasource tab
>9. (local) should be the local SQL Server, but if SQL

Quote:>enter it's name, or select it using the "..." button.
>10. Uncheck the Use Default database for current login
checkbox.
>11. From the dropdown, select "Pubs"
>12. Click the Settings tab
>13. *temporarily* check allow sql= or template= URL
queries.  I say
>temporarily, because this is clearly a security issue.  

Quote:>you  that everything is working.
>14. Click OK.

>Once that's all done, open IE6 (or IE5/IE5.5, for that
matter).

>In the address bar, type:

>http://myServerName/vpubs?root=foo&sql=SELECT a.au_fname,
a.au_lname,
>p.pub_name FROM authors AS a RIGHT OUTER JOIN publishers
AS p ON a.city =
>p.city ORDER BY p.pub_name ASC, a.au_lname ASC,

Dim xDOM As New MSXML.DOMDocument
Dim rsXML As New ADODB.Recordset
Dim sSQL As String, sConn As String

sSQL = "SELECT customerid, companyname, contactname FROM customers"
sConn="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Program Files" & _
        "\Common Files\System\msadc\samples\NWind.mdb"
rsXML.Open sSQL, sConn
rsXML.Save xDOM, adPersistXML   'Save Recordset directly into a DOM tree.

The resulting XML will have a particular format, and be qualified to a
namespace.  If you need to transform it (using XSLT), you'll need to take
that into account.  Here's an example of that process:

http://www.rdcpro.com/xmldev/groupinggrades/

As far as your error message goes, what is the query you're using?  (if
you're free to disclose it).  My brain is drawing a blank, and I can't for
the life of me remember what causes the error.  If I see your query, perhaps
my brain will reset ;^)

Also, you might try using FOR XML RAW instead of AUTO.  IIRC, aggregate
functions like count() don't work with AUTO, but do work with RAW.  However,
I don't think that's the issue here...

Regards,
Mike Sharp

Greetings and thank you so very much for your - MOST
PROMPT - reply. I truly appreciate it a whole lot more
than I can express.

First, about the error message. I was goofy ... skipping
the FOR XML AUTO in my query. When I realized that and
made the necessary correction, my query worked just fine
in the url!

Now 2 other questions (if you can forgive me for all my
questions!)

1. How can I get the effect of FOR EXPLICIT since I am
getting a whole row in the current result set, in other
words, instead of, for each element separately.

2. Coming back to your original reply (step # 13,
*temporary *), how would I accomplish all this in a non-
url-way (so that there would be no security issues, at
least as far as the url-way is concerned. Since, I know
that it is working now.)

Thanks again so very much.

kishan

Quote:> Hello Mike:

> Greetings and thank you so very much for your - MOST
> PROMPT - reply. I truly appreciate it a whole lot more
> than I can express.

> First, about the error message. I was goofy ... skipping
> the FOR XML AUTO in my query. When I realized that and
> made the necessary correction, my query worked just fine
> in the url!

> Now 2 other questions (if you can forgive me for all my
> questions!)

> 1. How can I get the effect of FOR EXPLICIT since I am
> getting a whole row in the current result set, in other
> words, instead of, for each element separately.

Select fname as GivenName, lname as Surname from Authors for XML AUTO

This will produce:

<Authors GivenName="Mike" Surname="Sharp"/>

If  you want the values as child elements, just modify the FOR XML AUTO to
look like:

FOR XML AUTO, ELEMENTS

which changes the XML output to look like:

<Authors>
    <GivenName>Mike</GivenName>
    <Surname>Sharp</Surname>
</Authors>

Quote:> 2. Coming back to your original reply (step # 13,
> *temporary *), how would I accomplish all this in a non-
> url-way (so that there would be no security issues, at
> least as far as the url-way is concerned. Since, I know
> that it is working now.)

So, I create a subfolder under my vpubs folder (using Windows Explorer).
Then I create a new virtual name, called for example, "template"
From the drop-down, I select "Template"
I browse to the folder I created previously, which enters the path to the
Template folder.
I click Save.

Now I create an XML document and put it in that folder, saving it as
"authorlist.xml".  The XML document might look like:

<?xml version="1.0" encoding="UTF-8"?>
<Author xmlns:sql="urn:schemas-microsoft-com:xml-sql">
 <sql:query>
  SELECT
   a.au_fname,
   a.au_lname,
   p.pub_name
  FROM
   authors AS a
  RIGHT OUTER JOIN
   publishers AS p
  ON
   a.city = p.city
  ORDER BY
   p.pub_name ASC,
   a.au_lname ASC,
   a.au_fname ASC
  FOR XML AUTO, ELEMENTS
 </sql:query>
</Author>

I get the XML by simply using the URL of the template I just created.

http://localhost/vpubs/template/authorlist.xml

You must use IIS to get the template--you can't for example use:
c:\inetpub\wwwroot\vpubs\template\authorlist.xml

You can actually put all this code in a stored procedure, which is better,
and call the sproc from the template, like:

<?xml version="1.0" encoding="UTF-8"?>
<LocReports xmlns:sql="urn:schemas-microsoft-com:xml-sql">
 <sql:query>
  EXECUTE sp_getAuthorList
 </sql:query>
</LocReports>

You can even pass parameters in, like:

<?xml version="1.0" encoding="UTF-8"?>
<CustomerList xmlns:sql="urn:schemas-microsoft-com:xml-sql">
 <sql:header>
  <sql:param name="custID">%</sql:param>
 </sql:header>
 <sql:query>
  SELECT
   *
  FROM
   Customers
  WHERE

  FOR XML AUTO, ELEMENTS
 </sql:query>
</CustomerList>

You use the template with a parameter like:

http://localhost/northwind/template/CustomerList.xml?custID=ALFKI

If you don't supply a parameter, the default of "%" is used.

Regards,
Mike Sharp

Quote:> Thanks again so very much.

> kishan