Use a transform against SQL Server

Use a transform against SQL Server

• CharlieWasAnAngel

  will do.
  
  

• RakeshGupta

  I would recommend checking out our native XML support in SQL Server 2005.  It allows you to store XML as a datatype and also allows server side XQuery (which is a superset of XPath).  You may be able to take advantage of it to do something along the lines of your solution #3 (and store/query/update the data using our built-in XQuery functionality).   

  
  

• Igor Rosenberg

  you got it!
  Find myself with 3 options atm:
  
  1.  Retrieve all rows from my database where the only constraint is the gametype, and from there, convert the results the xml, apply the transform and return to the client.
  
  or
  
  2.  Create a parser for the transform provided by client that will essentially create the correct T-SQL query.  My issue with this, is that my architecture provides clients with the ability to create these server filters using a heirarchial set of transforms, which get applied after one another, which could present some issue building the final query.
  
  or
  
  3.  Instead of storing the server information in sql server, simply store it xml files.  I can then create a "service" that will load up the servers into memory.  A web service will then have to contact the "service" via messaging to get its data.  It is necessary for me to load it into memory, not only for performance reasons, but also that the "service" needs to update the servers' information continually so that my users are provided with updated information.
  
  I most certainly appreciate your responses.  Thank you.
  
  If you have any other ideas for my situation, please let me know.
  
  

• Julian12

  John Gallardo - MSFT wrote:

  You will probably also want to investigate our FOR XML functionality in order to convert your scalar rowset into XML on the database.

  
  
  yeah, after reading that blog, I'm not quite sure if it meets my needs.  im trying to avoid the IO out of the db, but if all I end up doing is extrating all the rows out of the db, whether scalar or xml, then the IO is already there.  I might as well just retrieve it all into a DataSet, convert to xml and transform it.
  
  I was really hoping for a way to bypass transforming the rows into xml just to apply the transform, but instead, "transform" the xslt itself into a t-sql query or something similar.  I guess another alternative is to parse my xslt, which is pretty simple and controlled at this point, and convert it into a t-sql "where" clause.
  
  

• egank

  I don't know if it's within scope of this forum for me to post what I've found, but I'll do so anyways
  
  So I went and read up more about XML and SQL server, and I think I've found a viable solution.  Of course, I'll have to prototype some solutions, but just from reading references and examples, this one seems promising.
  
  So, currently, I have a three table structure to store the server information:
  Table 1: go_servers ("go" stands for Game Oracle) designed to store common server information (ie id, address, port, gametype and ping).
  Table 2: go_bf2servers (this is an example, "bf2" stand for Battlefield 2) designed to store bf2 specific information.
  Table 3: go_bf2players (similar to table 2, but holds player information)
  
  Tables 2 and 3 are quite necessary to normalize the information because game information from one game to another differ.  And so as you can see, in order for me to support multiple games, I would need two table for each one.
  
  Now, because I'll be able to create a table with xml fields, I can "simply" create Table 1 as (id, address, port, gametype, ping, properties(xml), custom(xml), players(xml)).  Which is just awesome...it'll make maintenance so much easier, not to mention making adding more games less of a pain
  
  And the fact that I can query those xml fields using xpath (or is it called xquery ), like so...
  
  select * from go_servers where properties.exist('prop[@key='mapname']/@value = 'somemapname'')
  (ignore the syntax error the single quotes, i did it for readability)
  
  where the properties (xml) field has an untype xml format of:
  
  <prop @key='property key1' @value='property value1'>
  <prop @key='property key2' @value='property value2'>
  <prop @key='property key3' @value='property value3'>
  etc...
  
  
  ...will reduce the parsing i need to do with the xslt, if i even need to do any.  if i do have to parse it, i will only need to rip out one single line from it.
  
  Sound about right, John or close to what you were thinking
  
  

• dnelson MSFT

  definitely learned a couple things from those two blogs (and ill be subscribing to your blog momentarily).  great reads.
  im still a bit ignorant on the typing part, so Ill be reading more reference material on it...however, just from examining the data that ill be storing in my xml fields, im not quite sure if ill need ranged results and consequently a need for typed xml fields.  if i needed servers where there are more than four players, i would have an query/xquery such as:
  
  select * from go_servers where players.exist('count(player) > 4')
  
  since the players field will be in this format:
  <player name="player1" ... />
  <player name="player2" ... />
  etc.
  
  But I'll say this now, regardless if i need it, I can already imagine the other benefits of typing the xml fields, such as strong structures and performance, so I'm sure ill be doing it anyways.

  
  
  

• didierkocurek

  You will probably also want to investigate our FOR XML functionality in order to convert your scalar rowset into XML on the database.
  

• Info_Tech

  Ok, I believe I see what you are trying to do.  The constraints for your game browser (latency, map, # of players, etc...) are expressed within your XSLT, and you want some way of automatically promoting those predicates out so you get some kind of optimized query on the server side.  I don't believe we offer anything which will do this for you out of the box.
  
  It seems to me that your best bet will be to express your constraints as a set of boolean conditions which you can append as part of the "where" clause.  Once you have done this, you can use our server side XML features to shred/transform the data in a way which is fit for your client code.

  
  

• ralfg

  I'm glad to see that you like the new xml datatype features :)
  
  You probably want to change your XQuery statement to be something like:
  '/prop[@key="mapname" and @value="somemapname"]'
  
  For an explanation as to why, you can read this blog entry:
  http://blogs.msdn.com/jgalla/archive/2005/06/13/428544.aspx
  
  Another thing you might want to keep in mind is that if you want to perform range queries (for example, players > 4), then you might want to look into typing your XML columns with a schema and instead of having general properties, using something like "intProp", "stringProp", "dateProp".  By doing this, you will get better performance if you are using indexed XML data.  For some details on why this is, see this blog entry:
  http://blogs.msdn.com/jgalla/archive/2005/09/09/463045.aspx
  
  Thanks for your interest in SQL Server 2005 and our XML features!
  -John
  

• S.Milind

  Jayson,
  You may want to read this blog http://blogs.msdn.com/mrorke/archive/2005/06/28/433471.aspx for some information on how to utilize XSLT in SQL Server 2005.  This will handle the issue of applying the XSLT to your data.
  
  -John Gallardo
  SQL Server Engine
  

• micster

  Thank you...I'll get started on reading that.
  
  
• Use a transform against SQL Server