October 18, 2011 at 4:02 am
Hi All,
Currently Iam using the following SP for splitting the below xml based on returnmessageid and insert into the table one by one based on returnmessageid, but now i want to insert the data into the table at once even for multiple returnmessageid node in xml one by one. Please do find my SP below and guide me on the same:
@ReturnMessage = '<MessageDelivery version="B000">
<ReturnMessage id="3652789340">
<AdC ocean="PACCGL">4815044</AdC>
<MessageStatus code="100" time="2011-09-25 13:43:22">status ok</MessageStatus>
<MessageData>700A20000018C0375494400000</MessageData>
<Flags les="0" app="0" read="1" /> </ReturnMessage>
<ReturnMessage id="3653174486">
<AdC ocean="PACCGL">4815045</AdC>
<MessageStatus code="100" time="2011-09-25 15:45:57">status ok</MessageStatus>
<MessageData>600A20000012B036B08B700000</MessageData>
<Flags les="0" app="0" read="1" />
</ReturnMessage> <ReturnMessage id="3653400028">
<AdC ocean="PACCGL">4815041</AdC>
<MessageStatus code="100" time="2011-09-25 16:57:55">status ok</MessageStatus>
<MessageData>700A2000001AF03AE04A000000</MessageData>
<Flags les="0" app="0" read="1" /> </ReturnMessage>
<ReturnMessage id="3654190154">
<AdC ocean="PACCGL">4815046</AdC>
<MessageStatus code="100" time="2011-09-25 20:57:17">status ok</MessageStatus>
<MessageData>600A200000186036B08B400000</MessageData>
<Flags les="0" app="0" read="1" />
</ReturnMessage>
</MessageDelivery>'
CREATE PROCEDURE [dbo].[usp_SW_ReturnMessages_Update]
@ReturnMessage XML,
@ReturnMessageID VARCHAR(15)
AS
BEGIN
BEGIN TRY
DECLARE @BatteryVoltage FLOAT
DECLARE @HexaTypeField VARCHAR(20)
DECLARE @TypeField VARCHAR(20)
DECLARE @HexaMeterReading VARCHAR(20)
DECLARE @MeterReading INT
DECLARE @HexaMeterID VARCHAR(20)
DECLARE @DataPacket VARCHAR(30)
DECLARE @MeterIDDecimal INT
DECLARE @BatteryRead INT
DECLARE @TerminalID VARCHAR(20)
DECLARE @MessageStatusTime DATETIME
DECLARE @CommErrorCode VARCHAR(10)
DECLARE @MeterIdErrorCode VARCHAR(50)
DECLARE @ErrorTypeID VARCHAR(10)
DECLARE @CustID INT
DECLARE @ModID INT
DECLARE @MeterID INT
DECLARE @LocID INT
DECLARE @getdate-2 DATETIME
UPDATE SW_ReturnMessages
SET ReturnMessage=@ReturnMessage ,Status =2
WHERE ReturnMessageID=@ReturnMessageID
SELECT @TerminalID = [Message].rows.value('AdC[1]', 'varchar(50)')
FROM @ReturnMessage.nodes('/ReturnMessage') [Message](rows)
SELECT @MessageStatusTime = [Message].rows.value('@time[1]', 'DATETIME')
FROM @ReturnMessage.nodes('/ReturnMessage/MessageStatus') [Message](rows)
SELECT @DataPacket = @ReturnMessage.value('(/ReturnMessage/MessageData/text())[1]', 'VARCHAR(50)')
SET @HexaTypeField = SUBSTRING(@DataPacket, 2, 2)
SET @HexaMeterID = SUBSTRING(@DataPacket, 14, 8)
SET @HexaMeterReading = SUBSTRING(@DataPacket, 6, 8)
SELECT@getdate-2 =GETDATE(),
@BatteryRead = SUBSTRING(@DataPacket, 1, 1),
@BatteryVoltage = (((4*@BatteryRead)/10) + 8),
@TypeField = (SELECT dbo.udf_HextoBinary(@HexaTypeField)),
@MeterReading = (SELECT dbo.udf_Convert_HextoDecimal(@HexaMeterReading)),
@MeterIdErrorCode= SUBSTRING (@HexaMeterID,1,3),
@MeterIDDecimal = (SELECT dbo.udf_Convert_HextoDecimal(@HexaMeterID))
SELECT @CommErrorCode = SUBSTRING(@TypeField,7,2),
@ErrorTypeID = SUBSTRING(@TypeField,1,1) -- 0: No Error, 1: Type Filed / Communication Error
SELECT @ModID = ModID
FROM Modems
WHERE TerminalID = @TerminalID
SELECT @CustID=CustID, @LocID=Locid,@MeterID = MetID
FROM Meters
WHERE MeterNumberOTA = @MeterIDDecimal
SELECT COUNT(WaitingMessagesID) AS TotalCount
FROM SW_ReturnMessages
WHERE Status=1
IF NOT Exists(SELECT 1 FROM ModemsHistoryBatteryVoltage
WHERE MeterID = @MeterID AND BatteryVoltageRawBase10 = @BatteryVoltage)
BEGIN
INSERT INTO ModemsHistoryBatteryVoltage(
ReadDateTime,ModID,MeterID,BatteryVoltageRawBase10,BatteryVoltageActual)
VALUES (@GetDate,@ModID,@MeterID,@BatteryVoltage,@BatteryRead)
END
IF NOT EXISTS(
SELECT MetID,ModID,ReceivedDateTime
FROM ModemsReadingsDaily
WHERE MetID=@MeterID AND ModID=@ModID AND ReceivedDateTime=@MessageStatusTime
)
BEGIN
INSERT INTO ModemsReadingsDaily(
ReadDateTime,ModID,MetID,TerminalID,CustID,
Reading,ReceivedDateTime,Usage,RawData,LocID,ErrorFlag)
VALUES
(@GetDate,@ModID,@MeterID,@TerminalID,@CustID,
@MeterReading,@MessageStatusTime,NULL,
@DataPacket,@LocID,@ErrorTypeID)
END
ELSE
BEGIN
UPDATE ModemsReadingsDaily
SET Reading=@MeterReading,ReadDateTime =@GetDate,
RawData=@DataPacket,LocID=@LocID,ErrorFlag=@ErrorTypeID
WHERE MetID=@MeterID AND ModID=@ModID AND ReceivedDateTime =@MessageStatusTime
END
IF NOT(@ErrorTypeID = 0)
INSERT INTO ModemsReadingsDailyErrors (ReadDateTime ,ModID ,MeterID ,ErrorTypeID ,ErrorValue )
VALUES (@GetDate,@ModID,@MeterID,@ErrorTypeID,@CommErrorCode)
EXEC usp_SW_ReturnMessages_Get
END TRY
BEGIN CATCH
EXEC usp_RaiseError
END CATCH
END
October 18, 2011 at 4:48 am
Dup post
Please post answers here.
http://www.sqlservercentral.com/Forums/Topic1191847-21-1.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply