Need critique and help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need critique and help

Author

Topic

LaurieCox

158 Posts

Posted - 2006-11-30 : 13:55:32

Hi,

I need some help. I am an old dog (cobol/db2/cics programmer) needing to be taught new tricks.

Notes:

1. ddl , sample data and current sql will follow on next post (unless somebody replies to this post before I get a chance to make the next post).
2. Do not bother to criticize the design and column names of the BaseData table. I have no control over that and it is what it is.

My Task:

Create an XML file for transmission of diagnosis data (new and updated since last run) to <other company> using a stored procedure. We will be using ftp for the actual transfer.

Problems/Questions/Wandering thoughts/etc:

Each row in the BaseData table can generate up to five <diagnosis> elements in the XML file (one for each "Axis"). I have written five insert/select statements. Is this the best way to do it i.e. is there another more elegant or efficient way (that does not affect readability or ease in maintenance/enhancements)?

Each XML file should contain no more than 1000 elements. I was thinking about doing it this way:


while (select count(*) from #Diagnosis) > 0
begin
	select top 1000 * from #Diagnosis for xml auto
	-- get data into file an XML file (see below for questions)
	-- Delete top 1000  written from #Diagnosis  (see below for questions)
        -- Maybe 
end

Questions about code above:

Delete top 1000
How do I do this? I don't think I can use this solution, as I don't have a pk in the temp table Diagnosis. Maybe I should add one. Though I don't want it in the XML elements. Of course I could list the columns I want in the select for xml auto … or is there a better way to create multiple xml files from the result set? The output file name's dynamic: {timestamp}DX.{facility ID} with timestamp in the form MMDDhhmm … which could cause problems as the loop above will surely create the files more than once a minute. I could always get the initial time and then increment the minutes with in the loop … any suggestions …

Get data into xml file:
In my web searches I found two ways to do this:
Called from the stored procedure:


exec sp_makewebtask 
	@outputfile = 'e:TestIt.xml',
	@query = 'select top 1000 * from ##Diagnosis as Diagnosis for xml auto',
        @templatefile ='e:DX-XML.txt'

Problem with this way is that it puts a cr/lf every 2033 characters and thus ruins the xml.

Or … Run at the command prompt:
bcp "'select top 1000 * from ##Diagnosis as Diagnosis for xml auto'" queryout e:data.xml -w -r '' -S server -U username -P password

Solves the cr/lf problem but introduces more:

1. Can I call this from inside the stored procedure? If yes then how?
2. I don't get the <root> and other header tags in the file that I supplied with the DX-XML template file in the makewebtask solution.
3. Does Diagnosis table need to be a permanent table?

Thanks for any help you can give me.

Laurie

P.S. I do plan on resurrecting my How to think in set based SQL thread when I am not busy doing real work … maybe some time next year.

LaurieCox

158 Posts

Posted - 2006-11-30 : 13:56:59

ddl, sample data and current sql as promiced in the OP.


Create table #BaseData
(epidia_num		int,
 clt_num		int,
 dia_dte		datetime,
 a123			varchar (70),
 axi1_cde		varchar (5),
 axi1_typ		varchar (10),
 axi1_icd9_cde		varchar (10),
 axi1_sec_cde		varchar (5),
 axi1_sec_typ		varchar (10),
 axi1_sec_icd9_cde	varchar (10),
 axi2_cde		varchar (5),
 axi2_typ		varchar (10),
 axi2_icd9_cde		varchar (10),
 axi2_sec_cde		varchar (5),
 axi2_sec_typ		varchar (10),
 axi2_sec_icd9_cde	varchar (10),
 axi3_cde		varchar (5),
 axi3_typ		varchar (10),
 axi3_sec_cde		varchar (5),
 axi4_cde		varchar (5),
 axi5_cde		varchar (5),
 prov_diag		tinyint) 

insert into #BaseData
(epidia_num,clt_num,dia_dte,a123,
 axi1_cde,axi1_typ,axi1_icd9_cde,
 axi1_sec_cde,axi1_sec_typ,axi1_sec_icd9_cde,
 axi2_cde,axi2_typ,axi2_icd9_cde,
 axi2_sec_cde,axi2_sec_typ,axi2_sec_icd9_cde,
 axi3_cde,axi3_typ,
 axi3_sec_cde,
 axi4_cde,
 axi5_cde,
prov_diag)
select  1070, 1751, '1994-07-14 00:00:00.000', 'A1', 
        '29570', 'DSM-IV', '295.70', '00000', 'DSM-IV', 
        '', 'V7109', 'DSM-IV', 'V71.09', '00000', 'DSM-IV', '', 
        'V655', 'DSM-IV', '', '00001', '00054', 0 union all
select  42480, 147, '2006-08-23 00:00:00.000', 'A2', 
        '31230', '', '312.30', '31100', '', '311', 
        '31800', '', '318.0', '30140', '', '301.4', '', 
        '', '00000', '00002', '30', 0 union all 
select  41655, 805, '2006-08-23 00:00:00.000', 'A3', 
        '30030', '', '300.3', '29890', '', '298.9', 
        '79990', '', '799.9', 'V7109', '', 'V71.09', 
        '34300', '', '00000', '00003', '65', 0 union all 
select  27336, 1752, '2002-05-13 00:00:00.000', 'A1', 
        '29590', 'DSM-IV', '295.90', 'V7109', 'DSM-IV', 
        'V71.09', 'V7109', 'DSM-IV', 'V71.09', 'V7109', 
        'DSM-IV', 'V71.09', '00000', 'DSM-IV', '00000', 
        '00002', '55', 0 union all 
select  36013, 575, '2005-05-18 00:00:00.000', 'A3', 
        '29890', '', '298.9', '00000', '', ' ', 'V7109', 
        '', 'V71.09', 'V7109', '', 'V71.09', '34390', 
        '', '00000', '00004', '45', 0 union  all
select  35241, 1753, '2005-12-14 00:00:00.000', 'A1', 
        '29680', '', '296.80', 'V7109', '', 'V71.09', 
        'V7109', '', 'V71.09', 'V7109', '', 'V71.09', 
        '2780', '', '00000', '00001', '50', 0 union  all
select  41232, 146, '2006-02-27 00:00:00.000', 'A2', 
        '31234', '', '312.34', '30930', '', '309.3', 
        '31800', '', '318.0', 'V7109', '', 'V71.09', 
        '', '', '00000', '00000', '50', 0 union  all
select  40014, 846, '2005-09-06 00:00:00.000', 'A3', 
        '00000', '', ' ', 'V7109', '', 'V71.09', 'V7109', 
        '', 'V71.09', 'V7109', '', 'V71.09', '34390', '', 
        '00000', '00003', '21', 0 union  all
select  38469, 1749, '2005-10-17 00:00:00.000', 
        'A1', '29570', '', '295.70', 'V7109', '', 
        'V71.09', 'V7109', '', 'V71.09', 'V7109', '', 
        'V71.09', '', '', '00000', '00000', '60', 0

select * from #basedata

Create table ##Diagnosis
(DxEternalID		int,
 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)) 

