Complicated Inserts

Hello everyone, im looking for some advice or maybe examples.

How is the best method of checking a table for a specific IP address located as an element value of a xml data type.

For example

xmlNode = Table in use

myXmlData = Document being stored as XML datatype

I would like to first check the table of existing XML entrys for an ip address that is within the current xmldata in the reader.

If the ip address in the reader matches any of the ones in the database it will update that row.

If the ip address can not be found in any existing row, a new row is created and myXmlData is stored.

At the moment data is stored regardless of existing entrys.

myQuery = "INSERT INTO T4(xmlNode) VALUES(@.myXMLData)"

If only things were simple eh !

INSERT INTO T4(xmlNode) VALUES(@.myXMLData) WHERE xmlNode.Childvalue = (?)

I know the above is totally wrong and syntaticlly rubbish but its along that idea.

Excause my SQL ignorance but im a bit of a newbie.

Examples are few to none, and few do not do anything like what i need.
Is it even possible ?

Regards

Russ

Since you want the logic "insert if the IP does not exist", your statement will go as follows:

IF EXISTS (SELECT * FROM T4 WHERE xmlNode.exist ('//ip[. = sql:variable(@.searchIPValue)]') = 1)
UPDATE T4 SET xmlNode = @.myXmlData
WHERE xmlNode.exist ('//ip[. = sql:variable(@.searchIPValue)]') = 1
ELSE
INSERT INTO T4 (xmlNode) VALUES (@.myXmlData)

Note that table name = T4 and XML column name = xmlNode (unlike what you have mentioned above).

Since the lookup on IP seems to be common in your workload and only a single IP occurs in each XML instance, you may want to promote it into a column to simplify the statment above. See the MSDN whitepaper for http://msdn.microsoft.com/xml/default.aspx?pull=/library/en-us/dnsql90/html/sql25xmlbp.asp for more information