With the advent of xml columns in SQL 2005, I was looking forward to the data manipulation that XML Data Manipulation Language (DML) provides.
Unfortunately, it appears that the implementation is horribly limited in that it cannot make an arbitrary number of modifications to the same value in a single update. Take the example below, where we attempt to insert one node for every value in @IntData (3 total) into the xml column of our first row in @XmlData.
DECLARE @XmlData TABLE (ID INT NOT NULL, Data XML NOT NULL)
INSERT INTO @XmlData (ID, Data) VALUES (1, '')
DECLARE @IntData TABLE (ID INT NOT NULL)
INSERT INTO @IntData (ID) VALUES(2)
INSERT INTO @IntData (ID) VALUES(4)
INSERT INTO @IntData (ID) VALUES(6)
SELECT XD.Data, INTD.ID
FROM @XmlData XD
CROSS JOIN @IntData INTD
UPDATE XD SET Data.modify('
insert
as last into /
')
FROM @XmlData XD
CROSS JOIN @IntData INTD
WHERE XD.ID = 1
SELECT Data FROM @XmlData
As you can see from the output, the cross join returns 3 records but the update only inserts one node. The only way around this is to use a cursor, which is a shame. Here’s hoping that they fix that up in SQL 2008.
Advertisement
