custom xml data type

When I try to create a custom xml datatype I get the following error:
CREATE TYPE myOwnXMLType FROM xml;
Msg 15226, Level 16, State 1, Line 1
Cannot create user defined types from an XML datatype.
Is there anything I can do ? Other types work fine (int, nvarchar(max)
etc.). I use SQL Server 2005 Standard Edition.
Alex Voit wrote:
> When I try to create a custom xml datatype I get the following error:
> CREATE TYPE myOwnXMLType FROM xml;
> Msg 15226, Level 16, State 1, Line 1
> Cannot create user defined types from an XML datatype.
> Is there anything I can do ? Other types work fine (int, nvarchar(max)
> etc.). I use SQL Server 2005 Standard Edition.
The documentation lists the following allowed base types:
bigint
binary(n)
bit
char(n)
datetime
decimal
float
image
int
money
nchar(n)
ntext
numeric
nvarchar(n | max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)
The xml data type is not listed as an allowed base type for CREATE TYPE
FROM.

Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
|||We did not allow CREATE TYPE on the XML datatype. Please file a request at
http://connect.microsoft.com/sqlserver/feedback if this is a functionality
that you need.
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:euWAnmrpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Alex Voit wrote:
> The documentation lists the following allowed base types:
> bigint
> binary(n)
> bit
> char(n)
> datetime
> decimal
> float
> image
> int
> money
> nchar(n)
> ntext
> numeric
> nvarchar(n | max)
> real
> smalldatetime
> smallint
> smallmoney
> sql_variant
> text
> tinyint
> uniqueidentifier
> varbinary(n | max)
> varchar(n | max)
>
> The xml data type is not listed as an allowed base type for CREATE TYPE
> FROM.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

custom xml data type

When I try to create a custom xml datatype I get the following error:
CREATE TYPE myOwnXMLType FROM xml;
Msg 15226, Level 16, State 1, Line 1
Cannot create user defined types from an XML datatype.
Is there anything I can do ? Other types work fine (int, nvarchar(max)
etc.). I use SQL Server 2005 Standard Edition.Alex Voit wrote:
> When I try to create a custom xml datatype I get the following error:
> CREATE TYPE myOwnXMLType FROM xml;
> Msg 15226, Level 16, State 1, Line 1
> Cannot create user defined types from an XML datatype.
> Is there anything I can do ? Other types work fine (int, nvarchar(max)
> etc.). I use SQL Server 2005 Standard Edition.
The documentation lists the following allowed base types:
bigint
binary(n)
bit
char(n)
datetime
decimal
float
image
int
money
nchar(n)
ntext
numeric
nvarchar(n | max)
real
smalldatetime
smallint
smallmoney
sql_variant
text
tinyint
uniqueidentifier
varbinary(n | max)
varchar(n | max)
The xml data type is not listed as an allowed base type for CREATE TYPE
FROM.
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/|||We did not allow CREATE TYPE on the XML datatype. Please file a request at
http://connect.microsoft.com/sqlserver/feedback if this is a functionality
that you need.
Best regards
Michael
"Martin Honnen" <mahotrash@.yahoo.de> wrote in message
news:euWAnmrpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Alex Voit wrote:
> The documentation lists the following allowed base types:
> bigint
> binary(n)
> bit
> char(n)
> datetime
> decimal
> float
> image
> int
> money
> nchar(n)
> ntext
> numeric
> nvarchar(n | max)
> real
> smalldatetime
> smallint
> smallmoney
> sql_variant
> text
> tinyint
> uniqueidentifier
> varbinary(n | max)
> varchar(n | max)
>
> The xml data type is not listed as an allowed base type for CREATE TYPE
> FROM.
>
> --
> Martin Honnen -- MVP XML
> http://JavaScript.FAQTs.com/

Custom unique ID.

