• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 62
  • Last Modified:

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

0
wkrasner
Asked:
wkrasner
  • 2
  • 2
2 Solutions
 
wkrasnerAuthor 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
 
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
 
wkrasnerAuthor Commented:
Thank you both
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now