Creating Table Type with another type (user defined) in SQL Server 2016

Question

I am trying to create a type with another type like below.

CREATE TYPE [dbo].[PropertyImage] AS TABLE
(
    [PropertyImage] [NVARCHAR](MAX) NULL,
    [PropertyType] [NVARCHAR](500) NULL
)

CREATE TYPE [dbo].[Properties] AS TABLE
(
    [Id] INT,
    [UserId] INT,
    [PropertyImageList] [dbo].[PropertyImage]
)

I get this error:

The column "PropertyImageList" does not have a valid data type. A column cannot be of a user-defined table type.

Are there any alternatives?

"A column cannot be of a user-defined table type." message is very clear. You can't do it. — PSK, Jan 21 '19 at 10:10
You want to create a one-to-many relationship, check [this](https://stackoverflow.com/questions/7296846/how-to-implement-one-to-one-one-to-many-and-many-to-many-relationships-while-de) SO post. — kaffekopp, Jan 21 '19 at 10:18
The fact that you are trying to use a table-value data type for a column implies that you are trying to store multiple values in a single entity. The solution, therefore, is one of 2 things; Add more columns to your table is it is a 1-to-1 relationship or create a new table if it's 1-to-many. — Thom A, Jan 21 '19 at 10:19

Savo Zirojević · Answer 1 · 2019-01-21T11:11:23.527

Maybe you can use an XML column with a schema.

 CREATE XML SCHEMA COLLECTION [PropertyImageXMLSchema] AS
 N' <schema xmlns="http://www.w3.org/2001/XMLSchema">  
          <element name="Image">  
           <complexType>
            <sequence>
                <element minOccurs="0" name="PropertyImage" type="string"/>
                <element minOccurs="0" name="PropertyType" type="string"/>
            </sequence>
           </complexType>
          </element>
        </schema>  ';
    GO
    CREATE TABLE [Properties]  (
        [Id] int,
        [UserId] int,
        [PropertyImageList] XML(PropertyImageXMLSchema)
    );
   GO
    INSERT INTO [Properties] (Id, UserId,PropertyImageList)
    VALUES (1,1,
    N'<Image>
        <PropertyImage>test image 1 </PropertyImage>
        <PropertyType>test type 1</PropertyType>
    </Image>
    <Image>
        <PropertyImage>test image 2 </PropertyImage>
        <PropertyType>test type 2</PropertyType>
    </Image>'
    );

This is more of a comment, not an answer. I would suggest expanding on this to explain your answer. Give an example, etc. — Thom A, Jan 21 '19 at 10:20

score 0 · Answer 2 · edited Jan 21 '19 at 11:08

0

CREATE TYPE TypeName FROM nvarchar(max) 

CREATE TABLE Test(Test_Name varchar(50), Test_Data TypeName)
--Custom Type Table 

CREATE TYPE customTypeTable AS Table (name varchar(100), age int, location TypeName)

For more details please click here

edited Jan 21 '19 at 11:08

Thom A

88,727
11
45
75

answered Jan 21 '19 at 10:27

Samim Hussain

396
5
14

Can you fit your solution in my problem? – Jitendra Pancholi Jan 21 '19 at 11:05

score 0 · Answer 3 · answered Jan 21 '19 at 13:10

0

Why do you need user defined types at all? Is there a problem with the typical SQL solution of two tables?

CREATE TYPE [dbo].Properties (
    PropertyId INT IDENTITY(1, 1) PRIMARY KEY,
    UserId INT
);

CREATE TABLE PropertyImages (
    PropertyImageId INT IDENTITY(1, 1) PRIMARY KEY,
    PropertyId INT NOT NULL,
    PropertyImage NVARCHAR(MAX) NULL,
    PropertyType [NVARCHAR(500) NULL,
    CONSTRAINT FK_PropertyImages_PropertyId FOREIGN KEY (PropertyId) REFERENCES Properties(PropertyId)
);

answered Jan 21 '19 at 13:10

Gordon Linoff

1,242,037
58
646
786

Yes, I have a property table and it's images in separate table. Now requirement is to save a list of properties along with images in 1 DB call. – Jitendra Pancholi Jan 22 '19 at 10:30
@JitendraPancholi . . . Then use the `PropertyImages` table as described in this answer. – Gordon Linoff Jan 22 '19 at 12:41

Creating Table Type with another type (user defined) in SQL Server 2016

3 Answers3