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

LVL 5
WillOwnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

WillOwnerAuthor Commented:
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

0
ste5anSenior DeveloperCommented:
Almost. XML is normally Unicode. Thus use NVARCHAR(255) as data type. Also use the correct dat type for your date DATETIME. Then I would not use ISNULL(). Cause you normally use XML in conjunction with XML Schema. Thus defining mandatory and nullable elements. When there is one missing, then it is normally allowd to be NULL. Forcining a different value is not a godd idea. Especially using an empty string is not a good idea. And last, but not least: Don't use DISTINCT. If multiple rows exists in the file, there must be a reason.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Agree with ste5an regarding :
NVARCHAR
DISTINCT

Little more relaxed about ISNULL - afterall, you are importing into a database which can have its own rules about nullability, and you might not have any influence / control over XML Schema. In your scenario, importing into a temp table first, then doing ISNULL is moot.

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.

I see you import into temp tables first, which I strongly recommend - it does alleviate some of the concern about datetime, but then brings into question about the need to check ISNULL.

And yes, retrieve the nodes in a combined select rather than selecting field-by-field (your old procedure).

So, a couple of areas to attend to, and a significant improvement on the old procedure. Well done :)

Cheers,
Mark
0
ste5anSenior DeveloperCommented:
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).
0
WillOwnerAuthor Commented:
Thank you both
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.