/* Get Axis 1 data. Use Columns named axi1... */
insert into ##Diagnosis
select epidia_num,
       clt_num,
       ICDDSM = Case when axi1_typ like '%DSM%' then '0' else '1' end,
       PrimaryDx = case a123 when 'A1' then '1' else '0' end, 
       axi1_icd9_cde,
       '',
       ProvisionalDx = case prov_diag when 0 then '0' else '1' end,
       '',
       Axis = case when axi1_typ like '%DSM%' then '1' else '' end,
       convert(char(10),dia_dte,101),
       ''
  from #BaseData
 where axi1_icd9_cde <> ''

/* Get Axis 1 secondary data. Use Columns named axi1_sec... */
insert into ##Diagnosis
select epidia_num,
       clt_num,
       ICDDSM = Case when axi1_sec_typ like '%DSM%' then '0' else '1' end,
       PrimaryDx = '0', 
       axi1_sec_icd9_cde,
       '',
       ProvisionalDx = case prov_diag when 0 then '0' else '1' end,
       '',
       Axis = case when axi1_sec_typ like '%DSM%' then '1' else '' end,
       convert(char(10),dia_dte,101),
       ''
  from #BaseData
 where axi1_sec_icd9_cde <> ''

/* Get Axis 2 data. Use Columns named axi2... */
insert into ##Diagnosis
select epidia_num,
       clt_num,
       ICDDSM = Case when axi2_typ like '%DSM%' then '0' else '1' end,
       PrimaryDx = case a123 when 'A2' then '1' else '0' end,
       axi2_icd9_cde,
       '',
       ProvisionalDx = case prov_diag when 0 then '0' else '1' end,
       '',
       Axis = case when axi2_typ like '%DSM%' then '2' else '' end,
       convert(char(10),dia_dte,101),
       ''
  from #BaseData
 where axi2_icd9_cde <> ''

