November 20, 2013 at 2:02 pm
I am toying with the idea of passing serialised objects to a stored procedure for the purpose of data inserts. I see this as being a way to handle multiple row inserts efficiently.
However, in my limited use of XML data I am not so sure how to link the data when I have a dependency on another "object" within the serialised XML.
Below is a code snippet showing what I have so far.
The first insert statement works fine - but I really have no idea of how to retrieve the identifier created by the DB - I want to use an SQL statement that finds the record in the table based on the XML representation (of the PluginInfo), allowing me to insert the ConfigurationInfo with the correct reference to the PluginInfo
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo" ConfigurationKey="App.Customization.Notifications.Users.Void.AfterCore.PluginInfo" ConfigurationType="1" ConfigurationDescription="Registers plugin with GetUsers Void event" isEnabled="1">
<complexValue>
<PluginInfo>
<ServiceOperation>Pipeline.Users.GetUsers</ServiceOperation>
<Stage>AfterCore</Stage>
<PluginOrder>1</PluginOrder>
<PluginTypeName>Pipeline.Users.GetUsers, Pipeline.Users</PluginTypeName>
</PluginInfo>
</complexValue>
</ConfigurationInfo>
</ConfigurationDirectory>
'
EXEC sp_xml_preparedocument @Handle OUTPUT, @Config
INSERT INTO
PluginInfo
(
ServiceOperation
, Stage
, PluginOrder
, PluginTypeName
)
SELECT
ServiceOperation
, Stage
, PluginOrder
, PluginTypeName
FROM
OPENXML (@Handle, '/ConfigurationDirectory/ConfigurationInfo/complexValue/PluginInfo', 2)
WITH
(
ServiceOperation NVARCHAR(100)
, Stage NVARCHAR(50)
, PluginOrder INT
, PluginTypeName NVARCHAR(200)
)
-- INSERT ConfigurationInfo (columnlist)
SELECT
groupKey
, sectionKey
, ConfigurationKey
, ConfigurationType
, ConfigurationDescription
, IsEnabled
, PluginInfoId
FROM
OPENXML (@Handle, '/ConfigurationDirectory/ConfigurationInfo', 1)
-- About Here I need to Join to the PluginInfo Table from the associated '/ConfigurationDirectory/ConfigurationInfo/complexValue/PluginInfo' node and retrieve generated Id
WITH
(
groupKey NVARCHAR(100)
, sectionKey NVARCHAR(100)
, ConfigurationKey NVARCHAR(100)
, ConfigurationType NVARCHAR(100)
, ConfigurationDescription NVARCHAR(500)
, IsEnabled BIT
, ???
)
Hopefully you can understand what I'm trying to achieve here and offer some good advice
Thanks
November 20, 2013 at 4:14 pm
Query now looks like this and it does what I want - but it may not be the best solution it is one that will get me going forward
DECLARE @Config NVARCHAR(MAX)
DECLARE @Handle AS INT
DECLARE @TransactionCount AS INT
SELECT @Config = '
<ConfigurationDirectory >
<ConfigurationInfo groupKey="Notifications" sectionKey="App.Customization.PluginInfo" ConfigurationKey="App.Customization.Notifications.Users.Void.AfterCore.PluginInfo" ConfigurationType="1" ConfigurationDescription="Registers plugin with GetUsers Void event" isEnabled="1">
<complexValue>
<PluginInfo>
<ServiceOperation>Pipeline.Users.GetUsers</ServiceOperation>
<Stage>AfterCore</Stage>
<PluginOrder>1</PluginOrder>
<PluginTypeName>Pipeline.Users.GetUsers, Pipeline.Users</PluginTypeName>
</PluginInfo>
</complexValue>
</ConfigurationInfo>
</ConfigurationDirectory>
'
EXEC sp_xml_preparedocument @Handle OUTPUT, @Config
INSERT INTO
PluginInfo
(
ServiceOperation
, Stage
, PluginOrder
, PluginTypeName
)
SELECT
ServiceOperation
, Stage
, PluginOrder
, PluginTypeName
FROM
OPENXML (@Handle, '/ConfigurationDirectory/ConfigurationInfo/complexValue/PluginInfo', 2)
WITH
(
ServiceOperation NVARCHAR(100)
, Stage NVARCHAR(50)
, PluginOrder INT
, PluginTypeName NVARCHAR(200)
)
-- INSERT ConfigurationInfo (columnlist)
SELECT
pl.PluginInfoId
, ci.groupKey
, ci.sectionKey
, ci.ConfigurationKey
, ci.ConfigurationType
, ci.ConfigurationDescription
, ci.IsEnabled
FROM
OPENXML (@Handle, '/ConfigurationDirectory/ConfigurationInfo/complexValue/PluginInfo', 2)
WITH
(
groupKey NVARCHAR(100) '../../@groupKey'
, sectionKey NVARCHAR(100) '../../@sectionKey'
, ConfigurationKey NVARCHAR(100) '../../@ConfigurationKey'
, ConfigurationType NVARCHAR(100) '../../@ConfigurationType'
, ConfigurationDescription NVARCHAR(500) '../../@ConfigurationDescription'
, IsEnabled BIT '../../@IsEnabled'
, ServiceOperation NVARCHAR(100)
, Stage NVARCHAR(50)
, PluginOrder INT
, PluginTypeName NVARCHAR(200)
) AS ci
INNER JOIN
OPENXML (@Handle, '/ConfigurationDirectory/ConfigurationInfo/complexValue/PluginInfo', 2)
WITH
(
groupKey NVARCHAR(100) '../../@groupKey'
, sectionKey NVARCHAR(100) '../../@sectionKey'
, ConfigurationKey NVARCHAR(100) '../../@ConfigurationKey'
, ConfigurationType NVARCHAR(100) '../../@ConfigurationType'
, ConfigurationDescription NVARCHAR(500) '../../@ConfigurationDescription'
, IsEnabled BIT '../../@IsEnabled'
, ServiceOperation NVARCHAR(100)
, Stage NVARCHAR(50)
, PluginOrder INT
, PluginTypeName NVARCHAR(200)
) AS po
ON
po.ServiceOperation = ci.ServiceOperation
AND
po.Stage = ci.Stage
AND
po.PluginOrder = ci.PluginOrder
AND
po.PluginTypeName = ci.PluginTypeName
INNER JOIN
dbo.PluginInfo pl
ON
pl.ServiceOperation = po.ServiceOperation
AND
pl.Stage = po.Stage
AND
pl.PluginOrder = po.PluginOrder
AND
pl.PluginTypeName = po.PluginTypeName
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply