Trying to convert the following sql query (msssql) into a Parameter for execution. I am completely lost how to place the apostrophes. Can someone get me started? Thanks

set @SQL=
SELECT 

@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')

FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK @FILENAME,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
	if exists(select* from [dbo].[MICU] where PRID=@PRID)	
	UPDATE  [dbo].[MICU] 
	
	SET dbo.MICU.Dispatched= @DP,dbo.MICU.Enroute= @er
	where dbo.MICU.PRID=@PRID

ELSE

 INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values(@PRID,@DP,@er)

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.

Pawan KumarDatabase ExpertCommented:
Please use this . I have handled the single quotes for your.

set @SQL= '
SELECT 
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')

FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK ' + @FILENAME + ' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
	if exists(select* from [dbo].[MICU] where PRID= ' + @PRID + ' )	
	UPDATE  [dbo].[MICU] 
	
	SET dbo.MICU.Dispatched=  ' + @DP + ' ,dbo.MICU.Enroute=  ' + @er + ' 
	where dbo.MICU.PRID= ' + @PRID + '
ELSE
 INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( ' + @PRID + ',' + @DP + ' , ' + @er + ')'

Open in new window

0
WillOwnerAuthor Commented:
Thank you Pawan.  I get the following error when executing.  I am thinking I may still be missing an '
Msg 245, Level 16, State 1, Procedure MICU_XML3, Line 33
Conversion failed when converting the varchar value '
SELECT 
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')

FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK c:\pcr\xml\48633430.XML ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
	if exists(select* from [dbo].[MICU] where PRID= ' to data type int.

Open in new window

0
WillOwnerAuthor Commented:
I looked a little more carefully,  looks like the filename is not wrapped in '    tried a couple of variants didn't work.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Try Now
WillOwnerAuthor Commented:
I was able to get the ' wrapped around the filename still get same error
0
WillOwnerAuthor Commented: 
Msg 245, Level 16, State 1, Procedure MICU_XML3, Line 33
Conversion failed when converting the varchar value '
SELECT 
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')

FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK 'c:\pcr\xml\48633430.XML' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
	if exists(select* from [dbo].[MICU] where PRID=' to data type int.

Open in new window

0
WillOwnerAuthor Commented:
My primary key is an int and my @prid is an int  do I have to construct the query differently with the apostrophes?
0
Pawan KumarDatabase ExpertCommented:
could you please give me data types of
@PRID , @DP , @er
0
Pawan KumarDatabase ExpertCommented:
Please use this.. Updated ..

DECLARE @FILENAME AS VARCHAR(100) = 'Pawan.txt'
DECLARE @DP AS VARCHAR(100) = 'Package'
DECLARE @PRID INT = 10
DECLARE @er AS VARCHAR(12) = 'Err'
DECLARE @SQL AS VARCHAR(MAX) = ''

set @SQL= '
SELECT 
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')

FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK ' + '''' + @FILENAME + '''' + ' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
	if exists(select* from [dbo].[MICU] where PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + ' )	
	UPDATE  [dbo].[MICU] 
	
	SET dbo.MICU.Dispatched=  ' + ''''+ @DP + '''' + ' ,dbo.MICU.Enroute=  ' + '''' + @er + '''' + ' 
	where dbo.MICU.PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + '
ELSE
 INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' 
	+ ',' + '''' + @DP + '''' + '  , ' + '''' + @er + '''' + ')'

EXEC (@SQL)

Open in new window

0
WillOwnerAuthor Commented:
Good morning.  Thanks again

@PRID , @DP , @er are all varchar(20).  The changes didn't work.  for some reason the query returns a null for @sql (have it updated a separate data table
0
Pawan KumarDatabase ExpertCommented:
can you run below and give me output of the below print statement.
Pass your actual values.

DECLARE @FILENAME AS VARCHAR(100) = 'Pawan.txt'
DECLARE @DP AS VARCHAR(100) = 'Package'
DECLARE @PRID INT = 10
DECLARE @er AS VARCHAR(12) = 'Err'
DECLARE @SQL AS VARCHAR(MAX) = ''

set @SQL= '
SELECT 
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')

FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK ' + '''' + @FILENAME + '''' + ' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
	if exists(select* from [dbo].[MICU] where PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + ' )	
	UPDATE  [dbo].[MICU] 
	
	SET dbo.MICU.Dispatched=  ' + ''''+ @DP + '''' + ' ,dbo.MICU.Enroute=  ' + '''' + @er + '''' + ' 
	where dbo.MICU.PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + '
ELSE
 INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' 
	+ ',' + '''' + @DP + '''' + '  , ' + '''' + @er + '''' + ')'

PRINT (@SQL)

Open in new window

0
WillOwnerAuthor Commented:
SELECT
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')

FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK 'Pawan.txt' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
      if exists(select* from [dbo].[MICU] where PRID= '10' )      
      UPDATE  [dbo].[MICU]
      
      SET dbo.MICU.Dispatched=  'Package' ,dbo.MICU.Enroute=  'Err'
      where dbo.MICU.PRID= '10'
ELSE
 INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( '10','Package'  , 'Err')
0
Pawan KumarDatabase ExpertCommented:
Now please execute your last statement with actual parameters and give me the errors you are getting.
0
WillOwnerAuthor Commented:
I tried a print of it and go no re 
declare @DP varchar(20)
declare @ER varchar(20)
declare @ATS varchar(20)
declare @ATP varchar(20)
declare @TODEST varchar(20)
declare @TRANSFER varchar(20)
declare @AVAIL varchar(20)
declare @DISPATCHLATLON varchar(25)
declare @INCIDENTLOC varchar(30)
DECLARE @SCENELONLAT VARCHAR(30)
declare @PRID int set  @PRID=48633430
declare @PRID_S varchar(10) set @PRID_S='48633430'
declare @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';
-- @PRID=47697063;


set @SQL= '
SELECT 
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')

FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK ' + '''' + @FILENAME + '''' + ' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
	if exists(select* from [dbo].[MICU] where PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + ' )	
	UPDATE  [dbo].[MICU] 
	
	SET dbo.MICU.Dispatched=  ' + ''''+ @DP + '''' + ' ,dbo.MICU.Enroute=  ' + '''' + @er + '''' + ' 
	where dbo.MICU.PRID= ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' + '
ELSE
 INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( ' + '''' + CAST(@PRID AS VARCHAR(200)) + '''' 
	+ ',' + '''' + @DP + '''' + '  , ' + '''' + @er + '''' + ')'

print (@SQL)

Open in new window

sponse
0
Pawan KumarDatabase ExpertCommented:
Thanks, checking.
0
WillOwnerAuthor Commented:
Thank you!
0
Pawan KumarDatabase ExpertCommented:
Update code for you .

declare @DP varchar(20)  = 'DP'
declare @ER varchar(20) = 'Enroute'
declare @ATS varchar(20) 
declare @ATP varchar(20)
declare @TODEST varchar(20)
declare @TRANSFER varchar(20)
declare @AVAIL varchar(20)
declare @DISPATCHLATLON varchar(25)
declare @INCIDENTLOC varchar(30)
DECLARE @SCENELONLAT VARCHAR(30)
declare @PRID int set  @PRID=48633430
declare @PRID_S varchar(10) set @PRID_S='48633430'
declare @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';
-- @PRID=47697063;

set @SQL= 
'SELECT 
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK ' + '''' + @FILENAME + '''' + ' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= ' + '''' + @PRID_S + '''' + ' )	
	UPDATE [dbo].[MICU]	
	SET dbo.MICU.Dispatched= ' +'''' + @DP + '''' + ',dbo.MICU.Enroute= ' + '''' + @ER + '''' + 
	' WHERE dbo.MICU.PRID = ' + '''' + @PRID_S + '''' + '
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('	+ '''' + @PRID_S + '''' + ',' + '''' + @DP + '''' + ',' + '''' + @ER + '''' + ')'
PRINT (@SQL)

Open in new window


OUTPUT I am getting..

/*------------------------
OUTPUT
------------------------*/
SELECT 
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK 'c:\pcr\xml\48633430.XML' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= '48633430' )	
	UPDATE [dbo].[MICU]	
	SET dbo.MICU.Dispatched= 'DP',dbo.MICU.Enroute= 'Enroute' WHERE dbo.MICU.PRID = '48633430'
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('48633430','DP','Enroute')

Open in new window

0
WillOwnerAuthor Commented:
Looks like my bulk import is not working.  set ER and DP to 'err' and query prints

SELECT 
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')

FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK 'c:\pcr\xml\48633430.XML' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
	if exists(select* from [dbo].[MICU] where PRID= '48633430' )	
	UPDATE  [dbo].[MICU] 
	
	SET dbo.MICU.Dispatched=  'ERR_DP' ,dbo.MICU.Enroute=  'ERR_ER' 
	where dbo.MICU.PRID= '48633430'
ELSE
 INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values( '48633430','ERR_DP'  , 'ERR_ER')

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Are you getting data from this ?
SELECT 
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM 
( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK 'c:\pcr\xml\48633430.XML' ,
     SINGLE_BLOB) AS T(y)
) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)

