SQLXML 101

SQLXML 101

Post by kisha » Fri, 13 Jun 2003 01:31:43



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.

 
 
 

SQLXML 101

Post by Mike Shar » Fri, 13 Jun 2003 02:40:54


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 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.


 
 
 

SQLXML 101

Post by Kisha » Fri, 13 Jun 2003 04:23:59


Hello Mike:

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

inetpub/wwwroot called
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

Server is elsewhere,
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.  

But it will prove to
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,

a.au_fname ASC for XML AUTO

- Show quoted text -

>Hit "Enter"

>You should see your XML.

>Regards,
>Mike Sharp



>> 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.

>.

 
 
 

SQLXML 101

Post by Mike Shar » Fri, 13 Jun 2003 05:19:42


With ADO, it's even easier (though you don't really have the flexibility in
how the XML is constructed).  When you create the recordset object, use the
save method to directly stream the recordset into an XML Dom:

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


> Hello Mike:

> 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
> >-----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
> 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



> >> 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.

> >.

 
 
 

SQLXML 101

Post by Kisha » Fri, 13 Jun 2003 05:51:31


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.

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

 
 
 

SQLXML 101

Post by Mike Shar » Sat, 14 Jun 2003 05:06:23


Responses inline...


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.

Well, you can modify the tagnames by using aliases in your query, such as

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.)

There are several ways, but I like using templates.  You can read about them
on BOL, and in the SQLXML documentation, but essentially you create a
virtual name in the same virtual directory managment snap-in (the Virtual
Names tab).

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

- Show quoted text -

Quote:> Thanks again so very much.

> kishan

 
 
 

1. A couple of DB 101 questions.

Hello:

I am new to database programming and I have been having difficulty
understanding some of what must be the basics of database programming with
MFC.  I have a few questions that I would GREATLY appreciate help with;

1. If I want to allow the user to select from a set of existing databases,
do I need to add a new entry for each database?

2. If the answer to 1 is yes, then how do I programmatically create a new
database?

3. Is there a difference between a database file and a data source?

4. Why does all of this have to be so damn hard?

Thanks again for the advice, I'm looking forward to your responses  In the
mean time I am reading the book TYS DB Programming w/ VC++6.

Jim Houskeeper

2. US-OR-Project Manager

3. CONVERT(datetime,GETDATE(),101) !!!!!!

4. HELP with stored procedures

5. Replicating 101

6. Pick Programmer/Analysts - Portland, Oregon

7. certification exams for 1z0-101

8. Rename File As String?

9. C++ 101 - basic casting problem in ODBC Connection

10. Retrieving rows 101-110

11. Please check this SQL code - SQL 101

12. US-NC-Peoplesoft Programmer, DB Basics, Inc. #101

13. Replication 101