TechEd 2006
So it only makes sense that if you have XML in the database that evetually you're going to want to do something with it. The combination of XML Indexing and XQuery give us efficient albeit limited transformational functionality in such cases. But what would you do if you wanted to use XSL/T stylesheet instead. SQL Server 2005 doesn't offer an out of the way to do that with T-SQL, but as I say in my talks about SQLCLR, its a reasonable way to get the job done. The first thing you'd do is cruft up a public static method around System.Xml.Xsl.CompiledTransform like this:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Xml.Xsl;
using System.IO;
namespace DM.Examples
{
public partial class XmlLibrary
{
[SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = false, IsPrecise = false, SystemDataAccess = SystemDataAccessKind.None)]
[return: SqlFacet(IsFixedLength = false, IsNullable = true, MaxSize = -1)]
public static SqlXml ApplyTransform(SqlXml Data, SqlXml StyleSheet)
{
// on null return null, just in case.
if (Data.IsNull || StyleSheet.IsNull)
return SqlXml.Null;
// Buffer the transformed xml
MemoryStream ms = new MemoryStream();
XmlWriter xw = XmlWriter.Create(ms);
// Load and transform
XslCompiledTransform ctx = new XslCompiledTransform(false);
ctx.Load(StyleSheet.CreateReader());
ctx.Transform(Data.CreateReader(), xw);
// return the result, assuming XML compliant output
return new SqlXml(ms);
}
}
}
Not really much exiciting there other than the use of the SqlXml type's abilty to create an XmlReader. Once you've deployed that using Visual Studio (or using good old CREATE ASSEMBLY and CREATE FUNCTION), its not very hard to use in a Query:
-- here we load the XSLT into a table to make easier to reuse in future queries. Do this once.
-- storing as BLOB as its just a stylesheet. No need to query, so no need for XML type here.
if (select count(*) from sys.tables where name = 'xslTransforms') = 0
create table dbo.xslTransforms (transformID tinyint identity(1,1) primary key,descr nvarchar(255),stylesheet varbinary(max))
go
truncate table dbo.xslTransforms
insert into dbo.xslTransforms
select 'AdventureWorks Product Table',bulkcolumn from openrowset(bulk 'c:\simple.xslt',single_blob) as p
go
-- Load the style sheet and feed it some data.
declare @x xml
select @x = (select productID as '@dbid',ProductNumber as '@productID',Name as 'name',Color as 'color',ListPrice as 'listPrice',Size as 'Size',SizeUnitMeasureCode as 'sizeCode',style as 'style' from adventureworks.production.product where not(coalesce(discontinuedDate,'2999-12-31') = 1) and FinishedGoodsFlag = 1 for xml path('product'),root('products'),elements xsinil,type)
select dbo.ApplyTransform(@x,(select cast(stylesheet as xml) from dbo.xslTransforms where transformID=1))
Pablo is giving us a talk about what the next changes and solutions in ADO.NET 3.x.
· Key drivers for them are providing an ORM tool without tossing out ADO.NET fundamentals.
· Most applications are data centric apps, Microsoft places high priority in preserving investments
· Two-fold strategy: Widen the stack with the conceptual model and adding an object layer on top of the ADO.NET primatitives
· Key themes for ADO.NET 3.x: conceptual data models; better integration with .NET and better integration with languages via LINQ.
· Showed a demo of DataDude and LINQ, Started with and Entity Diagram, a design surface in Studio 2005. Saving the ED generates a mapping layer via CLR classes. Can now use that an object binding. Showed a LINQ over DLINQ to do a filtering query. Showed one-line data persistence.
· Went into a discussion of Schemas (database, not XSD). Schemas can be less than ideal for an application and client views a logical separation level. Client-side instance that become updatable and statically verifiable.
· Views are surfaced as an ADO.NET provider and how to worked with Mapped View using Map* as we would Sql* or OleDb* data access classes. Security isn’t done at the mapped view level, but instead at the base table level. Baby steps, I guess.
· Talked about the Entity Data Model as structured records with keys and structural inheritance and it becomes and executable. Entities look to become the primary mediation object.
· Introduced that there would be Entity SQL for query for entity data and entity meta data. Stressed that this gives us a very high degree of database independence. Join syntax will be done with a WHER NAVIGATE function in the moment. Bit look a bit primitive yet, but should be very powerful.
· Talked about Object Services using ObjectContext vs. a provider and a Query. This will use either Entity SQL or LINQ. This looks pretty cool and like it should generate less net code.
· ObjectContext has a generic persistence method that does optimistic concurrency within a TranscationScope.
· Pablo then dug into LINQ. At this point I decided to exit.
It is my first day of TechEd 2006, and I’m starting with a presentation by the data team. The deck is entitled “Microsoft’s Data Platform Vision” and the initial speaker is Dave Campbell. I see Gert Drapers, Alex Payne and Pablo Castro in the room too. Sharon Dooley is here too. Dave framed this as a “where we are going talk.”
· Standard Kilo to Mega, Mega to Terabyte introduction
· Amount of data being “born digital” continues to grow
· The concept of permanently persistent data for prediction in sales and marketing becoming strategic assets
· Concept of pre-transactional data (record of intent) with less active management
· Made an interesting comment about spatial data and this becoming more important
· No longer about the database, it is about the higher level services.
· Latency and human costs are the drivers now
· Time to solution is very important
· Next frontier is automation of process interfacing with people
· Talking about bleeding device interfaces and dynamic applications
· Katmai goal is scale out and always on-line with better health monitoring – “Grid for real.” Policies for operations, policy for data. Semantic data models controlling aspects like security of data.
· Announced Always-On program. Again.
· Image acquisition and structured information with workflows and data mining
· Clearly giving the sense that spatial enablement is critical
· Greater emphasis on conceptual data modeling, target for entity framework. Unification of data models for use and programming. Clearly schema becomes very important
· Talking the insanity of dynamic SQL from the side. The idea is to have LINQ enabled data sources instead. Don’t program against the API, solve the business problem. LINQ over dataset (already announced) and DLINQ for SQL (already announced) and LINQ over entities. Also talking about a new XML data storage format for VS.NET
· Dave started picking playfully on Pablo for his typing skills
· Pablo gave a pretty good demonstration of the basics of DLINQ.
· Alex Payne gave an interesting demo of Excel consuming Analysis Services cube and consuming data by schema. Drill-through mostly using the new pivot table stuff.
· Talked about Federations of data sources and disconnected data sources and data being model driven and SOA.
· Talked about SQL Mobile as the foundation of SQL Server Everywhere Edition and the CTP is available today. Session DEV343 for drill-down on that.
· Cameron talked about VSTE for Database Professional (aka Data Dude)
o Schema management toolset
o Allows to delta and test in offline mode
o Showed the dependency walker for meta-data, very cool
o Showed the schema differencing tool. Very nice.