Open in new window

0
WillOwnerAuthor Commented:
Works!!  what was the error?
0
Pawan KumarDatabase ExpertCommented:
Great ...

We missed this ..assignment.

declare @DP varchar(20)  = ''
declare @ER varchar(20) = ''

if we use this declare @DP varchar(20) then the dynamic string does not work as it is NULL so we have set it ''.
0
WillOwnerAuthor Commented: 
Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@DP".
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.
SELECT 
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK 'c:\pcr\xml\48633430.XML' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= '48633430' )	
	UPDATE [dbo].[MICU]	
	SET dbo.MICU.Dispatched= 'DP',dbo.MICU.Enroute= 'Enroute' WHERE dbo.MICU.PRID = '48633430'
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('48633430','DP','Enroute')

(1 row(s) affected)

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Please use this complete code.

DECLARE @DP varchar(20) = ''
DECLARE @ER varchar(20) = ''
DECLARE @PRID int set  @PRID=48633430
DECLARE @PRID_S varchar(10) set @PRID_S='48633430'
DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';
SET @SQL= 
'SELECT 
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK ' + '''' + @FILENAME + '''' + ' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= ' + '''' + @PRID_S + '''' + ' )	
	UPDATE [dbo].[MICU]	
	SET dbo.MICU.Dispatched= ' +'''' + @DP + '''' + ',dbo.MICU.Enroute= ' + '''' + @ER + '''' + 
	' WHERE dbo.MICU.PRID = ' + '''' + @PRID_S + '''' + '
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('	+ '''' + @PRID_S + '''' + ',' + '''' + @DP + '''' + ',' + '''' + @ER + '''' + ')'
EXEC(@SQL)

Open in new window

0
WillOwnerAuthor Commented:
looks like its failing here:

@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
0
Pawan KumarDatabase ExpertCommented:
Please use this --complete code-

DECLARE @DP varchar(20) = ''
DECLARE @ER varchar(20) = ''
DECLARE @PRID int set  @PRID=48633430
DECLARE @PRID_S varchar(10) set @PRID_S='48633430'
DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';
SET @SQL= 
'SELECT 
@DP= Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
@ER=Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
FROM ( 
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK ' + '''' + @FILENAME + '''' + ' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= ' + '''' + @PRID_S + '''' + ' )	
	UPDATE [dbo].[MICU]	
	SET dbo.MICU.Dispatched= ' +'''' + @DP + '''' + ',dbo.MICU.Enroute= ' + '''' + @ER + '''' + 
	' WHERE dbo.MICU.PRID = ' + '''' + @PRID_S + '''' + '
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('	+ '''' + @PRID_S + '''' + ',' + '''' + @DP + '''' + ',' + '''' + @ER + '''' + ')'
EXEC(@SQL)

Open in new window

0
WillOwnerAuthor Commented:
same problem :

Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@DP".
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.
SELECT
@DP= Y.times.query('DateDispatched').value('.', 'varchar(19)'),
@ER=Y.times.query('DateEnroute').value('.', 'varchar(19)')
FROM (
SELECT CAST(y AS XML)
FROM OPENROWSET(
     BULK 'c:\pcr\xml\48633430.XML' ,
     SINGLE_BLOB) AS T(y)
     ) AS T(y)
CROSS APPLY y.nodes('/PCRDATA/Times') AS Y(Times)
if exists( select TOP 1 1 from [dbo].[MICU] where PRID= '48633430' )      
      UPDATE [dbo].[MICU]      
      SET dbo.MICU.Dispatched= '',dbo.MICU.Enroute= '' WHERE dbo.MICU.PRID = '48633430'
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('48633430','','')
0
Pawan KumarDatabase ExpertCommented:
Got the error. Checking.
0
Pawan KumarDatabase ExpertCommented:
Please use this ..