Hello all,
I baddly need to create my own datatype, that will be able to generate
itself as unique id.
1. my user defined data type is based on char(14).
2. structure of this type is
YYYYMMDDSCIDCONT
YYYY = year of record creation
MM = month of record creation
DD = day of record creation
SCID = value of SCID column in the table
CONT = counter (like identity seed) next unique value in the table
I can to do it by my .NET application, or i can make unique keys with all
values in the table, but I would like to create my own SQL type, that will
be able to do what i described. Is it possible?
Thanks.Maybe I should better explain this or ask for some questions.
My task Im going to do:
1) create my own User Defined Data Type based on char(16) named for example
MyIDType
2) create rule that will check if the value is in right format.
3) create function, that will able to generate new id with parameters
datetime, scid. This fuction will parse the datetime, scid and newly created
counter into char(16) and into MyIDType.
Questions:
1) can I create rule that contain more complex check than one rule row ?
2) How to ask for source table in T-SQL function. I mean for the table which
initiated running of my function. Im going to put this function onto
defaultValue of the column that will be type of MyIDType. The function will
suppose, that in this table will exist columns CreationDateTime and SCID.
I know, how to do it in C#. The workflow of this function should be (it is
an example, dont check any syntax):
FUNCTION GetNewMyID (@.scid char(4))
DECLARE @.datetimenow char(8)
DECLARE @.lastid char(4)
DECLARE @.partOfMyID char(12)
@.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
for(int i = 1; i < 10000; i++)
{
@.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
if(there is no row)
return @.partOfMyID;
}
I hope, the good programer will understand this weird construction. :)))
Thanks.
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I baddly need to create my own datatype, that will be able to generate
> itself as unique id.
> 1. my user defined data type is based on char(16).
> 2. structure of this type is
> YYYYMMDDSCIDCONT
> YYYY = year of record creation
> MM = month of record creation
> DD = day of record creation
> SCID = value of SCID column in the table
> CONT = counter (like identity seed) next unique value in the table
> I can to do it by my .NET application, or i can make unique keys with all
> values in the table, but I would like to create my own SQL type, that will
> be able to do what i described. Is it possible?
> Thanks.
>|||hi Mirek,
Sure.
I understand that function but what does 'scid' mean? Let me know.
Current location: Alicante (ES)
"Mirek Endys" wrote:

> Maybe I should better explain this or ask for some questions.
> My task Im going to do:
> 1) create my own User Defined Data Type based on char(16) named for exampl
e
> MyIDType
> 2) create rule that will check if the value is in right format.
> 3) create function, that will able to generate new id with parameters
> datetime, scid. This fuction will parse the datetime, scid and newly creat
ed
> counter into char(16) and into MyIDType.
> Questions:
> 1) can I create rule that contain more complex check than one rule row ?
> 2) How to ask for source table in T-SQL function. I mean for the table whi
ch
> initiated running of my function. Im going to put this function onto
> defaultValue of the column that will be type of MyIDType. The function wil
l
> suppose, that in this table will exist columns CreationDateTime and SCID.
> I know, how to do it in C#. The workflow of this function should be (it is
> an example, dont check any syntax):
> FUNCTION GetNewMyID (@.scid char(4))
> DECLARE @.datetimenow char(8)
> DECLARE @.lastid char(4)
> DECLARE @.partOfMyID char(12)
> @.datetimenow = FORMAT( GETDATE()) // in format YYYYMMDD
>
> for(int i = 1; i < 10000; i++)
> {
> @.partOfMyID = @.datetimenow + @.scid + i.ToString("0000")
> SELECT MyID FROM [executingTable?] WHERE MyID LIKE @.partOfMyID
> if(there is no row)
> return @.partOfMyID;
> }
> I hope, the good programer will understand this weird construction. :)))
> Thanks.
>
> "Mirek Endys" <MirekE@.community.nospam> wrote in message
> news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Enric
the SCID is variable - value that is contained in inserted record... or
passed by function parameter.
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:6DE5E6D0-9B7A-421C-A6BF-14E33D967B06@.microsoft.com...
> hi Mirek,
> Sure.
> I understand that function but what does 'scid' mean? Let me know.
> Current location: Alicante (ES)
>
> "Mirek Endys" wrote:
>|||Hi Mirek,
As for the SQL 2005 clr UDT, it is still quiet limited, e.g, if the data
type used in the UDT is not in the following basic types:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double,
SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble,
SqlMoney, SqlBoolean
we have to implenent our userdefined serialization. For type validation, we
can define the validation rules in a certain method and mark it with the
"ValidationMethodName" attribute.
And for the constructing a new UDT instance based on some parameters or
other info in the current database, this is hard to be done through the UDT
itself since UDT just provide a "parse" function which help use create UDT
instance from a string representation(also a "ToString" method help return
string representation from a UDT instance). For your scenario, maybe we
have to define an additional user defined funciton(CLR based) to the work.
#User-Defined Type Requirements
http://msdn2.microsoft.com/en-us/library/f33b06h1.aspx
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||You can create static method(s) in your UDT to create. Kinda like a static
constructor. Something like:
public static MyID Create(SqlDateTime dt, SqlInt id, SqlInt counter)
{
// ...
return new MyID(dt, id, counter);
}
Use the sql types that make sense for your needs.
Then you can create an instance something like:
declare @.id MyID
set @.id = MyID.Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:%23$vH%23RZTGHA.196@.TK2MSFTNGP10.phx.gbl...
| Hello all,
|
| I baddly need to create my own datatype, that will be able to generate
| itself as unique id.
|
| 1. my user defined data type is based on char(14).
| 2. structure of this type is
| YYYYMMDDSCIDCONT
|
| YYYY = year of record creation
| MM = month of record creation
| DD = day of record creation
| SCID = value of SCID column in the table
| CONT = counter (like identity seed) next unique value in the table
|
| I can to do it by my .NET application, or i can make unique keys with all
| values in the table, but I would like to create my own SQL type, that will
| be able to do what i described. Is it possible?
|
| Thanks.
|
||||Thank for Williams inputs.
This is a good suggestion that directly use a static method on the type.
Regards,
Steven Cheng
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may
learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks guys,
i thought so, that this will be solution.
"Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
> Thank for Williams inputs.
> This is a good suggestion that directly use a static method on the type.
> Regards,
> Steven Cheng
> Microsoft Online Community Support
>
> ========================================
==========
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may
> learn and benefit from your issue.
> ========================================
==========
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Just a note after looking at it again. You need the double "::" to refer to
the static method on a type from tsql. Such as:
set @.id = MyID::Create(getdate(), 1, 1)
William Stacey [MVP]
"Mirek Endys" <MirekE@.community.nospam> wrote in message
news:uYBv3FmTGHA.4792@.TK2MSFTNGP14.phx.gbl...
| Thanks guys,
|
| i thought so, that this will be solution.
|
|
| "Steven Cheng[MSFT]" <stcheng@.online.microsoft.com> wrote in message
| news:wQfSEekTGHA.5536@.TK2MSFTNGXA03.phx.gbl...
| > Thank for Williams inputs.
| >
| > This is a good suggestion that directly use a static method on the type.
| >
| > Regards,
| >
| > Steven Cheng
| > Microsoft Online Community Support
| >
| >
| > ========================================
==========
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may
| >
| > learn and benefit from your issue.
| >
| > ========================================
==========
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
|
|