/* Get Axis 2 secondary data. Use Columns named axi2_sec... */
insert into ##Diagnosis
select epidia_num,
       clt_num,
       ICDDSM = Case when axi2_sec_typ like '%DSM%' then '0' else '1' end,
       PrimaryDx = '0', -- This is a secondary diag so by default it is not Primary (?)
       axi2_sec_icd9_cde,
       '',
       ProvisionalDx = case prov_diag when 0 then '0' else '1' end,
       '',
       Axis = case when axi2_sec_typ like '%DSM%' then '2' else '' end,
       convert(char(10),dia_dte,101),
       ''
  from #BaseData
 where axi2_sec_icd9_cde <> ''

/* Get Axis 3 data. Use Columns named axi3... */
insert into ##Diagnosis
select epidia_num,
       clt_num,
       ICDDSM = Case when axi3_typ like '%DSM%' then '0' else '1' end,
       PrimaryDx = case a123 when 'A3' then '1' else '0' end, 
       axi3_cde,
       '',
       ProvisionalDx = case prov_diag when 0 then '0' else '1' end,
       '',
       Axis = case when axi3_typ like '%DSM%' then '3' else '' end, 
       convert(char(10),dia_dte,101),
       ''
  from #BaseData
 where axi3_cde <> ''

/* Get Axis 4 data. Use Columns named axi4... */
insert into ##Diagnosis
select epidia_num,
       clt_num,
       ICDDSM = '0', -- Axis 4 has no type
       PrimaryDx = '0', -- Axis4 is never primary
       axi4_cde,
       '',
       ProvisionalDx = case prov_diag when 0 then '0' else '1' end,
       '',
       Axis = '4',  
       convert(char(10),dia_dte,101),
       ''
  from #BaseData
 where axi4_cde <> ''

/* Get Axis 5 data. Use Columns named axi5... */
insert into ##Diagnosis
select epidia_num,
       clt_num,
       ICDDSM = '0', -- Axis 5 has no type
       PrimaryDx = '0', -- Axis5 is never primary
       axi5_cde,
       '',
       ProvisionalDx = case prov_diag when 0 then '0' else '1' end,
       '',
       Axis = '5',
       convert(char(10),dia_dte,101),
       ''
  from #BaseData
 where axi5_cde <> ''

/*Format for xml*/
select * from ##Diagnosis as Diagnosis for xml auto

Kristen
Test

22859 Posts

Posted - 2006-11-30 : 15:23:33

You would be better off with a CHANGED_DATE column, and then doing

select TOP 1000 WITH TIES *
from Diagnosis
WHERE CHANGED_DATE > @LastShippedDate
ORDER BY CHANGED_DATE
for xml auto

and thus you would ship up to 1000 rows which have changed since last time, each time you are asked for a batch of XML.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources