Solved

SQL XML XPATH!!

Posted on 2003-12-05
17
491 Views
Last Modified: 2013-11-19
Dear smart person,

Okay, I'm on a Win2k box with SQL 2000 and i want to start using XPath.  Here is the purpose:

I users on my site, and i want them to access their profiles by:
www.server.com/users/username/

From what I've understood so far, I have created a Virtual Directory in SQLXML called Users and enabled XPath.  What do I do from there?

I know this question is really indescript, but I can't think of a better way to put it, so I left it as this and put it as 500 pts

Thanks!!
0
Comment
Question by:BeamerCola
  • 9
  • 8
17 Comments
 
LVL 26

Expert Comment

by:rdcpro
ID: 9885592
You'll also need to create an annotated schema, that tells SQL XML what your XML needs to look like, and relates the elements to the tables and columns of your DB.  This used to be known as "XML View" but now is known as an annotated mapping schema.  For example, here's an XDR Schema for the Northwind Customers table:

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
        xmlns:dt="urn:schemas-microsoft-com:datatypes"
        xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <ElementType name="Customer" sql:relation="Customers" >
    <AttributeType name="CustomerID" />
    <AttributeType name="ContactName" />
    <AttributeType name="Phone" />

    <attribute type="CustomerID" />
    <attribute type="ContactName" />
    <attribute type="Phone" />
  </ElementType>
</Schema>

You might put this XDR schema the schema subdirectory of the virtual root directory, and call it SchemaCustomers.xdr.

An XPath query against the annotated schema can be specified directly in the URL:

http://IISServer/VirtualRoot/schema/SchemaCustomers.xdr/Customer[@CustomerID="ALFKI"]

Or you can specify it in a template:

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
  <sql:xpath-query mapping-schema="schema/SchemaCustomers.xdr">
    /Customer[@CustomerID="ALFKI"]
  </sql:xpath-query>
</ROOT>


The mapping schema file path is relative the virtual root, not the location of the template.

Regards,
Mike Sharp

0
 

Author Comment

by:BeamerCola
ID: 9886771
Man, you're on top of this.  I love you.

Okay, tell me if i did this right.

Copied your Schema stuff, and put it in a file called Users.xdr in my XML folder

I then opened IIS Virtual directory Manager for SQL Server and opened the properties for my virtual directory "Users" underneath my server.  on Virtual Names I put one named "Users" as a Schema type and pointed it to the xdr file.

What happens from there to be able to access information by
www.server.com/users/username

Also, is it possible to run ASP scripts in there? or will it be strictly XML?

Thanks so much
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 9887091
The Schema virtual name should point to a folder, which contains the users.xdr.

It would then be something like:

www.server.com/VirtualRoot/users/users.xdr/username

And don't put anything else like ASP in those folders.  Well, I've put XSLT in there...

Regards,
Mike Sharp
0
 

Author Comment

by:BeamerCola
ID: 9888942
Okay this is starting to make more sense.

now how do i get it to read an XML file to show that information?

and will you have to type in users/users.xdr/username?  how would you get it where its pretty, and just says
server.com/users/username/ ?

Thanks so much for all your help, i'd increase the points, but i cant.
0
 

Author Comment

by:BeamerCola
ID: 9888948
oh i think i'm seeing that its supposed to be pulling from the database?  heres my xdr file

<?xml version="1.0" ?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
      xmlns:dt="urn:schemas-microsoft-com:datatypes"
      xmlns:sql="urn:schemas-microsoft-com:xml-sql">

  <ElementType name="User" sql:relation="tbl_Users" >
      <AttributeType name="UserID" />
      <AttributeType name="UserName" />
      
      <attribute type="UserID" />
      <attribute type="UserName" />
  </ElementType>
</Schema>

tbl_Users is the table with UserID and UserName in it..
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 9891336
That looks good...If I recall correctly, as long as your attribute names are the same as the column names in the table, you don't have to map the attribute to the column.  But if you wanted to change the name of your UserID column in the DB to BloggerID in the XML (for example) you can annotate the attribute type to define the mapping.

As I mentioned before, you can get the XML either by loading the "URL" of the schema, and passing an XPath expression on the URL, or you can put the XPath in a template and use that.  The template approach is a little more secure, in that you can control the XPath's that the user can use.

Regards,
Mike Sharp
0
 

Author Comment

by:BeamerCola
ID: 9891405
When I go to

www.server.com/Users/Users.xdr/BeamerCola/

i get
HResult: 0x80040e14
Source: Microsoft XML Extensions to SQL Server
Description: MSXML3: is an invalid or unsupported XPath
--
i think i'm confused on what's supposed to happen.  is XPath what I'm looking for?  I bought an XQuery book today to start learning about that, i'm now confused on both.

I want to be able to go to
www.server.com/users/beamercola/
and it pull up my profile, formatting it with CSS

Thanks,
beamer

0
 
LVL 26

Expert Comment

by:rdcpro
ID: 9891462
Users is a virtual name of type Schema, right?  It is a child of your virtual root?  That is, you have a virtual root that is configured using SQL XML to be:

