I am getting an error in Microsoft SQL Server Management Studio when
designing a View on a Table. The table definition is:
CREATE TABLE [dbo].[Deals](
[recid] [int] IDENTITY(1,1) NOT NULL,
[xmlData] [xml] NOT NULL,
CONSTRAINT [PK_Deals] PRIMARY KEY CLUSTERED
(
[recid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The view definition is:

CREATE VIEW [dbo].[EnergyProfileView]
AS
SELECT recid,
xmlData.value('data((//DealNumber)[1])', 'int') AS DealNumber,
xmlData.value('data((//DealType)[1])', 'char(10)') AS DealType,
EP_Block.xmlData2.value('data((EP_HE)[1])', 'char(4)') AS EP_HE,
EP_Block.xmlData2.value('data((EP_MW)[1])', 'numeric(15,2)') AS EP_MW,
EP_Block.xmlData2.value('data((EP_Price)[1])', 'numeric(15,7)') AS EP_Price
FROM dbo.Deals
CROSS APPLY xmlData.nodes('//EP_Block') AS EP_Block(xmlData2)

The table and view work correctly, but the Microsoft SQL Server Management
Studio crashes with an error when I try to use the Design option.

The database server is SQL Server 2005 Standard Edition (SQL Server
0.0.4034). Here is the version information from Microsoft SQL Server
Management Studio:

Microsoft SQL Server Management Studio 9.00.4035.00
Microsoft Analysis Services Client Tools 2005.090.4035.00
Microsoft Data Access Components (MDAC) 2000.085.1132.00
(xpsp.080413-0852)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.3082
Operating System 5.1.2600

MichaelQuinlan (maquinlan (AT) nospam (DOT) nospam) writes:
Well, the view/query designer is a piece of crap, but I guess you knew
that already.

I tested your repro, and I was able to reproduce the problem with SSMS 2005
SP3. However, it does not appear with SSMS 2008 SP1.

If you feel strongly about it, you can report it on
http://connect.microsoft.com/SqlServer/Feedback, but given that it's fixed
in SQL 2008, I doubt that they will address it in SQL 2005.

The workaround is to not use tbe View Designer. When I open the view in
design mode in SQL 2008, I am told that it cannot be represented
graphically anyway.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx