[xquery-talk] SQL Server 2005

[Peter Coppens]

Frank,

Thanks for the interesting pointers....

Concerning

"After the most basic of examples this begins to look to me like a bunch
of non-standard messy commands to work with XML data. This didn't do
much to move me from an opinion that XQuery and native XML databases are
best for working with XML. Leave the relational stuff for everything
that is not XML."
(http://www.xquerynow.com/cohensxblog/sql2005.html)

Reality is that there are lots of relational databases containing lots
of critical business information. Sooner or later the need for some type
of integration with XML data might pop up. Should that be the trigger to
throw away the relational database and a substantial part of the
application code and start from scratch, or start deploying a second
database technology next to the well known RDBMS? I am sure you will
agree that these are not realistic scenarios. 

The for xml (explicit) SQL extension introduced in SQLServer 2000 is
certainly not the most user friendly way to deal with the problem, but
it is a pragmatic one, and it worked...although it is pretty expensive
in terms of development time and time to cope with changes. Michael Rys,
Microsoft's programmer manager for this type of technology refers to it
as the query from hell
(http://sqljunkies.com/weblog/mrys/archive/2004/01/27/869.aspx).

The SQLServer 2005 extensions make developing these types of
applications a lot simpler, and although and can complain MS continues
to go the proprietary way, I am sure a lot of MS customers appreciate
this a lot.

Warning - the product plug part.....

There are standards based solutions available to solve this. See e.g.
http://www.datadirect.com/products/xquery/index.ssp

E.g. the example query provided in the article you refer to

SELECT c.Name AS '@Name',
  (SELECT sc.Name AS '@Name'
  FROM Production.ProductSubCategory sc
  WHERE sc.ProductCategoryID = 
        c.ProductCategoryID
  ORDER BY sc.Name
  FOR XML PATH('SubCategory'), TYPE)
FROM Production.ProductCategory c
ORDER BY c.Name
FOR XML PATH('Category'), ROOT('XML')

Becomes


<XML>{
  for $cat in collection("ProductCategory")/ProductCategory
  order by $cat/Name
  return 
    <Category Name='{$cat/Name}'>{
      for $subcat in collection("ProductSubcategory")/ProductSubcategory
      where $cat/ProductCategoryID = $subcat/ProductCategoryID
      order by $subcat/Name
      return
        <SubCategory Name='{$subcat/Name}' />
    }</Category>
}</XML>


SQL2003 also introduced a set of extensions (SQL/XML) to deal with this
(see e.g. http://www.datadirect.com/products/sql_xml/index.ssp)

Cheers,

Peter

> -----Original Message-----
> From: talk-bounces at xquery.com [mailto:talk-bounces at xquery.com] On
Behalf
> Of Frank Cohen
> Sent: Saturday, January 21, 2006 6:58 PM
> To: talk at xquery.com
> Subject: [xquery-talk] SQL Server 2005
> 
> Maybe SQL Server 2005 has some new secret sauce for handling XML
> data. I ran across Jerry Dixon's article in which he talks about the
> new XML features. Jerry writes from a software developer perspective
> that seems true: He likes the new XML features but uses them to
> create XML, not to store XML.
> 
> I blog about this at:
> 
> http://www.xquerynow.com/cohensxblog/sql2005.html
> 
> -Frank
> 
> ---
> Frank Cohen, Raining Data, http://www.RainingData.com, phone: 408 236
> 7604
> http://www.xquerynow.com for free XML, XQuery and native XML database
> tips,
> techniques and solutions.
> 
> 
> 
> 
> 
> 
> 
> _______________________________________________
> talk at xquery.com
> http://xquery.com/mailman/listinfo/talk