quote:

---

I’m sorry it took so long to get this validation done for you. XXX says that your Dx file is technically correct but it won’t work with our importer. Since VB6 doesn’t have a built in XML parser XXX had to write one from scratch, and the one he wrote looks for key pieces of information in specific places. This isn’t how XML is supposed to work -- XML shouldn’t force users to put carriage returns at the end of the line – but this is how the importer works. As such we’ll need to have you make some changes.

1. Place <Root> tags on their own lines.
2. Add carriage return/line feed after every closing tag “/>”

We apologize for the inconvenience.

---

CREATE PROCEDURE usp_Infochannel_DiaData_ForXML

AS

set nocount on

DECLARE @out_dte datetime
DECLARE @XMLFileName char(10)
DECLARE @xmlSQL varchar(300)
DECLARE @bcpString varchar(8000)
DECLARE @CopyCommand varchar(100)

/**************************************************************************************************
Get data for output

Note:  This gets all of the data currently ... needs to be modified to just get new and changed 
since last run.  I do not know how to do this yet
**************************************************************************************************/
Create table #BaseData
(epidia_num		int,
 clt_num		int,
 dia_dte		datetime,
 a123			varchar (70),
 axi1_cde		varchar (10),
 axi1_typ		varchar (10),
 axi1_icd9_cde		varchar (10),
 axi1_sec_cde		varchar (10),
 axi1_sec_typ		varchar (10),
 axi1_sec_icd9_cde	varchar (10),
 axi2_cde		varchar (10),
 axi2_typ		varchar (10),
 axi2_icd9_cde		varchar (10),
 axi2_sec_cde		varchar (10),
 axi2_sec_typ		varchar (10),
 axi2_sec_icd9_cde	varchar (10),
 axi3_cde		varchar (10),
 axi3_typ		varchar (10),
 axi3_sec_cde		varchar (10),
 axi4_cde		varchar (10),
 axi5_cde		varchar (10),
 prov_diag		tinyint) 


insert into #BaseData
	select * from v_epidia_latest_infoscriber 

/**************************************************************************************************
Format data for XML

**************************************************************************************************/
Create table ##Diagnosis
(DxIdentity             int Identity,
 DxEternalID		varchar(20),
 MemberExternalID	int,
 ICDDSM		  	char (1),
 PrimaryDx		char (1),
 Code			varchar (10),
 DxKey			varchar(10),
 ProvisionalDx		char (1),
 ProvSSN		varchar (9),
 Axis			char (1),
 StartDate		char (10),
 EndDate		varchar (10),
 XMLFileName            char(10)) 

/* Get Axis 1 data. Use Columns named axi1... */
insert into ##Diagnosis 
select DxEternalID = cast(epidia_num as varchar(20)) + '_1',
       MemberExternalID = clt_num,
       ICDDSM = '0',
       PrimaryDx = case a123 when 'A1' then '1' else '0' end, 
       Code =  axi1_icd9_cde,
       DxKey  = '',
       ProvisionalDx = case prov_diag when 0 then '0' else '1' end,
       ProvSSN = '',
       Axis = '1',
       StartDate = convert(char(10),dia_dte,101),
       EndDate = '',
       XMLFileName = null
  from #BaseData
 where axi1_icd9_cde <> ''

/* --- unimportant code deleted --- *?

/**************************************************************************************************
Output XML files
Notes:
1.  XMLFileName is mmddhhmmDX.748 - For each file output at for a given run the minute is incremeted by 1
**************************************************************************************************/
set @out_dte = getdate()

while (select count(*) from ##Diagnosis where XMLFileName is null) > 0
begin
	set @XMLFileName = replace(replace(replace(substring(convert(varchar, @out_dte, 120), 6, 11), ' ', ''), '-', ''), ':', '') + 'DX'

	update ##Diagnosis set XMLFileName = @XMLFileName  
	          from (select top 1000 *
			  from ##Diagnosis
	                 where XMLFileName is null
	                 order by DxIdentity) as d1
	         where ##Diagnosis.DxIdentity = d1.DxIdentity

	set @xmlSQL = 'Select DxEternalID,  MemberExternalID,  ' +
		      'ICDDSM,  PrimaryDx,  Code,  DxKey,  ProvisionalDx, ' +  
                      'ProvSSN,  Axis,  StartDate,  EndDate ' +
                      'from ##Diagnosis as Diagnosis ' + 
                      'where XMLFileName = ''' + @XMLFileName + ''' for xml auto'

	set @bcpString = 'bcp "' + @xmlSQL + '" queryout e:' + @XMLFileName +  '.TXT -c -r  -Sdogbert -Usa -Pui29wol '
	exec master.dbo.xp_cmdshell @bcpString, no_output

	set @CopyCommand = 'copy/b e:DX-XML-Header.txt+e:'+  @XMLFileName + '.TXT+e:DX-XML-Footer.txt ' + 'e:' + @XMLFileName + '.xml'
	exec master.dbo.xp_cmdshell @CopyCommand, no_output

	set @out_dte = DATEADD(mi,1,@out_dte)
end

• There is no answer?


• No one understood the question? (please ask for clarification)


• The question was boring?


• Other?