Very insightful article!

Any idea if UDT/CLR types can be used?

Say I have AddressInfo User Defined Type and table like this:

CREATE TABLE [AddressBook](

[ID] [int] IDENTITY(1,1) NOT NULL,

[LastName] [varchar](25) NOT NULL,

[FirstName] [varchar](25) NOT NULL,

[Address] [dbo].[AddressInfo] NULL,

)

XML that I am trying to insert looks like this:

DOE

JOHN

I tried this stored procedure:

CREATE PROCEDURE [dbo].[P_AddressBook_Insert]

(

@xmlAddressBook XML

)

AS

BEGIN

SET NOCOUNT OFF

INSERT

INTO [AddressBook]

([LastName], [FirstName],[Address])

SELECT

T.Item.query('./LastName').value('.', 'VARCHAR(25)') [LastName],

T.Item.query('./FirstName').value('.', 'VARCHAR(25)') [FirstName],

T.Item.query('./Address').value('.', 'AddressInfo') [Address],

FROM @xmlAddressBook.nodes('/AddressBook/Contact') AS T(Item)

END

However, I am not able to use AddressInfo UDT because it's not one of SQL Server built-in types.

I tried dbo.AddressInfo without single quotes it wouldn't take it either.

I also tried to use OpenXML for this and got the error message "CLR types cannot be used in an OpenXML WITH clause".

Please let me know if there is a syntax that I am missing or there is just no way to use UDTs with XML inserts.

Thank you!