custom datatype SQL server 2000

Hi All
In our DB we have info in a format of "150:1" resp "nnn:n". Its information
about Contrast Ratio.
This is of course typed as varchar. That prevents us from sorting the fields
the way we need. we are also
unable to do any kind of 'simple' compare < >
I guess in 2005 we could use custom Data Type feature. Is there any
workaround in MSSQL 2000
Thanx for any hints...
PetttA ratio (rational number) can be expressed as a decimal number. You could
add a computed column to your tabe that represents the contrast ratio as a
decimal
ALTER TABLE [YourTable]
ADD [DecimalRatio]
AS CAST(LEFT([ContrastRatio], CHARINDEX(':',[ContrastRatio]) - 1) AS numeric)
/
CAST(RIGHT([ContrastRatio], LEN([ContrastRatio]) -
CHARINDEX(':',[ContrastRatio])) AS numeric)
"Petr SIMUNEK" wrote:

> Hi All
> In our DB we have info in a format of "150:1" resp "nnn:n". Its informatio
n
> about Contrast Ratio.
> This is of course typed as varchar. That prevents us from sorting the fiel
ds
> the way we need. we are also
> unable to do any kind of 'simple' compare < >
> I guess in 2005 we could use custom Data Type feature. Is there any
> workaround in MSSQL 2000
> Thanx for any hints...
> Pettt
>
>|||Petr
Why not split the values into 2 columns? You could then build a sequence or
do a comparison using a quotient of the 2 values, right? You can always
combine them back to ratio notation for display purposes.
CREATE TABLE Foobar (
key_col VARCHAR(3) NOT NULL PRIMARY KEY
,contrast_num INT NOT NULL
,contrast_den INT NOT NULL
);
INSERT INTO Foobar VALUES ('AAA', 150, 1);
INSERT INTO Foobar VALUES ('BBB', 20, 1);
INSERT INTO Foobar VALUES ('CCC', 1, 1);
INSERT INTO Foobar VALUES ('DDD', 43, 2);
INSERT INTO Foobar VALUES ('EEE', 25, 6);
SELECT
key_col
,CAST(contrast_num AS VARCHAR(3)) + ':' + CAST(contrast_den AS CHAR(1))
AS contrast_ratio
FROM Foobar
ORDER BY contrast_num / contrast_den
;
Comparisons could work the same way.
Joe