I have an app that performs scans and returns information
like what windows updates it has, services running, programs installed,
browsesr history, etc. Scans will be performed once a week and sent to
a server. The server will only save the most recent scan and store the
rest in a history database. I have the methods for inserting and they
work fine. However I am stuck with the task of getting this to work
after a scan from a PC is already stored. The procedure will have to
check the AssetName from tblAsset and compare it to the equivalent in
my XML input. It will get the associated ScanID and use that to make
updates in tblScan and tblScanDetail.
ALTER PROCEDURE csTest.StoredProcedure1 (@doc NTEXT)

AS
declare @iTree int
declare @assetid int
declare @scanid int
create table #temp (ID nvarchar(50), ParentID nvarchar(50), Name
nvarchar(50), scanattribute nvarchar(50))
create table #dup (attid nvarchar(50), name nvarchar (50), ID
nvarchar(50))

/* SET NOCOUNT ON */

EXEC sp_xml_preparedocument @iTree OUTPUT, @doc

INSERT INTO tblAsset (AssetName, DatelastScanned, LastModified)
SELECT *, LastModified = getdate() FROM openxml(@iTree,
'ComputerScan', 1)
WITH (
ComputerName nvarchar(30) 'computer/ComputerName',
DatelastScanned smalldatetime 'scanheader/ScanDate'
)

set @assetid = scope_identity()

INSERT INTO tblScan (AssetName, ScanDate, AssetID, LastModified)
SELECT *, @assetid, LastModified = getdate() FROM openxml(@iTree,
'ComputerScan', 1)
WITH (
ComputerName nvarchar(30) 'computer/ComputerName',
ScanDate smalldatetime 'scanheader/ScanDate'
)

SET @scanid = scope_identity()

INSERT INTO #temp
SELECT * FROM openxml(@iTree,
'ComputerScan/scans/scan/scanattributes/scanattribute', 1)
WITH(
ID nvarchar(50) './@ID',
ParentID nvarchar(50) './@ParentID',
Name nvarchar(50) './@Name',
scanattribute nvarchar(50) '.'
)

INSERT INTO #dup
SELECT ScanAttributeID, #temp.scanattribute, #temp.ID FROM
tblScanAttribute, #temp
WHERE tblScanAttribute.Name = #temp.Name

INSERT INTO tblScanDetail(Instance, ScanAttributeID, ScanID,
AttributeValue, LastModified)
SELECT instance = (select count(*) from #dup where #dup.attid =
tblScanAttribute.ScanAttributeID AND ((#dup.name<#temp.scanattribute)
or (#dup.name=#temp.scanattribute) and
(#dup.ID<=#temp.ID))),
tblScanAttribute.ScanAttributeID, tblScan.ScanID, #temp.scanattribute,
getdate()
FROM tblScanAttribute, #temp, tblScan
WHERE tblScanAttribute.Name = #temp.Name
ORDER BY tblScan.ScanID

drop table #temp
drop table #dup

EXEC sp_xml_removedocument @iTree

RETURN