SQL Server - XML
It’s a first class data type, you know.
A commonly asked question is "how can I load an existing XSD file into an XML Schema Collection?" It's pretty easy:
declare @don xmlselect @don=bulkcolumn from openrowset(bulk 'c:\users\ktegels\some.xsd',single_blob) as screate xml schema collection dbo.collectionName as @dongoSubsituting the location for your file following the BULK provider in the OpenRowSet source.
 |
Congrats, Michael!
So if you've been wondering why I never wrote a book about SQL Server 2005 or XML, here's why -- there's one straight from the source.
For those of you with a passion of XML and SQL Server 2005, this is the book for you. My copy has shipped, when are you going to get yours? |
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))
I'll be presenting to the New Jersey SQL Server user's group tommorow (http://njsql.org/blogs/user_group_news/archive/2006/05/15/30.aspx) night. Details here.
Hope to see you there!
"order has shipped."
My copy of Bob and Dan's "A Developer's Guide to SQL Server 2005 has finally departed from whatever cave Amazon loaded them into. Can't wait to see how different it is. Its going to be a lot of reading too, its accompanied by Johnson's ADO.NET Advanced Topics book that I'm also looking forward too.
Is it time for me to take up the writing effort again? Maybe. I've got ideas, but what would you be interested in?
Doh! Sorry to be late getting this on the wire but the Heartland Developers' Conference for 2006 is has been announced. This year its in Omaha and runs from 26 October and 27 October. Hopefully I won't find myself in China again...
In its 3rd year, HDC06 is the largest independent annual professional Microsoft development conference in the nation, organized by user groups, and focused on .NET development and other emerging Microsoft technologies. HDC provides regional developers the opportunity to experience sessions usually reserved for Tech-Ed and PDC at a much lower cost while retaining the same nationally known presenters. Over 2 days, 23 sessions, 2 tracks, and several networking opportunities, HDC06 is where you’re find top knowledge experts that can prepare you for next level development.
For only $125 Early Bird/$175 standard, each Attendee Pass includes:
1 Conference pass to 2 keynotes and all 23 ninety minute sessions
- Meals including breakfast, lunch, snacks, and coffee breaks
- Access to the “Developer Lounge” expo and activity center
- Wireless internet access throughout conference area
- Pre conference networking party including drinks and food
- Thursday night “Developer Jam” including food, drink, and a casino for prizes!
- Attendee Kit and a chance to win a monster 64 Bit Alienware computer
The speaker list is looking awesome too: Microsoft's own Jeff Brand, Don Bryner and Jacob Cynamon. MVPs Dave Donaldson, Robert Hurlbut, Rocky Lhotka, Javier Lozano and Andrew Troelsen and community rockstars Craig Utley, Phil Wolfe, Robert Boedigheimer, Tim Gifford, Matt Milner and Nick Parker. I'm planning of giving a couple of talks on SQL Server 2005 for Developers.
Register here. And remember, we're talking a mere $125 one of the best Microsoft-focused technical conferences in the Midwest.
File this under this how to know what you have fix before you fix an XML Schema Collection
When I’m introducing folks to XML Schema Collections in SQL Server 2005, conversation usually goes something like this:
Blah blah blah blah use to make sure that XML instances are valid according to an XML Schema blah blah blah and you create them using CREATE XML SCHEMA COLLECTION name AS blah blah blah. Naturally, you can delete such a schema collection using DROP XML SCHEMA COLLECTION but you have to make sure there are no dependent objects on that before doing so. Some more talk and the then I have to it. The part of the talk I like the second least.
“So there is an alter for XML schema collections and to nobody’s surprise that starts with ALTER XML SCHEMA COLLECTION followed by the qualified name of the schema collection and a verb. In SQL Server 2005 there is one supported verb: ADD.”
That means that the alter for an XML schema collection is really only good for doing either of things: adding additional elements to default namespace, or adding a schema for a new namespace. And that’s basically it. There’s really not a good way to fix a broken or out of date schema for an existing namespace. And that means – oh joy – that you need to alter all of the objects that use the schema to not do so before you go about fixing the schema. And the first step in that is, of course, getting an inventory of the objects you’ll need to touch.
Continued here, due to length.
As Greg and Wally have reported, I've been thinking about bringing back Take Outs as a PodCast. I'm pleased to announce with the the hosting support offered by Steve Wynkoop of SSWUG, its now a reality. The need-to-knows:
The RSS feed: http://feeds.feedburner.com/sswugorgradio
iTunes friendly feed: http://feeds.feedburner.com/SswugorgRadio?format=pcast
Episode one show notes and download file.
Let's roll!
A big thank you to the Kansas City SQL Server Users Group for giving me their time and attention last night. Despite getting confused as to where the meeting was being held and thus being late (DOH!), I had a great time talk about the XML features in SQL Server 2005. The presentation download is here. The extra bit of code for loading a file directly into an XML column of table follows:
create table dbo.xmlDocs(pkid smallint identity(-32767,1) primary key,doc xml)
go
insert into dbo.xmlDocs
select * from openrowset(bulk 'c:\tmktx\Great BBQ joints in Kansas City.xml',single_blob) p
go
I'd especially like to thank Bill Graziano and Troy Schuh for making the arrangement and the nice gift. While we do this kind of stuff for the community, its really nice when a group goes out of their way to say thanks like these guys did. Also thanks to the nice folks at Idera for giving that group a license and 1yr of upkeep on their SQLSafe product and providing the pizza. I can't believe I missed a chance to snarf down pizza... next time, I'll double check the address.
Over on the Microsoft.Public.SqlServer.XML newsgroup, one Chris Kilmer asked a good question about how to shred a single XML document into multiple tables using multiple stored procedures. Chris's goal with this was to have each stored procedure update one table and pass the XML remaining nodes of the first document off to the next stored procedure. This is actually fairly easy except for one thing: maintaining referential activity between inserted elements.
Continues at: http://geekswithblogs.net/ktegels/archive/2006/01/20/ShredWithXQuery01.aspx
Sorry for the continues. If you'd rather not chance links, please subscribe to my new feed at: http://geekswithblogs.net/ktegels/Rss.aspx
The start of the new year always makes me think I want to do new things. This year I'm trying to eat more healthily, drink less and blog more. While you probably don't care so much about the first two of those, you're probably interested in the third. Afterall, consider what your doing right now...
Effective today, I'm shifting my blog focus from SQLJunkies (what you're reading right now) over to GeeksWithBlogs. Here's the new feeds and their RSS URLs.
I'll keep posting references to stuff about .NET and SQL Server on this site for some time to come, of course.
I've been spending a fair amount of time recently working on Reporting Services stuff. Last year, Teo was kind enough to have Manning Press send me a copy of his Reporting Services in Action book. Quite honestly, this is one of the best books on any technology I've ever read. But not merely because it's technically solid, but because its completely readable and to the point. I find myself diving into it regularly, as I did yesterday morning. About two hours later, I'm having lunch with our local SQL Server Specialist -- Don Bryner -- from Microsoft and we're talking about books for SQL Server 2005. Teo's name comes up. It was amazing here somebody else say exactly the same thing I've been thinking about Teo. But Don was talking about Teo's new book on Analysis Services. I had to laugh. My darling Janell was very good to me for Christmas with a Border's gift card and I redeemed for that book. It arrived Monday and I'd already consumed the first chapter of it.
But it doesn't end there: yesterday I put a bit of code up talking about how to do simple object serialization and then today Teo posts about Object Serialization in SQLCLR. While the post stands on its own technical merit, its yet another example of why I dig Teo's writing style: What it is; Why it matters; Why it does that; What to do next and how and finally, a good conclusion.
If you're not subscribed to Teo's Blog, you're missing out. If you need really good books that will help you wrap your head around Reporting Services or Analysis Services, you won't go wrong with his books.
The other day there was a posting in the SqlServer.XML newsgroup asking about Serialization. While the topic can get hairy quickly, there's a fairly simple solution if you're willing to live with a few... uh... interesting side affects. There's a fair amount of code in the example, so I've posted into an article over here.
Just in case you live under a rock (like I seem to have been doing recently), my DevelopMentor cohort Niels Berglund has shipped updated versions of his custom SqlClr Project and his GUI for Service Broker. Go check 'em out.
As if this wasn't a bad enough idea the first time around... anyway, those of you with an XQuery phobia might say, "Kent, your last post, it scared me. Can you show me how do this with SQLCLR instead?" Sure!
Our database is very much designed as it was before, but I've added a couple of additional stored procedures (in bold):
use master
go
drop database procexample
go
create database procexample
go
use procexample
go
create table dbo.tableA
(pkid tinyint identity(1,1) primary key
,descr varchar(50) not null)
go
create procedure dbo.tableA_Insert
(@descr varchar(50))
as
begin
set nocount on
declare @i table (pkid tinyint,descr varchar(50))
insert into dbo.tableA(descr)
output inserted.* into @i
values (@descr)
select top 1 pkid from @i
end
go
create table dbo.tableB
(
pkid tinyint identity(1,1) primary key
,pkid_tableA tinyint not null
constraint fk_tableB_tableA_PKID
foreign key references dbo.tableA(pkid)
,descr varchar(50) not null
)
go
create procedure dbo.tableB_Insert
(@pkid_tableA tinyint,@descr varchar(50))
as
begin
set nocount on
insert into dbo.tableB(pkid_tableA,descr)
values (@pkid_tableA,@descr)
end
go
The SQLCLR bits look like this:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Transactions;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static SqlInt32 XmlLoader(SqlXml doc)
{
SqlConnection conn = new SqlConnection("context connection=true;");
SqlCommand cmdA = new SqlCommand("dbo.tableA_Insert",conn);
cmdA.Parameters.AddWithValue("@descr", null);
cmdA.CommandType = CommandType.StoredProcedure;
SqlCommand cmdB = new SqlCommand("dbo.tableB_Insert", conn);
cmdB.Parameters.AddWithValue("@descr", null);
cmdB.Parameters.AddWithValue("@pkid_tableA", null);
cmdB.CommandType = CommandType.StoredProcedure;
byte lastTableAID = 0;
int rc = -1;
XmlReader xr = doc.CreateReader();
using (TransactionScope ts = new TransactionScope())
{
try
{
conn.Open();
while (xr.Read())
{
switch (xr.Name)
{
case "tableA":
cmdA.Parameters[0].Value = xr.ReadString();
lastTableAID = (byte)(cmdA.ExecuteScalar());
while (xr.Name == "tableB")
{
cmdB.Parameters[0].Value = xr.ReadString();
cmdB.Parameters[1].Value = lastTableAID;
cmdB.ExecuteNonQuery();
xr.Read();
}
break;
default:
throw new ArgumentException();
}
}
ts.Complete();
rc = 0;
}
catch (Exception ex)
{
rc = -1;
throw ex;
}
}
return (rc);
}
};
One can test it thusly:
declare @x xml
set @x = 'BeerWaterMaltHopsYeastWineWaterGrapesYeast'
exec dbo.XmlLoader @x
This variant has one advantage over the XQuery-based example in that it can accept multiple object graphs in SqlXml document.
Twice today, I've had basically the same question in the newsgroups. It boils down to this: the poster wants to send an array of objects (an object graph) to a stored procedure and have that procedure inserts into n-many tables. In other words, they don't want to call n-many procs from the client side. One send, one result.
However, the problem is that T-SQL doesn't have the idea of an array, so you can't pass an array of objects into a stored procedure. And, no, it does