Having problems with Typed Xml

Here is my context ( SQL server version : 9.00.1116.00 ) ( CPT April )
1- I've created a "Business" Database
USE [Business]
GO
2- I've defined an Xml Schema Collection
create xml schema collection dbo.CustomerFile as
'<xs:schema xmlns="http://XmlSpaces.Tests"
targetNamespace="http://XmlSpaces.Tests"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:complexType name="EntityType">
<xs:sequence />
<xs:attribute name="key" type="xs:string" />
<xs:attribute name="reference" type="xs:string" />
<xs:attribute name="creationDate" type="xs:dateTime" />
</xs:complexType>
<xs:simpleType name="CivilityCodification">
<xs:restriction base="xs:string">
<xs:enumeration value="Mr" />
<xs:enumeration value="Mm" />
<xs:enumeration value="Ml" />
</xs:restriction>
</xs:simpleType>
<xs:complexType name="CivilityType">
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="code" type="CivilityCodification" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
<xs:complexType name="NameType">
<xs:sequence>
<xs:element name="Civility" type="CivilityType" />
<xs:element name="First" type="xs:string" />
<xs:element name="Last" type="xs:string" />
</xs:sequence>
</xs:complexType>
<xs:complexType name="AddressType">
<xs:sequence>
<xs:element name="Street" type="xs:string" />
<xs:element name="Zip" type="xs:string" />
<xs:element name="Town" type="xs:string" />
</xs:sequence>
<xs:attribute name="code" type="AddressCodification" />
</xs:complexType>
<xs:simpleType name="AddressCodification">
<xs:restriction base="xs:string">
<xs:enumeration value="Main" />
<xs:enumeration value="Office" />
<xs:enumeration value="Home" />
</xs:restriction>
</xs:simpleType>
<xs:element name="Address" type="AddressType" />
<xs:complexType name="CustomerType">
<xs:complexContent mixed="false">
<xs:extension base="EntityType">
<xs:sequence>
<xs:element minOccurs="0" maxOccurs="1" name="Name"
type="NameType" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="Address" />
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
<xs:element name="Customer" type="CustomerType" />
</xs:schema>'
3- I've build a table that use this Schema definition
CREATE TABLE [dbo].[Customers]
(
[OID] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
[Reference] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
[XmlValue] [xml](DOCUMENT [dbo].[CustomerFile]) NOT NULL,
CONSTRAINT [Unique_CustomerByOID] PRIMARY KEY CLUSTERED
(
[OID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
4- From what i understood the following statement should work but it doensn't
INSERT Customers ( Reference, XmlValue ) VALUES( 'CUSS-007',
'<Customer reference="CUSS-007" creationDate="2005-06-28T13:59:49.1548464"
xmlns="http://XmlSpaces.Tests">
<Name>
<Civility code="Mr">Mister</Civility>
<First>Brice</First>
<Last>Prunier</Last>
</Name>
<Address code="Main">
<Street>66 street A</Street>
<Zip>11111</Zip>
<Town>Seattle</Town>
</Address>
<Address code="Office">
<Street>33 Avenue B</Street>
<Zip>22222</Zip>
<Town>Los Angeles</Town>
</Address>
</Customer>' )
Trying the here above context raised the followings error
1- invalid format for Customer/@.creationDate
After switching xs:dateTime to xs:string i get :
Expected element : Name, http://XmlSpaces.Tests:Address where element
http://XmlSpaces.Tests:Name was specified
thanks for help
First issue:
In SQL Server 2005, we require a timezone on the value. So you either need
to change the type in the schema to xs:string (as you did) or add a timezone
to the value (e.g., append a Z). Also, in the April CTP (and June CTP) we do
not yet round to the millisecond precision but raise an error (we are
looking into changing that, although at this stage any functional change
needs lots of justification). So you should reduce the microseconds to
milliseconds.
Second issue:
You need to add
elementFormDefault="qualified"
to the xs:schema element.
Best regards
Michael
"Brice Prunier" <Brice Prunier@.discussions.microsoft.com> wrote in message
news:E28FD8F9-50CD-446D-8A0A-BB2775114B64@.microsoft.com...
> Here is my context ( SQL server version : 9.00.1116.00 ) ( CPT April )
> 1- I've created a "Business" Database
> USE [Business]
> GO
> 2- I've defined an Xml Schema Collection
> create xml schema collection dbo.CustomerFile as
> '<xs:schema xmlns="http://XmlSpaces.Tests"
> targetNamespace="http://XmlSpaces.Tests"
> xmlns:xs="http://www.w3.org/2001/XMLSchema">
> <xs:complexType name="EntityType">
> <xs:sequence />
> <xs:attribute name="key" type="xs:string" />
> <xs:attribute name="reference" type="xs:string" />
> <xs:attribute name="creationDate" type="xs:dateTime" />
> </xs:complexType>
> <xs:simpleType name="CivilityCodification">
> <xs:restriction base="xs:string">
> <xs:enumeration value="Mr" />
> <xs:enumeration value="Mm" />
> <xs:enumeration value="Ml" />
> </xs:restriction>
> </xs:simpleType>
> <xs:complexType name="CivilityType">
> <xs:simpleContent>
> <xs:extension base="xs:string">
> <xs:attribute name="code" type="CivilityCodification" />
> </xs:extension>
> </xs:simpleContent>
> </xs:complexType>
> <xs:complexType name="NameType">
> <xs:sequence>
> <xs:element name="Civility" type="CivilityType" />
> <xs:element name="First" type="xs:string" />
> <xs:element name="Last" type="xs:string" />
> </xs:sequence>
> </xs:complexType>
> <xs:complexType name="AddressType">
> <xs:sequence>
> <xs:element name="Street" type="xs:string" />
> <xs:element name="Zip" type="xs:string" />
> <xs:element name="Town" type="xs:string" />
> </xs:sequence>
> <xs:attribute name="code" type="AddressCodification" />
> </xs:complexType>
> <xs:simpleType name="AddressCodification">
> <xs:restriction base="xs:string">
> <xs:enumeration value="Main" />
> <xs:enumeration value="Office" />
> <xs:enumeration value="Home" />
> </xs:restriction>
> </xs:simpleType>
> <xs:element name="Address" type="AddressType" />
> <xs:complexType name="CustomerType">
> <xs:complexContent mixed="false">
> <xs:extension base="EntityType">
> <xs:sequence>
> <xs:element minOccurs="0" maxOccurs="1" name="Name"
> type="NameType" />
> <xs:element minOccurs="0" maxOccurs="unbounded" ref="Address" />
> </xs:sequence>
> </xs:extension>
> </xs:complexContent>
> </xs:complexType>
> <xs:element name="Customer" type="CustomerType" />
> </xs:schema>'
> 3- I've build a table that use this Schema definition
> CREATE TABLE [dbo].[Customers]
> (
> [OID] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newid()),
> [Reference] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
> [XmlValue] [xml](DOCUMENT [dbo].[CustomerFile]) NOT NULL,
> CONSTRAINT [Unique_CustomerByOID] PRIMARY KEY CLUSTERED
> (
> [OID] ASC
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> 4- From what i understood the following statement should work but it
> doensn't
> INSERT Customers ( Reference, XmlValue ) VALUES( 'CUSS-007',
> '<Customer reference="CUSS-007" creationDate="2005-06-28T13:59:49.1548464"
> xmlns="http://XmlSpaces.Tests">
> <Name>
> <Civility code="Mr">Mister</Civility>
> <First>Brice</First>
> <Last>Prunier</Last>
> </Name>
> <Address code="Main">
> <Street>66 street A</Street>
> <Zip>11111</Zip>
> <Town>Seattle</Town>
> </Address>
> <Address code="Office">
> <Street>33 Avenue B</Street>
> <Zip>22222</Zip>
> <Town>Los Angeles</Town>
> </Address>
> </Customer>' )
> Trying the here above context raised the followings error
> 1- invalid format for Customer/@.creationDate
> After switching xs:dateTime to xs:string i get :
> Expected element : Name, http://XmlSpaces.Tests:Address where element
> http://XmlSpaces.Tests:Name was specified
> thanks for help
>

have a problem with pericision

I have been working on an application which require as much percision as we can get. We have defined all numeric values as (38,16) however when ever we divide a number by the result of a built in function it truncate the percision to 5 decimal place this cause us to have a small but enoying loss in the numbers being caculated we need a minimuim of 8 decimal places or better is there a work around or solution for this problem in sql server 2005. The more percision the better.

example: select tna/(select sum(tna) from taum) from taum where fund_id = 2345

the result is always 5 decimal places even though fund_id tna = 2569698.23 and sum(tna) =98745612325879.36 which should equal .00000002602341683313994 instead the result = .00000

Hi Caveman1,

I had try your issue using t_sample table:

CREATE TABLE t_sample ( a DECIMAL(38,16) NOT NULL, b DECIMAL(38,16) NOT NULL );

And I have received your same results to: SELECT a / b AS result FROM t_sample

I have changed precision and scale to my datatypes in t_sample table.

ALTER TABLE t_sample ALTER COLUMN a DECIMAL(18,10) NOT NULL, b DECIMAL(18,4) NOT NULL;

Great news: result = .00000002602341683313994324

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||the problem appears to only happen when a you use a function like sum(b) or avg(b) as the denominator....|||

Nope!

Books online:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation Result precision Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

I don't understand the table above.

here is and example I hope will clearify the issue

if I have a table that is defined as such

create table sec ( secid as int,nasset as numeric(38,16))

If I write a query that no tryies to calcalulate the percentage of a security bassed on total holdings the result is truncated to five decimal places.

select nasset/(select sum(nasset) from sec) from sec where secid = 1 -- truncate the result to five decimal places

I need aleast 8 places of percision in the result what do I need to do.....?

I found a work around that suggest that if you use a variable instead of the subquery that it would solve the problem but that did not work.

example:

declare @.totolA as numeric(38,16)

select @.totalA = sum(nasset) from sec

select nasset/@.totalA from sec where secid = 1 --still truncates to 5 decimal places

|||

I have the same question. How SQL decide the number of decimal in division operator. the formulation in the online book does not make sense. I got 6 decimal when I used (28,18) /(28,18) but over 20 decimal when I used (28,18)/(18,4).

looks like there is no problem for addtion, subtraction, and multiplication operators.

can anyone help ? thx.

sql

have a problem with pericision

I have been working on an application which require as much percision as we can get. We have defined all numeric values as (38,16) however when ever we divide a number by the result of a built in function it truncate the percision to 5 decimal place this cause us to have a small but enoying loss in the numbers being caculated we need a minimuim of 8 decimal places or better is there a work around or solution for this problem in sql server 2005. The more percision the better.

example: select tna/(select sum(tna) from taum) from taum where fund_id = 2345

the result is always 5 decimal places even though fund_id tna = 2569698.23 and sum(tna) =98745612325879.36 which should equal .00000002602341683313994 instead the result = .00000

Hi Caveman1,

I had try your issue using t_sample table:

CREATE TABLE t_sample ( a DECIMAL(38,16) NOT NULL, b DECIMAL(38,16) NOT NULL );

And I have received your same results to: SELECT a / b AS result FROM t_sample

I have changed precision and scale to my datatypes in t_sample table.

ALTER TABLE t_sample ALTER COLUMN a DECIMAL(18,10) NOT NULL, b DECIMAL(18,4) NOT NULL;

Great news: result = .00000002602341683313994324

Good coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||the problem appears to only happen when a you use a function like sum(b) or avg(b) as the denominator....|||

Nope!

Books online:

The operand expressions are denoted as expression e1, with precision p1 and scale s1, and expression e2, with precision p2 and scale s2. The precision and scale for any expression that is not decimal is the precision and scale defined for the data type of the expression.

Operation

Result precision

Result scale *

e1 + e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 - e2

max(s1, s2) + max(p1-s1, p2-s2) + 1

max(s1, s2)

e1 * e2

p1 + p2 + 1

s1 + s2

e1 / e2

p1 - s1 + s2 + max(6, s1 + p2 + 1)

max(6, s1 + p2 + 1)

e1 { UNION | EXCEPT | INTERSECT } e2

max(s1, s2) + max(p1-s1, p2-s2)

max(s1, s2)

* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

I don't understand the table above.

here is and example I hope will clearify the issue

if I have a table that is defined as such

create table sec ( secid as int,nasset as numeric(38,16))

If I write a query that no tryies to calcalulate the percentage of a security bassed on total holdings the result is truncated to five decimal places.

select nasset/(select sum(nasset) from sec) from sec where secid = 1 -- truncate the result to five decimal places

I need aleast 8 places of percision in the result what do I need to do.....?

I found a work around that suggest that if you use a variable instead of the subquery that it would solve the problem but that did not work.

example:

declare @.totolA as numeric(38,16)

select @.totalA = sum(nasset) from sec

select nasset/@.totalA from sec where secid = 1 --still truncates to 5 decimal places

Handling a SQL Exceptions and Custom Error Messages

Hello guys,

I need some ideas on how to handle an exception or a user defined error message.

I have a procedure that creates a new user. Lets say if the e-mail address entered is already in use. What are some of the best practices for notifying the user that the e-mail address is already in use?

This is what I was thinking...

Solution #1
-----
My proc will raise an error with a message id that is great than 50000, then my DAL will recognize this is a user defined error and spit back to the user instead of trapping it.

Solution #2
-----
The proc should have an output param ( @.CreationStatus CHAR(1) ).
If the @.CreationStatus has a value for example "E", I will have lookup the value for "E" in my app and spit back that custom error message. I don't really like this option because it is too concrete.

What are some of the ways you deal with this situation?

Your suggestions are greatly appreciated.

Thank you!

You could return a @.status value with (0=success, 1= failure and an appropriate status message @.Statusmsg ( = 'Success' if @.status = 0, custom error message if @.status = 1)

From your application you could check the value in @.status and if its not 0, then display the message from @.statusmsg. You can handle this in a number of ways, It comes down to setting up one standard way of doing it across all procs and communicating with your team and documenting it so the same logic is followed across all procs.