> Using MS SQL 2000
>
> I have a stored procedure that processes an  XML file generated from an
> Audit program.  The XML looks somewhat like this:
>
> <ComputerScan>
>  <scanheader>
>   <ScanDate>somedate&time</ScanDate>
>   <UniqueID>MAC address</UniqueID>
>  </scanheader>
>  <computer>
>   <ComputerName>RyanPC</ComputerName>
>  </computer>
>  <scans>
>   <scan ID = "1.0" Section= "Basic Overview">
>    <scanattributes>
>      <scanattribute ID="1.0.0.0" ParentID=""
> Name="NetworkDomian">MSHOMe</scanattribute>
> scanattribute ID = "1.0.0.0.0" ParentID="1.0.0.0", etc etc....
>
> This is the Update portion of the sproc....
>
>
> CREATE PROCEDURE csTest.StoredProcedure1 (@doc ntext)
>
> AS
> DECLARE @iTree int
> DECLARE @assetid int
> DECLARE @scanid int
> DECLARE @MAC nvarchar(50)
> CREATE TABLE #temp (ID nvarchar(50), ParentID nvarchar(50), Name
> nvarchar(50), scanattribute nvarchar(50))
>
>     /* SET NOCOUNT ON */
>
> EXEC sp_xml_preparedocument @iTree OUTPUT, @doc
>
> 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) '.'
>             )
>
> SET @MAC = (select UniqueID from openxml(@iTree, 'ComputerScan',
> 1)with(UniqueID nvarchar(30) 'scanheader/UniqueID'))
>
> IF EXISTS(select MAC from tblAsset where MAC = @MAC)
>     BEGIN
>     UPDATE tblAsset set DatelastScanned = (select ScanDate from
> openxml(@iTree, 'ComputerScan', 1)with(ScanDate smalldatetime
> 'scanheader/ScanDate')),
>                     LastModified = getdate() where MAC = @MAC
>
>     UPDATE tblScan set ScanDate = (select ScanDate from openxml(@iTree,
> 'ComputerScan', 1)with(ScanDate smalldatetime 'scanheader/ScanDate')),
>                     LastModified = getdate() where MAC = @MAC
>
>     UPDATE tblScanDetail set GUIID = #temp.ID, GUIParentID =
> #temp.ParentID, AttributeValue = #temp.scanattribute, LastModified =
> getdate()
>     FROM tblScanDetail INNER JOIN #temp
>         ON (tblScanDetail.GUIID = #temp.ID AND tblScanDetail.GUIParentID =
> #temp.ParentID AND tblScanDetail.AttributeValue = #temp.scanattribute)
>     WHERE MAC = @MAC
>
> !!!!!!!!!!!!!!!!!! THIS IS WHERE IT SCREWS UP, THIS NEXT INSERT
> STATEMENT IS SUPPOSE TO HANDLE attributes THAT WERE NOT IN THE PREVIOUS
> SCAN SO CAN NOT BE UDPATED BECAUSE THEY DON'T EXIST
> YET!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
>
>     INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,
> ScanID, AttributeValue, DateCreated, LastModified)
>     SELECT @MAC, b.ID, b.ParentID, tblScanAttribute.ScanAttributeID,
> @scanid, b.scanattribute, DateCreated = getdate(), LastModified =
> getdate()
>     FROM tblScanDetail LEFT OUTER JOIN #temp a ON (tblScanDetail.GUIID =
> a.ID AND tblScanDetail.GUIParentID = a.ParentID AND
> tblScanDetail.AttributeValue = a.scanattribute), tblScanAttribute JOIN
> #temp b ON tblScanAttribute.Name = b.Name
>     WHERE (tblScanDetail.GUIID IS NULL AND tblScanDetail.GUIParentID IS
> NULL AND tblScanDetail.AttributeValue IS NULL)
>     END
> ELSE
>     BEGIN
>
> Here are a few table defintions to maybe help out a little too...
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblAsset]
> GO
>
> CREATE TABLE [dbo].[tblAsset] (
>     [AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
>     [AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
>     [AssetTypeID] [int] NULL ,
>     [MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>     [DatelastScanned] [smalldatetime] NULL ,
>     [NextScanDate] [smalldatetime] NULL ,
>     [DateCreated] [smalldatetime] NULL ,
>     [LastModified] [smalldatetime] NULL ,
>     [Deleted] [bit] NULL
> ) ON [PRIMARY]
> GO
> -----------------------------
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblScan]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblScan]
> GO
>
> CREATE TABLE [dbo].[tblScan] (
>     [ScanID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
>     [AssetID] [int] NULL ,
>     [ScanDate] [smalldatetime] NULL ,
>     [AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
>     [MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>     [DateCreated] [smalldatetime] NULL ,
>     [LastModified] [smalldatetime] NULL ,
>     [Deleted] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
> ----------------------------
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblScanDetail]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblScanDetail]
> GO
>
> CREATE TABLE [dbo].[tblScanDetail] (
>     [ScanDetailID] [int] IDENTITY (1, 1) NOT FOR REPLICATION  NOT NULL ,
>     [ScanID] [int] NULL ,
>     [ScanAttributeID] [int] NULL ,
>     [MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>     [GUIID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
>     [GUIParentID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
>     [AttributeValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
>     [DateCreated] [smalldatetime] NULL ,
>     [LastModified] [smalldatetime] NULL ,
>     [Deleted] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ------------------------------------------------------------
> My problem is that Insert statement that follows the update into
> tblScanDetail, for some reason it just seems to insert everything twice
> if the update is performed.  Not sure what I did wrong but any help
> would be appreciated.  Thanks in advance.