MSDN WebCast Demo: Casting between CLR UDT and XML
/*
Creates user-defined CLR type and casts between it and XML
Steps to generate the DLL:
1. Write your UDT ComplexNumber.cs.
2. Build your UDT into ComplexNumber.dll
3. call sgen /nologo /force ComplexNumber.dll.
This generates ComplexNumber.XmlSerializers.dll
4. Run this script
(c) 2004, 2005 Microsoft Corp.
*/
use
master
use msdn2005
go
drop
table Math;
DROP TYPE ComplexNumber;
DROP Assembly ComplexNumberXML;
DROP ASSEMBLY ComplexNumber;
Go
-- Create assemblies: One for the type and one for the serializer
create assembly ComplexNumber
from 'Insert your path here\ComplexNumber.dll';
go
create
assembly ComplexNumberXML
from 'Insert your path here\ComplexNumber.XmlSerializers.dll';
go
-- Create CLR UDT
CREATE TYPE ComplexNumber
EXTERNAL NAME [ComplexNumber].[Microsoft.Samples.SqlServer.ComplexNumber];
go
-- Create table with CLR UDT and add values
CREATE TABLE Math (c ComplexNumber);
go
Insert
into Math Values ('(1, 2i)');
Insert into Math Values ('(2, 3i)');
go
-- You potentially need to enable clr before insertion works:
exec sp_configure 'clr enabled', 1
go
RECONFIGURE
go
-- Try inserting again if it failed the first time
Insert into Math Values ('(1, 2i)');
Insert into Math Values ('(2, 3i)');
go
select
c.ToString() from Math;
go
-- Casting the UDTs to XML
select CAST(c as XML) from Math;
go
-- Casting from XML to UDT
declare @x xml
set @x = N'<ComplexNumber >
<Real>42</Real>
<Imaginary>6</Imaginary>
</ComplexNumber>'
select
CAST(@x as ComplexNumber).ToString()
go