January 15, 2009 at 3:16 am
Hi,
I have some data which comes in as name/value pairs within some xml. I use this information to link to Client records. However, as some of the information is duplicated, I have created a table to map client records to the name/value pairs. For example there is a name/value pair which is "Status", "Stopped" which can be used by more than one attribute group.
I want to capture the changes to the attributes, and if someting changes create a new Client record, with the new mappings and consign the old Client record to a history table. This is where I am getting lost.
The tables are:
CREATE Table MyAttribute
(MyAttributeID INTEGER IDENTITY(1,1)
, aName NVARCHAR(4000)
,aValue NTEXT
,iHash INTEGER)
CREATE TABLE ClientAud
(ClientAudID INTEGER IDENTITy(1,1)
,Client INTEGER
,Discoverd Datetime
,ClientName NVARCHAR(4000)
,iHash INTEGER)
CREATE TABLE ClientAudAttributeMap
(ClientAudID INTEGER
,MyAttributeID INTEGER)
CREATE TABLE CLientAudHistory
(CientAudID INTEGER
,Client INTEGER
,Discovered Datetime
,Removed Datetime
,ClientName NVARCHAR(4000)
,iHash INTEGER)
I am "shredding" the XML into a temp table, and then adding the new attributes to the ClientAud table. Then I need to add the ClientAudID and MyAttributes to the mapping table.
So what I think need to do:
Check to see if Attribute exists, and add if not.
Add client record, and then populate
mapping table with AttributeIDs and Client ID.
If attributeID does exist, what is the best way to detect if there are changes, and create a new record mapping to an existing AttributeID (for example status has changed from "Stopped" to "Started" and that Attribute record exists already, or add a new attribute if it does not exist (eg "Status " = "Suspended" where that name/valuepair has not appeared in table before?
I don't know if I have explained this very well.
Here is a fragment of the XML, although the XML may contain hundreds of these with varying attributes, as they are user configurable (hence the need for name/value pairs:
As I said, I am just unsure of how to capture any deltas! thanks in advance for any pointers you can give, but please let me know if I can provide any more info.
January 21, 2009 at 2:54 am
Hi all,
I didn't notice that my XML didnt show up. Maybe that is why I got no replies, or perhaps I wasn't very clear.
Code to add new stuff goes like this:
CREATE TABLE #IDsList
(
ID INTEGER not null
)
--Add any new Attributes
BEGIN
INSERT INTO MyAttribute (aName, aValue, iHash)
Output INSERTED MyAttributeID Into #IDsList
SELECT atName ,atValue, BINARY_CHECKSUM(atName, atValue)
FROM #Attrib a
LEFT JOIN MyAttribute at
on aName = atName AND CAST( aValue AS nvarchar(4000)) = CAST( atValue AS nvarchar(4000)) AND IndHash = BINARY_CHECKSUM(atName, atValue)
where at.aName IS NULL
END
--Add a list of the Attributes which already exist
INSERT INTO #IDsList
SELECT MyAttributeID FROM MyAttribute at
INNER JOIN #Attrib a
on aName = atName AND CAST( aValue AS nvarchar(4000)) = CAST( atValue AS nvarchar(4000)) AND IndHash = BINARY_CHECKSUM(atName, atValue)
--Debug only**
SELECT * from #IDsList
--***********
DECLARE @ClientAud Integer
-- Find any matching record from a previous Audit
Select @vcValue = atValue from #Attrib where atName like 'ClientDesc'
select @vcValue
SELECT @ClientAud = A.ClientAudID FROM ClientAud A
WHERE A.Client = 3 --@Client
AND A.Client = @vcValue
--If @Aud is null then it is a new record
----EXECUTE dbo.SA_ClientAud_Add @vcValue
,@iClient
--else IF @ClientAud is not null
--Compare new attribute list with previous attribute list
Select count(*)
from MyAttribute AT
inner join ClientAudAttributeMap M
on AT.MyAttributeID = M.MyAttributeID
right join #Attrib A
on aName = atName AND CAST( aValue AS nvarchar(4000)) = CAST( atValue AS nvarchar(4000)) AND IndHash = BINARY_CHECKSUM(atName, atValue)
where aValue is null and M.ClientAudID = @CientAud
--IF count = > 0 something has changed, so remove and add new Audit record with new mappings
--BEGIN
---EXECUTE dbo.ClientAud_Add @vcValue
,@iClient
--EXECUTE dbo.ClientAud_Remove @vcValue
,@iClient
January 22, 2009 at 10:53 am
Can you try and clarify your goal? You don't have any XML, and based on what you are doing it is hard to tell both the end result and the reason for that result.
For example, if you are only trying to parse attributes into a name-value pair related to a parent object, why can't you clear all attributes for that parent and reinsert? Even if you create a table of unique attributes to replace the attribute name with a key, clearing and reinserting for the parent id would work. Or, will the XML not contain all attributes that you are storing, only a recent round of updated or inserted attributes? Another way of asking is if you have an attribute of color come across for parent object "My Car", and then tomorrow you get another parent node of "My Car' that does not have a color attribute, would you want to get rid of color or keep it?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply