SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter procedure [dbo].[spGenericXML_Step]
	@fromTBLNM varchar(100) , 			-- has to be with schema name. For example, dbo.test
	@fromDBNM varchar(50) , 
	@XML_ID bigint = 0,				-- id from which start conversion
	@XMLSchemaNM varchar(50), 
	@selectCMD varchar(max) OUTPUT
	 
as

begin

 SET NOCOUNT ON


declare  @minid int,@maxid int, @tblcmd varchar(255), @cmd varchar(4000), @elementname varchar(50), @datatypename varchar(50);
declare @namespace_nm varchar(100), @rootelement_nm varchar(50), @insertcmd varchar(4000),  @base_datatype_nm varchar(50);
declare @restrictionvalue varchar(10), @datatypeis_fixed bit;

set @minid = 0;
set @maxid = -1;

declare @XMLMeta table (	schemaname varchar(50),	namespace_nm varchar(100),	elementname varchar(100),
	datatypename varchar(50),base_datatype_nm varchar(50),	elementkind varchar(5),elementkind_desc varchar(100), 
	dtrestrictionkind varchar(5),	dtrestrictionkind_desc varchar(100), 	datatypeis_fixed bit, 	restrictionvalue varchar(50),
	tid int identity(1,1) );

--=========
BEGIN TRY
--=========
insert into @XMLMeta (schemaname ,namespace_nm, elementname,	
	datatypename, base_datatype_nm,
	elementkind,	elementkind_desc, 	
	dtrestrictionkind,	dtrestrictionkind_desc, 
	datatypeis_fixed , 	restrictionvalue  )
select  sco.name, sn.name,  
	sc.name, sc1.name, 
	sc2.name,	sc1.kind,
	sc1.kind_desc, sf.kind,
	sf.kind_desc, sf.is_fixed, sf.value
from sys.xml_schema_components sc 
		inner join sys.xml_schema_component_placements scp
			on scp.xml_component_id  = sc.xml_component_id
		inner join sys.xml_schema_components sc1
			on scp.placed_xml_component_id = sc1.xml_component_id
		inner join sys.xml_schema_components sc2
			on sc1.base_xml_component_id = sc2.xml_component_id
		inner join sys.xml_schema_collections sco
			on sco.xml_collection_id = sc.xml_collection_id
		inner join sys.xml_schema_facets sf
			on sf.xml_component_id = sc1.xml_component_id
		inner join sys.xml_schema_namespaces sn
			on sn.xml_collection_id = sc.xml_collection_id
where  sco.name = @XMLSchemaNM;

--*********************************************************************************************
-- create permanent table to test xml schema because temporary table is not workin with registred xml schema
--**********************************************************************************************
IF exists (select * from sys.sysobjects where name = 'testXML')
	drop table dbo.testXML;

set @tblcmd = ' create table dbo.testXML (XML_Data XML(' + @XMLSchemaNM + ') );'

exec (@tblcmd);

--select * from @XMLMeta
--*********************************************************************************************
-- get the first element (root) tag name to add to the 
select @rootelement_nm = sc.name 
from sys.xml_schema_components sc
	inner join sys.xml_schema_component_placements scp
			on scp.xml_component_id  = sc.xml_component_id
	inner join sys.xml_schema_collections sco
			on sco.xml_collection_id = sc.xml_collection_id
where sco.name = @XMLSchemaNM and sc.kind = 'E' and scoping_xml_component_id is NULL;

select 	@minid = min(tid), @maxid = max(tid),  
	@namespace_nm = max(namespace_nm) 
from @XMLMeta;

set @insertcmd = 'insert into testXML (XML_Data) 
		select ''<xs:' + @rootelement_nm + ' xmlns:xs="' + @namespace_nm + '"> ''' + 
		
		' + cast(XML_Data as varchar(max)) + ' + '''	</xs:' + @rootelement_nm + '>''' ;


set @insertcmd = @insertcmd + ' from ' + @fromDBNM + '.' + @fromTBLNM + ' where Schema_NM = ''' + @XMLSchemaNM + '''  and XML_ID > ' + cast(@XML_ID as varchar(30)) + '; ' ;

exec (@insertcmd);

--***********************************************************************************************
-- if all records of the schema passed validation then proceed create extract select statement
--***********************************************************************************************
-- create select statement based on the schema elements and elements data types. data already verified

set @cmd = ' select  XML_ID, Table_NM, ';

while (@minid <= @maxid)
	begin

		IF (@minid > 1)
			set @cmd = @cmd + ', ';

		select 	@elementname = elementname,
			@datatypename = datatypename,
			@restrictionvalue = restrictionvalue,
			@datatypeis_fixed = datatypeis_fixed,
			@base_datatype_nm = base_datatype_nm
		from @XMLMeta
		where tid = @minid;

set @cmd = @cmd + '  [XML_Data].query(''//' + @elementname + ''').value(''.'',''' +  
	CASE 
		WHEN @base_datatype_nm = 'string' and @datatypeis_fixed = 0 THEN 'varchar(' + cast(@restrictionvalue as varchar(50) ) + ')'
		WHEN @base_datatype_nm = 'string' and @datatypeis_fixed = 1 THEN 'char(' + cast(@restrictionvalue as varchar(50) )  + ')'
		WHEN @datatypename = 'string' THEN 'varchar(max)'
		ELSE @datatypename
	END
	+ ' '') '
		
		set @minid = @minid + 1;
	end;

set @cmd = @cmd + 'from ' + @fromDBNM + '.' + @fromTBLNM + ' where Schema_NM = ''' + @XMLSchemaNM + ''' and XML_ID > ' + cast(@XML_ID as varchar(30)) + '; ' ;

-- output the command
set @selectCMD = @cmd;

--========
END TRY
--========
--***********************************************************************
--===========
BEGIN CATCH
--===========
RAISERROR ('Error' , 16, 1);

--===========
END CATCH
--===========


  SET NOCOUNT OFF

  return 0;

end
--===================================================================
-- End of procedure: dbo.spGenericXML_Step
--===================================================================