DECLARE @DP varchar(20) = ''
DECLARE @ER varchar(20) = ''
DECLARE @PRID int set  @PRID=48633430
DECLARE @PRID_S varchar(10) set @PRID_S='48633430'
DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';

IF OBJECT_ID('tempdb..#tempDATA') IS NOT NULL DROP TABLE #tempDATA
CREATE TABLE #tempDATA ( DP VARCHAR(20) , ER VARCHAR(20) )

INSERT INTO #tempDATA
EXEC ( '
	SELECT 
		  Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
		  Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
	FROM 
	( 
		SELECT CAST(y AS XML)
		FROM OPENROWSET(
		 BULK ' + '''' + @FILENAME + '''' + ' ,
		 SINGLE_BLOB) AS T(y)
	) AS T(y)
	CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times) ' 
)

SELECT TOP 1 @DP = DP , @ER = ER FROM #tempDATA

IF EXISTS( select TOP 1 1 from [dbo].[MICU] where PRID = '''' + @PRID_S + '''' )	
	UPDATE [dbo].[MICU]	
	SET dbo.MICU.Dispatched= '''' + @DP + '''' + ',dbo.MICU.Enroute= ' + '''' + @ER + ''''
	WHERE dbo.MICU.PRID =  '''' + @PRID_S + ''''
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('''' + @PRID_S + '''' + ',' + '''' + @DP + '''' + ',' + '''' + @ER + '''' )

Open in new window

0
WillOwnerAuthor Commented:
doesn't make sense:
Msg 109, Level 15, State 1, Line 36
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
0
WillOwnerAuthor Commented:
I ran the sql up to before the if "exists" and I am getting valid data to print:

(1 row(s) affected)
2017-11-26T09:37:00
2017-11-26T09:39:00
48620039
0
Pawan KumarDatabase ExpertCommented:
Sorry updated.
Please use this .

DECLARE @DP varchar(20) = ''
DECLARE @ER varchar(20) = ''
DECLARE @PRID int set  @PRID=48633430
DECLARE @PRID_S varchar(10) set @PRID_S='48633430'
DECLARE @FILENAME VARCHAR(30) SET @FILENAME='c:\pcr\xml\'+@PRID_S+'.XML'
DECLARE @SQL AS VARCHAR(MAX) = '';

IF OBJECT_ID('tempdb..#tempDATA') IS NOT NULL DROP TABLE #tempDATA
CREATE TABLE #tempDATA ( DP VARCHAR(20) , ER VARCHAR(20) )

INSERT INTO #tempDATA
EXEC ( '
	SELECT 
		  Y.times.query(''DateDispatched'').value(''.'', ''varchar(19)''),
		  Y.times.query(''DateEnroute'').value(''.'', ''varchar(19)'')
	FROM 
	( 
		SELECT CAST(y AS XML)
		FROM OPENROWSET(
		 BULK ' + '''' + @FILENAME + '''' + ' ,
		 SINGLE_BLOB) AS T(y)
	) AS T(y)
	CROSS APPLY y.nodes(''/PCRDATA/Times'') AS Y(Times) ' 
)

SELECT TOP 1 @DP = DP , @ER = ER FROM #tempDATA

IF EXISTS( select TOP 1 1 from [dbo].[MICU] where PRID = '''' + @PRID_S + '''' )	
	UPDATE [dbo].[MICU]	
	SET dbo.MICU.Dispatched= '''' + @DP + '''' , dbo.MICU.Enroute = '''' + @ER + ''''
	WHERE dbo.MICU.PRID =  '''' + @PRID_S + ''''
ELSE
INSERT INTO [dbo].[MICU] (PRID,Dispatched,Enroute) values('''' + @PRID_S + '''' , '''' + @DP + '''' , '''' + @ER + '''' )

Open in new window

0

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
WillOwnerAuthor Commented:
I fixed I removed the quotes around the @PRD_S, @DP and @ER.  Thank you so much.  Have a great day!!
0
WillOwnerAuthor Commented:
Thanks so much. Pawan!!
0
Pawan KumarDatabase ExpertCommented:
Great ! Glad to help as always:)
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
SQL

From novice to tech pro — start learning today.