October 18, 2018 at 1:43 pm
So after figuring out (with all of your help, of course) how to filter data based on arbitrary number of xml node values supplied, now I have to provide a way for client applications to patch xml data with arbitrary number of xml node values. The following SQL works for a single xml node.
IF EXISTS (select 1 FROM dbo.Test WHERE something = 'sixth' AND xmlData.exist('/Data/e1') = 1)
BEGIN
PRINT 'Does Exist - will be replaced'
UPDATE dbo.Test SET xmlData.modify('replace value of (/Data/e1[1]/text())[1] with "Modified"') WHERE something = 'sixth'
END
ELSE
BEGIN
PRINT 'Does Not Exist - will be inserted'
UPDATE dbo.Test SET xmlData.modify('insert <e1>New</e1> into (/Data)[1]') WHERE something = 'sixth'
END
Once gain, here's the sample data I use
CREATE TABLE dbo.Test
(
id INT IDENTITY PRIMARY KEY,
something VARCHAR (1024),
xmlData XML
);
INSERT INTO dbo.Test
SELECT something = 'first', xmlData = '<Data><e1>404</e1><e2>Dont Care</e2><e3>Yomama</e3></Data>'
INSERT INTO dbo.Test
SELECT something = 'second', xmlData = '<Data><e1>500</e1><e2>Whoop Dee Do</e2><e3>Yikes</e3></Data>'
INSERT INTO dbo.Test
SELECT something = 'third', xmlData = '<Data><e1>200</e1><e2>Nice</e2></Data>'
INSERT INTO dbo.Test
SELECT something = 'fourth', xmlData = '<Data><e1>404</e1><e2>Ho hum</e2><e3>Yopapa</e3></Data>'
INSERT INTO dbo.Test
SELECT something = 'fifth', xmlData = '<Data><e1>200</e1><e2>Nice</e2><e3>Fantastic</e3></Data>'
INSERT INTO dbo.Test
SELECT something = 'sixth', xmlData = '<Data><e2>Whoop Dee Do</e2><e3>Whoa!</e3></Data>'
So now I want to pass in multiple xml node element values which may or may not exist on the record I'm updating. I'm trying to do something like the following but having trouble
DECLARE @patch TABLE (
name VARCHAR (64),
val VARCHAR (64)
)
INSERT @patch SELECT name = 'e1', val = 'New' -- add to the sixth record
INSERT @patch SELECT name = 'e2', val = 'Whoop Dee Da!' -- update on the sixth record
DECLARE @record VARCHAR(64) = 'sixth'
MERGE dbo.Test WITH (SERIALIZABLE) AS T
USING @patch P ON T.something = @record AND T.xmlData.exist('...') = 1
WHEN MATCHED THEN
UPDATE T SET xmlData.modify('replace value of ... with ...') WHERE something = @record
WHEN NOT MATCHED THEN
UPDATE T SET xmlData.modify('insert ... into (/Data)[1]') WHERE something = @record
I've used this pattern on simple column updates but now I need to make it work on elements of the XML column. I have to believe there's a way or I will have to batch the replace/inserts using modify for each element (yuck!). Looking for any assistance to pull this off. Assuming I'm on the right track, need to fill in the blanks for all instances of the the 3 dots in my example above.
October 19, 2018 at 6:26 pm
Until I can get my MERGE statement working if ever 🙁, I have the below tacky loop but it seems to work
DECLARE @patch TABLE (
id INT,
name VARCHAR (64),
val VARCHAR (64)
)
INSERT @patch SELECT id = 1, name = 'e1', val = 'Newer' -- add to the sixth record
INSERT @patch SELECT id = 2, name = 'e2', val = 'Whoop Dee Da!' -- update on the sixth record
DECLARE @record VARCHAR(64)
SET @record = 'sixth'
DECLARE @counter INT = 1
DECLARE @total INT
SELECT @total = COUNT(*) FROM @patch
PRINT 'total --> ' + CONVERT(VARCHAR(64), @total)
DECLARE @name VARCHAR(64), @val VARCHAR(64)
DECLARE @element XML
WHILE (@counter <= @total)
BEGIN
SELECT @name = name, @val = val FROM @patch WHERE id = @counter
IF EXISTS (select 1 FROM dbo.Test WHERE something = @record AND xmlData.exist('/Data/*[local-name() = sql:variable("@name")]') = 1)
BEGIN
PRINT 'Does Exist - will be replaced'
UPDATE dbo.Test SET xmlData.modify('replace value of (/Data/*[local-name()=sql:variable("@name")][1]/text())[1] with sql:variable("@val")')
WHERE something = @record
END
ELSE
BEGIN
PRINT 'Does Not Exist - will be inserted'
SET @element = '<' + @name + '>' + @val + '</' + @name + '>'
UPDATE dbo.Test SET xmlData.modify('insert sql:variable("@element") into (/Data)[1]')
WHERE something = @record
END
SET @counter = @counter + 1
END
The challenge I'm facing for the MERGE is on my modify statements and especially the insert which needs to have an XML data type to feed into it. They syntax is getting hairy, and I can't even get it to compile.
If someone can suggest options. Even if not with MERGE, just any improvements to my above solution to make it little better?
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply