Insert the xml data into table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert the xml data into table

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-10-18 : 06:00:51

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 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 =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

rams

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 07:45:26

even if you pass multiple request nodes through parameter .nodes() will be able to extract the data out of them. Populate a temporary table with extracted data and use it to join to other tables to get relevant details and doing your data processing.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-10-18 : 08:11:39

Hi Visakh,
First of all thanks for your reply, could you just reply me with the code for that as u told. Iam new to this field it is time taking for me. kindly help me on this

rams

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 08:23:41

create a table for all colums to be extracted from xml


INSERT INTO table
SELECT [Message].rows.value('AdC[1]', 'varchar(50)'),
[Message].rows.value('@time[1]', 'DATETIME'),
[Message].value('(MessageData/text())[1]', 'VARCHAR(50)')
FROM @ReturnMessage.nodes('/ReturnMessage') [Message](rows)

then use table for joins for inserts below

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources