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)
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.
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.
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)
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)
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)
sponse
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)
/*------------------------
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')
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')
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)
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)
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)
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)
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