www.server.com/vroot

and then you add a schema type below it, like:

www.server.com/vroot/users

and in the users schema directory is the schema you create, users.xdr.

So you should use a path like:

www.server.com/vroot/users/users.xdr/User[@UserID = 'BeamerCola']

Regards,
Mike Sharp

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:BeamerCola
ID: 9892117
i have Users as a Virtual Directory, and in the Virtual Names tab, i've set up Users as a Schema pointing to Users.xdr

is it possible to not use a root?

And do you always have to specify users.xdr, and [@UserID = 'BeamerCola'] ?
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 9894388
I'm not sure if it works with the schema virtual name pointing to the same directory as the vroot.  

As far as the XPath, well, that's how XPath works.  You probably want to use a template like I showed you in the earlier post.  Then you'd use something more conventional like:

www.server.com/vroot/users/getUser.xml?UserID=BeamerCola

Regards,
Mike Sharp


0
 

Author Comment

by:BeamerCola
ID: 9894405
Maybe XPath is not what I'm looking for.  Pretty much the only reason i'm implementing this is for ease-of-use for the users by being able to go to "www.server.com/users/beamercola/" to access their information.  The quick explanation in SQLXML showed XPath as the thing for that.

if XPath isn't that, then what would i be looking for?

-beamer
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 9894887
Well, that something you get with some application servers.  For example, my site, http://dev.rdcpro.com has a system of parsing the URL, so that each "part" of the URL is separate.  So, my member pages are located at:

http://dev.rdcpro.com/Members/

and my own particular page is:

http://dev.rdcpro.com/Members/rdcpro

But when I log in, no matter where I am in the site I can always click the link on the horizontal nav bar that takes me to a page where I set my preferences.  Because I'm logged in, it knows who I am.  You don't need a separate page for each user, you need a single page, so that authenticated users can see their information.  What's to stop someone from entering another user's URL?  Really, what you're wanting is a page that will take the user id from the session variable (where they logged in), and query the DB and get the appropriate User data which is rendered in the page.  

Regards,
Mike Sharp



0
 

Author Comment

by:BeamerCola
ID: 9899358
It's a public site, so I want people to be able to type in other peoples usernames.  I'm also going to be implementing this into a news site i'm working on, which will work like:

domain.com/articles/2003/jan/001/

and that would pull up article 001.  so it would skim through the XML document to Articles, then inside that find 2003, then january, and then 001.
0
 
LVL 26

Accepted Solution

by:
rdcpro earned 500 total points
ID: 9899767
This is a very common approach for some application servers, but not really for IIS, at least as far as I know.  I think you'll have to write an ISAPI filter that does the parsing of the URL to extract the query information, and map it to the appropriate resources.  And that sounds like a lot of work.  It's easier to use query strings, as I mentioned earlier.  However, if you really must have that type of query, you might consider other platforms, such as Zope, which does this very well.  It can run on Windows, behind IIS, but also comes with it's own web server (Medusa).  ISPs usually run it behind Apache.  Zope is programmed in Python, though, and it means you'll probably have to learn a new language.  But, it's a very elegant OO language, and worth learning.  And, as a bonus, your RSS is built-in, along with XML-RPC and a whole raft of other cool stuff.  

But the best part is that it's free, and has a pretty talented community behind it.  The only drawback is that there aren't many ISPs who specialize in it, and although you could manage it yourself, it's nice to have a host that understands the issues.  If you self host (through a local DSL connection or something), then it's worth trying out anyway, because it will work fine out of the box responding to port 8080 by default. So it can co-exist peacefully with your existing site.

Regards,
Mike Sharp
0
 

Author Comment

by:BeamerCola
ID: 9900224
Thanks for all your help,

Do you know what that kind of approach is called?  So i could look around to see the possibilities in IIS?

I guess I'm a little confused, when I look in the IIS Virtual Directory Management for SQL in the Settings tab, it shows for the XPath example as:

Allows users to execute XPath queries over SQL Views provided by mapping schemas.
http://server/virtualdirectory/cust/CustomerList

how would that be different from
http://server.com/Users/UserName/SubInformation

Thanks so much for your help.
0
 
LVL 26

Expert Comment

by:rdcpro
ID: 9900809
Right, but the XPath "CustomerList" essentially returns a nodelist containing the nodes for all customers.  If you want  a particular customer, you have to use a predicate like:

http://server/virtualdirectory/cust/CustomerList[@custID = 'ALFKI']

I can't recall the term for that URL approach...but it's fairly common.

Regards,
Mike Sharp




0
 

Author Comment

by:BeamerCola
ID: 9905014
Thanks!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Most of the sites are being standardized with W3C Web Standards. W3C provides lot of web standard services to the web. They have the web specification, process and documentation for all the web standards. You can apply HTML, CSS and Accessibility st…
Preface This article introduces an authentication and authorization system for a website.  It is understood by the author and the project contributors that there is no such thing as a "one size fits all" system.  That being said, there is a certa…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
The viewer will receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now