dbo.formatxml

SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO

CREATE FUNCTION dbo.formatxml  (@filename varchar(255)
        ,@file_type_id int
        ,@terminator varchar(10))
RETURNS varchar(max) 
WITH encryption ,
EXECUTE AS CALLER 
AS
BEGIN
 
declare @field table( 
 ordering int     
,nullable bit  
,source_col varchar(50)  
,source_type varchar(50)  default('CharTerm')
,source_terminator varchar(50)   default(',')
,source_collation varchar(50)   default('')
,result_col_name varchar(50)   default('')
,result_type varchar(50)   default('')
) 
 

insert into @field(ordering,source_col) 
select * from  dbo.axSplit([dbo].[axReadFirstLine](@filename),@terminator,0) 

update @field
set source_terminator=@terminator


 MERGE @field AS target  
    USING (SELECT         
 source_col
, nullable
, source_type
, source_terminator
, source_collation
, result_col_name
, result_type 
FROM            enum_ms_file_type_field
WHERE        (file_type_id = @file_type_id)  
) AS source (source_col
, nullable
, source_type
, source_terminator
, source_collation
, result_col_name
, result_type )  
    ON (dbo.axRegexReplace(target.source_col,'[^\u0000-\u007F]+','') = dbo.axRegexReplace(source.source_col,'[^\u0000-\u007F]+','') ) 
    WHEN MATCHED THEN   
        UPDATE SET target.source_type = source.source_type
  ,target.source_terminator = source.source_terminator
  ,target.source_collation = source.source_collation
  ,target.result_col_name = source.result_col_name
  ,target.result_type = source.result_type
   ; 
--update @field
--set new_ordering= isnull((select top (1)urut from @tmp_split as xs where xs.value=[@field].source_col),-1)
 
-- select * from @field

update @field
set source_terminator= '\r\n'
where ordering=(select max(ordering) from @field as x)
  
declare @xml_record xml
set @xml_record=(
select  
  ordering as '@ID'  
, source_type as '@xsi_type' 
, source_terminator as '@TERMINATOR'
, source_collation as '@COLLATION' 
from @field
order by ordering
for xml path ('FIELD'),root('RECORD')
)
  
declare @xml_row xml
set @xml_row=(
select  
  ordering as '@SOURCE'  
, result_col_name as '@NAME'
, result_type as '@xsi_type'  
from @field
where nullif(result_col_name,'') is not null
order by ordering
for xml path ('COLUMN'),root('ROW')
)

declare @xrow xml 
  set @xrow=(
  select @xml_record,@xml_row 
  for xml path('BCPFORMAT')
 )
 declare @iOutput varchar(max)=replace(cast((select @xrow)  as  varchar(max)) ,'',
 
'')
 set @iOutput=REPLACE(@iOutput,'xsi_type','xsi:type')
  
return(@iOutput)
END;
GO