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:
JOHN 123 STREET
I tried this stored procedure:
CREATE PROCEDURE [dbo].[P_AddressBook_Insert]
SET NOCOUNT OFF
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)
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.