how to get XML SCHEMA COLLECTION definition
-
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....
-
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_idfrom (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_idthen '[' + 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 nulland c.system_type_id = c.user_type_idthen ' COLLATE ' + c.collation_nameelse ''end + case when c.is_nullable = 1 then ' NULL'else ' NOT NULL'end + case when c.default_object_id != 0then ' CONSTRAINT [' + object_name(c.default_object_id) + ']'+ ' DEFAULT ' + object_definition(c.default_object_id)else ''end + case when cc.object_id is not nullthen ' CONSTRAINT [' + cc.name + '] CHECK '+ cc.definitionelse ''end + case when c.is_identity = 1then ' IDENTITY('+ cast(IDENTITYPROPERTY(c.object_id,'SeedValue') as VARCHAR(5))+ ','+ cast(IDENTITYPROPERTY(c.object_id,'IncrementValue') as VARCHAR(5))+ ')'else ''endendfrom sys.columns c with (nolock)join sys.types tp with (nolock) on c.user_type_id = tp.user_type_idleft join sys.check_constraints cc with (nolock) on c.[object_id] = cc.parent_object_idand cc.parent_column_id = c.column_idwhere c.[object_id] = @object_idorder by c.column_idfor 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 ''endfrom sys.index_columns ic with (nolock)where i.object_id = ic.object_idand i.index_id = ic.index_idforxml path(N''),type) as nvarchar(max)), 1, 2, '')) + ')'from sys.indexes i with (nolock)where i.object_id = @object_idand i.is_primary_key = 1), '') + char(13) + ');';print @SQL; -
To get the information on the XML Schema collections use the sys.xml_schema_collections view and the xml_schema_namespace function
SELECTSXSC.nameFROM 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
-
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