[xquery-talk] SQL Server 2005

[Frank Cohen]

Hi Peter: Thanks for the reply and the product plug. :-)

See below for some thoughts.

-Frank




On Jan 21, 2006, at 1:34 PM, Peter Coppens wrote:

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

99% of the data is stored in relational databases at the customers I  
work with. I don't see the need to switch off relational to anything  
else. They already did the hard work of normalizing a database and  
writing code to shred incoming data from XML-based Web Services or  
HTML Form submits.

Where I have a problem is with complex XML documents like those  
created using UBL for ebXML solutions. When a service or application  
receives an XML document containing hundreds-to-thousands of  
elements, lots of nesting, and many different schema versions then I  
think its time to look at adding an XML database to the datacenter.

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

I appreciate Michael's comment when advising developers on working  
with XML. Right on.

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

Good product plug. Your SELECT statement looks cleaner than using XML  
FOR.

What test methodology do you use to compare performance and  
scalability of the resulting XQuery against the SQL I would write  
natively in MS SQL Server 2005?

-Frank




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