March 21, 2003 at 7:39 am
Hi All,
Having gotten to grips with SQLXML for the first time, I'm very enthused by what it has to offer. I'm implementing an XML gateway for an internet site and I'd envisaged a lot of work in converting SQL tables/columns etc into XML syntax. But no! Provide an .XSD to take care of the SQL mapping and you can even query it using XPath.
This is the stage I've got to, but we're allowing the clients to specify in their requests as to whether the returned XML is to be sorted. I realise that the client can 'play' with the XML data using XSLT to sort any which way, but thought it would be good to provide this service if necessary.
However, I can see no way of sorting using the XPath query method. The code I'm using is:
oCmd.ActiveConnection = moCurrentConn.Connection
oCmd.CommandText = vXPathQuery ' The CommandText must be set to the XPath query
oCmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}" ' This is the dialect of the XPath query processor, believe it or not
oCmd.Properties("Base Path").Value = vSchemaDir ' Must be set to the schema document directory
oCmd.Properties("Mapping Schema").Value = vSchemaFileName ' Must be set to the schema document file name
oCmd.Properties("Output Encoding") = "utf-8" ' Encoding must be UTF-8 to cope with Greek etc
oCmd.Properties("ClientSideXML") = True
Set oStream = New ADODB.Stream
oStream.Open
oCmd.Properties("Output Stream").Value = oStream ' Supply a stream to receive the XML back
oCmd.Execute , , adExecuteStream ' Execute the query
' Close everything down
Set oCmd.ActiveConnection = Nothing
Set oCmd = Nothing
' Initialise and return the text stream
oStream.Position = 0
oStream.Charset = "utf-8"
Debug.Print oStream.ReadText(adReadAll)
It's all working wonderfully, when I pass in the query, schema directory and schema file name. But can I sort the resultant XML stream in the same hit?
Thanks in advance
Paul Armstrong
March 21, 2003 at 8:33 am
What I would suggest is writing an XSLT that does an xsl:sort and use a xsl parameter for the sort. You can then pass the users parameter on to the xsl, and even allow sorting ASC or DESC.
Tim C.
//Will write code for food
Tim C //Will code for food
March 21, 2003 at 10:59 am
Hi Tim,
Thanks for the reply. I had considered applying some XSLT as that was the only option I'd discovered during my searching. However, it seemed an excessive strain on the web server, if I could ensure that the database server delivered the XML already sorted. Is that a definiteive 'no' to being able to sort the data without using XSLT?
Thanks
Paul
March 21, 2003 at 11:42 am
No its not a definite no, but using xml template queries I am not sure how
else you could do the sort. If using a stored proc, you could pass in the
columns to sort with, and using a case statement (please no dynamic sql) sort
upon the column you need to sort with. Example here of the second method :
http://www.sqlservercentral.com/columnists/rmarda/howdynamiccanstaticbe.asp
Have you looked into using template queries with xsl transforms from the URL?
They provide awesome response times, and require very little coding. In fact I
read a study where they are actually faster than the coding solution. There are
quite a few examples in the SQLXML chm as well on the web to be found. If you
want I can post an example that runs against the pubs or Northwind sample db's.
One of the problem that I have so far with template queries is that you can
only use ROWSET providers right now for their data sources. So far I have used
views, tables, and table UDF's for them, but not being able to use a stored proc
is a definite drawback IMO. Otherwise they are nice, except for the sql they
generate behind the scenes. I got curious one time to see what was actually
going on when I ran a template query so I ran profiler and saw one of the
ugliest FOR XML EXPLICIT queries I could have imagined. So behind the scenes it
is parsing your XSD and determining how to interpret that into a FOR XML
EXPLICIT call. I have yet to see one of them turn into a query that was invalid,
or did not return the results I was looking for, but being the control freak I
am I decided to stick to stored procs returning my xml. Lastly, me and a friend
of mine have figured out a few XML AUTO tricks that let you do some very
interesting xml returns, so that 99.9% of the time I accomplish what I need with
XML AUTO. I am not saying that template queries are out, when I need to output
some very complicated xml they are definitely easier to write and maintain than
the corresponding FOR XML EXPLICIT T-SQL.
Sorry for the long winded post.... Hopefully I have not confused the issue too
much.
Tim C.
//Will write code for food
Tim C //Will code for food
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply