--USE [TestDB]
--GO
/****** Object: StoredProcedure [dbo].[PutAndGetSQLBeeValues] Script Date: 12/09/2005 08:50:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if (not exists(
select SS.Name, SO.Name from Sys.objects SO
INNER JOIN Sys.schemas SS ON SS.schema_id = SO.schema_id
where SS.Name = 'dbo' and SO.Name = 'PutAndGetSQLBeeValues' and SO.type = 'P'))
BEGIN
exec ('CREATE PROCEDURE dbo.PutAndGetSQLBeeValues AS')
END
GO
-- =============================================
-- Create date: 30/5-2009
-- Description: Sample SP for reading and writing data to/from SQL server using Apis SQL Bee
-- =============================================
ALTER PROCEDURE [dbo].[PutAndGetSQLBeeValues]
-- Add the parameters for the function here
@ByRows bit = 1,
@ModuleName nvarchar(max) = '',
@TriggerName nvarchar(max) = '',
@xmlWriteData xml = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;
------------------------------------------------------------------
-- First handle WRITE operations:
-- GENERIC CODE:
-- The Format of the XML Document give as input param @xmlWriteData:
-- <?xml version="1.0" encoding="utf-16"?>
-- <ROOT>
-- <ItemSample ItemID="SQLBee1.MyWriteItem1" ItemValue="1.1" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" />
-- <ItemSample ItemID="SQLBee1.MyWriteItem2" ItemValue="3.14" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" />
-- <ItemSample ItemID="SQLBee1.MyWriteItem3" ItemValue="6.28" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" />
-- :
-- <ItemSample ItemID="SQLBee1.MyWriteVector1" ItemValue="1.1" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" Row="0"/>
-- <ItemSample ItemID="SQLBee1.MyWriteVector1" ItemValue="2.1" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" Row="1"/>
-- <ItemSample ItemID="SQLBee1.MyWriteVector1" ItemValue="3.1" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" Row="2"/>
-- <ItemSample ItemID="SQLBee1.MyWriteVector1" ItemValue="4.1" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" Row="3"/>
-- :
-- <ItemSample ItemID="SQLBee1.MyWriteMatrix1" ItemValue="1.1" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" Row="0" Col="0"/>
-- <ItemSample ItemID="SQLBee1.MyWriteMatrix1" ItemValue="2.1" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" Row="1" Col="0"/>
-- <ItemSample ItemID="SQLBee1.MyWriteMatrix1" ItemValue="1.2" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" Row="0" Col="1"/>
-- <ItemSample ItemID="SQLBee1.MyWriteMatrix1" ItemValue="2.2" ItemTimestamp="2009-05-29 12:00:00.000" ItemQuality="192" Row="1" Col="1"/>
-- </ROOT>
--print convert(nvarchar(max), @xmlWriteData)
Declare @WriteDataTable TABLE
(
ItemID nvarchar(max),
ItemValue nvarchar(max),
--ItemType smallint,
ItemTimestamp datetime,
ItemQuality smallint,
Row smallint,
Col smallint
)
DECLARE @hXmlDoc int
Exec sp_xml_preparedocument @hXmlDoc Output, @xmlWriteData
if (@hXmlDoc is not null)
begin
INSERT INTO @WriteDataTable(ItemID, ItemValue, ItemTimestamp, ItemQuality, Row, Col)
SELECT ItemID, ItemValue, ItemTimestamp, ItemQuality, Row, Col
FROM
OPENXML(@hXmlDoc, '/ROOT/ItemSample',1)
WITH (ItemID nvarchar(max), ItemValue nvarchar(max), ItemTimestamp datetime, ItemQuality smallint, Row smallint, Col smallint)
end
Exec sp_xml_removedocument @hXmlDoc
-- Then use contents of @WriteDataTable to insert into target system
-- END OF GENERIC CODE:
-- CUSTOMER SPECIFIC CODE!
-- Sample 1: Insert into target table:
--insert into WriteDataTarget
--select * from @WriteDataTable
-- Sample 2: Call a customer specific stored procedure
--Exec CustomerStoredProc
-- convert( real, select ItemValue from @WriteDataTable where ItemID = 'Tagnavn 1'),
-- convert( real, select ItemValue from @WriteDataTable where ItemID = 'Tagnavn 2'),
-- convert( real, select ItemValue from @WriteDataTable where ItemID = 'Tagnavn 3'),
-- convert( real, select ItemValue from @WriteDataTable where ItemID = 'Tagnavn 4')
------------------------------------------------------------------
-- Then handle READ operations:
-- Insert statements for procedure here
-- Below is sample code for generating a dummy namespace and dummy values:
Declare @TS datetime
Set @TS = GetUtcDate()
if @ByRows = 1
begin
Declare @RandomTable TABLE
(
ItemName varchar(64),
ItemValue varchar(128),
ItemTimestamp datetime
)
insert into @RandomTable select 'BoolValue', cast(round(Rand(), 0) as varchar(128)), @TS
insert into @RandomTable select 'FloatValue', cast(10*RAND() as varchar(128)), @TS
insert into @RandomTable select 'StringValue', 'StringValue X' +cast(round(100*RAND(), 0) as varchar(128)) + 'XEV', @TS
insert into @RandomTable select 'DateValue', cast(getDate() as varchar(128)), @TS
insert into @RandomTable select 'BoolValue2', cast(round(Rand(), 0) as varchar(128)), @TS
insert into @RandomTable select 'FloatValue2', cast(10*RAND() as varchar(128)), @TS
insert into @RandomTable select 'StringValue2', 'StringValue2 X' +cast(round(100*RAND(), 0) as varchar(128)) + 'XEV', @TS
insert into @RandomTable select 'DateValue2', cast(getDate() as varchar(128)), @TS
insert into @RandomTable select 'BoolValue3', cast(round(Rand(), 0) as varchar(128)), @TS
insert into @RandomTable select 'FloatValue3', cast(10*RAND() as varchar(128)), @TS
insert into @RandomTable select 'StringValue3', 'StringValue3 X' +cast(round(100*RAND(), 0) as varchar(128)) + 'XEV', @TS
insert into @RandomTable select 'DateValue3', cast(getDate() as varchar(128)), @TS
insert into @RandomTable select 'BoolValue4', cast(round(Rand(), 0) as varchar(128)), @TS
insert into @RandomTable select 'FloatValue4', cast(10*RAND() as varchar(128)), @TS
insert into @RandomTable select 'StringValue4', 'StringValue4 X' +cast(round(100*RAND(), 0) as varchar(128)) + 'XEV', @TS
insert into @RandomTable select 'DateValue4', cast(getDate() as varchar(128)), @TS
insert into @RandomTable select 'BoolValue5', cast(round(Rand(), 0) as varchar(128)), @TS
insert into @RandomTable select 'FloatValue5', cast(10*RAND() as varchar(128)), @TS
insert into @RandomTable select 'StringValue5', 'StringValue5 X' +cast(round(100*RAND(), 0) as varchar(128)) + 'XEV', @TS
insert into @RandomTable select 'DateValue5', cast(getDate() as varchar(128)), @TS
insert into @RandomTable select 'StringArray', '[2] elem1-' +cast(round(100*RAND(), 0) as varchar(128)) + '; elem2-' + cast(round(100*RAND(), 0) as varchar(128)) + ';', @TS
insert into @RandomTable select 'DoubleArray', '[2] ' + cast(100*RAND() as varchar(128)) + '; ' + cast(100*RAND() as varchar(128)) + ';', @TS
select * from @RandomTable
end
else
begin
Select
cast(round(Rand(), 0) as bit) as 'ColBoolValue',
10*RAND() as 'ColFloatValue',
'column X' +cast(round(100*RAND(), 0) as varchar(128)) + 'XEV' as 'ColStringValue',
getDate() as 'ColDateValue',
cast(round(Rand(), 0) as bit) as 'ColBoolValue2',
10*RAND() as 'ColFloatValue2',
'column X' +cast(round(100*RAND(), 0) as varchar(128)) + 'XEV' as 'ColStringValue2',
getDate() as 'ColDateValue2',
cast(round(Rand(), 0) as bit) as 'ColBoolValue3',
10*RAND() as 'ColFloatValue3',
'column X' +cast(round(100*RAND(), 0) as varchar(128)) + 'XEV' as 'ColStringValue3',
getDate() as 'ColDateValue3',
cast(round(Rand(), 0) as bit) as 'ColBoolValue4',
10*RAND() as 'ColFloatValue4',
'column X' +cast(round(100*RAND(), 0) as varchar(128)) + 'XEV' as 'ColStringValue4',
getDate() as 'ColDateValue4',
cast(round(Rand(), 0) as bit) as 'ColBoolValue5',
10*RAND() as 'ColFloatValue5',
'column X' +cast(round(100*RAND(), 0) as varchar(128)) + 'XEV' as 'ColStringValue5',
getDate() as 'ColDateValue5',
'[2] tkst-' +cast(round(100*RAND(), 0) as varchar(128)) + '; tkst-' + cast(round(100*RAND(), 0) as varchar(128)) + ';' as 'StringArray',
'[2] ' + cast(100*RAND() as varchar(128)) + '; ' + cast(100*RAND() as varchar(128)) + ';' as 'DoubleArray'
end
END