how to get XML SCHEMA COLLECTION definition

  • i1888

    SSCommitted

    Points: 1806

    Hi All,

    I am trying to copy the table data from db to another db, because some tables has xml column with a schema collection, I would like to get difinition of the xml schema collection and create it on the target db, otherwise I will get below error:

    Xml columns cannot refer to schemata across databases.

    My plan is for those tables with xml column with schema collection, I will:

    1. Get all tables/columns that requires xml schema

    SELECT t.name AS table_name,

    SCHEMA_NAME(t.schema_id) AS schema_name,

    c.name AS column_nam,

    c2.name

    FROM sys.tables AS t

    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

    inner join sys.xml_schema_collections c2 on c.xml_collection_id = C2.xml_collection_id

    2. Get table definition

    DECLARE

    @object_name SYSNAME

    , @object_id INT

    , @SQL NVARCHAR(MAX)

    SELECT

    @object_name = '[' + OBJECT_SCHEMA_NAME(o.[object_id]) + '].[' + OBJECT_NAME([object_id]) + ']'

    , @object_id = [object_id]

    FROM (SELECT [object_id] = OBJECT_ID('TableName', 'U')) o

    SELECT @SQL = 'CREATE TABLE ' + @object_name + CHAR(13) + '(' + CHAR(13) + STUFF((

    SELECT CHAR(13) + ' , [' + c.name + '] ' +

    CASE WHEN c.is_computed = 1

    THEN 'AS ' + OBJECT_DEFINITION(c.[object_id], c.column_id)

    ELSE

    CASE WHEN c.system_type_id != c.user_type_id

    THEN '[' + SCHEMA_NAME(tp.[schema_id]) + '].[' + tp.name + ']'

    ELSE '[' + UPPER(tp.name) + ']'

    END +

    CASE

    WHEN tp.name IN ('varchar', 'char', 'varbinary', 'binary')

    THEN '(' + CASE WHEN c.max_length = -1

    THEN 'MAX'

    ELSE CAST(c.max_length AS VARCHAR(5))

    END + ')'

    WHEN tp.name IN ('nvarchar', 'nchar')

    THEN '(' + CASE WHEN c.max_length = -1

    THEN 'MAX'

    ELSE CAST(c.max_length / 2 AS VARCHAR(5))

    END + ')'

    WHEN tp.name IN ('datetime2', 'time2', 'datetimeoffset')

    THEN '(' + CAST(c.scale AS VARCHAR(5)) + ')'

    WHEN tp.name = 'decimal'

    THEN '(' + CAST(c.[precision] AS VARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'

    ELSE ''

    END +

    CASE WHEN c.collation_name IS NOT NULL AND c.system_type_id = c.user_type_id

    THEN ' COLLATE ' + c.collation_name

    ELSE ''

    END +

    CASE WHEN c.is_nullable = 1

    THEN ' NULL'

    ELSE ' NOT NULL'

    END +

    CASE WHEN c.default_object_id != 0

    THEN ' CONSTRAINT [' + OBJECT_NAME(c.default_object_id) + ']' +

    ' DEFAULT ' + OBJECT_DEFINITION(c.default_object_id)

    ELSE ''

    END +

    CASE WHEN cc.[object_id] IS NOT NULL

    THEN ' CONSTRAINT [' + cc.name + '] CHECK ' + cc.[definition]

    ELSE ''

    END +

    CASE WHEN c.is_identity = 1

    THEN ' IDENTITY(' + CAST(IDENTITYPROPERTY(c.[object_id], 'SeedValue') AS VARCHAR(5)) + ',' +

    CAST(IDENTITYPROPERTY(c.[object_id], 'IncrementValue') AS VARCHAR(5)) + ')'

    ELSE ''

    END

    END

    FROM sys.columns c WITH(NOLOCK)

    JOIN sys.types tp WITH(NOLOCK) ON c.user_type_id = tp.user_type_id

    LEFT JOIN sys.check_constraints cc WITH(NOLOCK)

    ON c.[object_id] = cc.parent_object_id

    AND cc.parent_column_id = c.column_id

    WHERE c.[object_id] = @object_id

    ORDER BY c.column_id

    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ') +

    ISNULL((SELECT '

    , CONSTRAINT [' + i.name + '] PRIMARY KEY ' +

    CASE WHEN i.index_id = 1

    THEN 'CLUSTERED'

    ELSE 'NONCLUSTERED'

    END +' (' + (

    SELECT STUFF(CAST((

    SELECT ', [' + COL_NAME(ic.[object_id], ic.column_id) + ']' +

    CASE WHEN ic.is_descending_key = 1

    THEN ' DESC'

    ELSE ''

    END

    FROM sys.index_columns ic WITH(NOLOCK)

    WHERE i.[object_id] = ic.[object_id]

    AND i.index_id = ic.index_id

    FOR XML PATH(N''), TYPE) AS NVARCHAR(MAX)), 1, 2, '')) + ')'

    FROM sys.indexes i WITH(NOLOCK)

    WHERE i.[object_id] = @object_id

    AND i.is_primary_key = 1), '') + CHAR(13) + ');'

    PRINT @SQL

    3. Get XML Schema definition

    4. Combine 2 and 3.

    5

    insert into newtable select .... cast(xmlColumnas xml) from OldDB.dbo.oldtable

    I am stuck at step 3 now....

  • Phil Parkin

    SSC Guru

    Points: 244578

    And here is part 2 again, for those people who prefer their SQL formatted 🙂

    declare @object_name sysname

    ,@object_id int

    ,@SQL nvarchar(max);

    select @object_name = '[' + object_schema_name(o.object_id) + '].[' + object_name(o.object_id) + ']'

    ,@object_id = o.object_id

    from (select object_id = object_id('TableName', 'U')

    ) o;

    select @SQL = 'CREATE TABLE ' + @object_name + char(13) + '(' + char(13)

    + stuff((select char(13) + ' , [' + c.name + '] ' + case when c.is_computed = 1 then 'AS ' + object_definition(c.object_id, c.column_id)

    else case when c.system_type_id != c.user_type_id

    then '[' + schema_name(tp.schema_id) + '].[' + tp.name + ']'

    else '[' + upper(tp.name) + ']'

    end + case when tp.name in ('varchar', 'char', 'varbinary', 'binary')

    then '(' + case when c.max_length = -1 then 'MAX'

    else cast(c.max_length as varchar(5))

    end + ')'

    when tp.name in ('nvarchar', 'nchar')

    then '(' + case when c.max_length = -1 then 'MAX'

    else cast(c.max_length / 2 as varchar(5))

    end + ')'

    when tp.name in ('datetime2', 'time2', 'datetimeoffset')

    then '(' + cast(c.scale as varchar(5)) + ')'

    when tp.name = 'decimal'

    then '(' + cast(c.precision as varchar(5)) + ','

    + cast(c.scale as varchar(5)) + ')'

    else ''

    end + case when c.collation_name is not null

    and c.system_type_id = c.user_type_id

    then ' COLLATE ' + c.collation_name

    else ''

    end + case when c.is_nullable = 1 then ' NULL'

    else ' NOT NULL'

    end + case when c.default_object_id != 0

    then ' CONSTRAINT [' + object_name(c.default_object_id) + ']'

    + ' DEFAULT ' + object_definition(c.default_object_id)

    else ''

    end + case when cc.object_id is not null

    then ' CONSTRAINT [' + cc.name + '] CHECK '

    + cc.definition

    else ''

    end + case when c.is_identity = 1

    then ' IDENTITY('

    + cast(IDENTITYPROPERTY(c.object_id,

    'SeedValue') as VARCHAR(5))

    + ','

    + cast(IDENTITYPROPERTY(c.object_id,

    'IncrementValue') as VARCHAR(5))

    + ')'

    else ''

    end

    end

    from sys.columns c with (nolock)

    join sys.types tp with (nolock) on c.user_type_id = tp.user_type_id

    left join sys.check_constraints cc with (nolock) on c.[object_id] = cc.parent_object_id

    and cc.parent_column_id = c.column_id

    where c.[object_id] = @object_id

    order by c.column_id

    for xml path('')

    ,type).value('.', 'NVARCHAR(MAX)'), 1, 7, ' ')

    + isnull((select '

    , CONSTRAINT [' + i.name + '] PRIMARY KEY ' + case when i.index_id = 1 then 'CLUSTERED'

    else 'NONCLUSTERED'

    end + ' ('

    + (select stuff(cast((select ', [' + col_name(ic.object_id, ic.column_id) + ']' + case when ic.is_descending_key = 1 then ' DESC'

    else ''

    end

    from sys.index_columns ic with (nolock)

    where i.object_id = ic.object_id

    and i.index_id = ic.index_id

    for

    xml path(N'')

    ,type

    ) as nvarchar(max)), 1, 2, '')

    ) + ')'

    from sys.indexes i with (nolock)

    where i.object_id = @object_id

    and i.is_primary_key = 1

    ), '') + char(13) + ');';

    print @SQL;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Eirikur Eiriksson

    SSC Guru

    Points: 182438

    To get the information on the XML Schema collections use the sys.xml_schema_collections view and the xml_schema_namespace function

    😎

    SELECT

    SXSC.name

    FROM sys.xml_schema_collections SXSC;

    SELECT xml_schema_namespace(N'[RELATIONAL SCHEMA]',N'[XML SCHEMA COLLECTION NAME]');

    There are also two undocumented stored procedures, sys.sp_xml_schema_rowset and sys.sp_xml_schema_rowset2, the latter will list all schema collections if executed without parameters.

    Edit: additional info

  • i1888

    SSCommitted

    Points: 1806

    Thanks Buddy, I will give it a try!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply