asked on

Looking to clean up sloppy code and integrate the query into one table through the stored procedure. Importing from xml to ms sql datatable

My question is there any way for me to integrate the 3 prongs of my stored procedure and thus allow me to put all the data points in the node into one data table. I am working in MS SQL
The xml code as follows:

<PCRDATA>
     <Activity>
       <Procedures>
         <Procedure>
               <CardiacAction ID="12-Lead EKG">12-Lead EKG</CardiacAction>
              <EnteredBy ID="586807">xxxxxxxxxxxx</EnteredBy>
              <Procedure ID="Cardiac">Cardiac</Procedure>
              <Comments>12-Lead EKG performed by xxxxxr. Successful.  Authorization: Via Protocol. </Comments>
              <ConsultEventID>-26</ConsultEventID>
              <EventID>219271851</EventID>
              <ProcedureDate>2018-01-17T19:36:00</ProcedureDate>
      </Procedure>
      </Procedures>
    </Activity>
</PCRDATA>

Open in new window

The stored procedure is as follows:

IF OBJECT_ID('tempdb..#tempDATA5') IS NOT NULL DROP TABLE #tempDATA5
CREATE TABLE #tempDATA5 ( Meds VARCHAR(20) )
insert into #tempDATA5
SELECT A.EventID.value('.', 'Varchar(20)') AS MedicationDT
FROM   @xml.nodes('/PCRDATA/Activity/Procedures/Procedure/EventID') A(EventID)
insert into dbo.ProcedureList
select @PRID_S,  Meds,(row_Number() OVER (ORDER BY @PRID_S )),@PRID_S+'-'+ convert(varchar(10),(row_Number() OVER (ORDER BY Meds ))) from #tempDATA5

IF OBJECT_ID('tempdb..#tempDATA6') IS NOT NULL DROP TABLE #tempDATA6
CREATE TABLE #tempDATA6 ( Meds VARCHAR(20) )
insert into #tempDATA6
SELECT A.[Procedure].value('.', 'Varchar(20)') AS MedicationDT
FROM   @xml.nodes('/PCRDATA/Activity/Procedures/Procedure/Procedure') A([Procedure])
insert into dbo.ProcedureType
select @PRID_S,  Meds,(row_Number() OVER (ORDER BY @PRID_S )),@PRID_S+'-'+ convert(varchar(10),(row_Number() OVER (ORDER BY @PRID_S ))) from #tempDATA6


IF OBJECT_ID('tempdb..#tempDATA7') IS NOT NULL DROP TABLE #tempDATA7
CREATE TABLE #tempDATA7 ( Meds VARCHAR(20) )
insert into #tempDATA7
SELECT A.[CardiacAction].value('.', 'Varchar(20)') AS MedicationDT
FROM   @xml.nodes('/PCRDATA/Activity/Procedures/Procedure/CardiacAction') A([CardiacAction])
insert into dbo.ProcedureType
select @PRID_S,  Meds,(row_Number() OVER (ORDER BY @PRID_S )),@PRID_S+'-'+ convert(varchar(10),(row_Number() OVER (ORDER BY @PRID_S ))) from #tempDATA7

Open in new window

Will

ASKER

Ok I came up with this with a little research. Wondering if its best practice

select distinct
	isnull(x.v.value ('ProcedureDate[1]', 'Varchar(20)'),'1900-01-01') as ProcedureDate,
	isnull(x.v.value ('CardiacAction[1]', 'Varchar(20)'),'')  As CardiacAction,
	isnull(x.v.value ('MedicationDosage[1]', 'Varchar(20)'),'') as MedDose,
	isnull(x.v.value ('MedicationName[1]', 'Varchar(20)'),'') as MedUnits,
	isnull(x.v.value ('MedicationRoute[1]', 'Varchar(20)'),'') as MedRoute,
	
	
	isnull(x.v.value ('IntubationVerifications[1]', 'Varchar(100)'),'') as IntubateVerify
		

from
@xml.nodes('/PCRDATA/Activity/Procedures/Procedure') x(v)
order by ProcedureDate

Open in new window

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

While in principle, you should use DATETIME, when importing data, it can be problematic especially with some of the ISO8601 formats (not so much style 126, but 127 / other time zones). I am always in two minds about dates.

In general: Yes.

BUT: In XML this date format is well-defined (xs:date).

Will

ASKER

Thank you both