Some time ago, I displayed how to work with XML data when searching for data stored in a XML column.
Here Some XML search approaches and here Updated XML search (test case with variables).
Today I thought I should demonstrate how to insert and update XML columns with data from other columns and not using variables. Well, I do have some examples of using variables in here because I wanted to display the core difference.
And this is my first blog post (not counting the previous announcement) since I become a MVP, I thought this blog post should be about something necessary.
Much of the XML manipulations I have made by trials and errors, because I haven’t found a single good source of information about these things. If you do know of such information, please comment.
First, we create a sample table to hold some important data, like this
CREATE TABLE #Sample
(
RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MemberID INT,
MemberData XML
)
INSERT #Sample
SELECT 1, '<meta><customergroup>F</customergroup><mosaic>Young educated man</mosaic></meta>' UNION ALL
SELECT 2, '<meta><age>24</age></meta>'
CREATE PRIMARY XML INDEX IX_PrimaryXML ON #Sample(MemberData)
CREATE XML INDEX IX_Element ON #Sample(MemberData)
USING XML INDEX IX_PrimaryXML FOR PATH
As you see, I also created the primary XML index and a secondary XML index on the XML column.
Now let’s see what is stored in the important table.
SELECT MemberID,
MemberData,
MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM #Sample
The exercise now is to add one or more elements to the XML column, both with using a variable but also, more importantly, using a column. Create a helper table like this below.
DECLARE @Sample TABLE
(
MemberID INT,
ZipCode VARCHAR(5),
Mosaic VARCHAR(200)
)
INSERT @Sample
SELECT 1, '26737', 'SQLTeam' UNION ALL
SELECT 2, '12345', 'Smart woman in the countryside'
When an element doesn’t already exist, the element is added to the XML column. Beware that an additional element is created if one alerady exists!
-- New element for all
UPDATE s
SET MemberData.modify('insert <zipcode>{sql:column("x.ZipCode")}</zipcode> into (/meta)[1]')
FROM #Sample AS s
INNER JOIN @Sample AS x ON x.MemberID = s.MemberID
Now do the SELECT again and you will see that a zipcode element was added to both records.
SELECT MemberID,
MemberData,
MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM #Sample
As I said before, if an element already exist a new elemtent with same name is added. This behaviour can be avoided by using a WHERE MemberData.exists clause. Do the updates first since it only touches existing records with matching elements and then do the inserts with the MemberData.exists clause.
-- Update existing element
UPDATE s
SET MemberData.modify('replace value of (/meta/mosaic/text())[1] with sql:column("x.Mosaic")')
FROM #Sample AS s
INNER JOIN @Sample AS x ON x.MemberID = s.MemberID
-- Insert new element. Beware of duplicates.
UPDATE s
SET MemberData.modify('insert <mosaic>{sql:column("x.Mosaic")}</mosaic> into (/meta)[1]')
FROM #Sample AS s
INNER JOIN @Sample AS x ON x.MemberID = s.MemberID
Now do the SELECT again and you will see that the element mosaic was updated for member 1 and added to member 2. This is also true for member 1 because now this member has two elements with same name.
SELECT MemberID,
MemberData,
MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM #Sample
And this was my reason for this blog post. How to add and replace elements with values from another column. So why not display how to do this with variables too?
DECLARE @Status VARCHAR(18)
SET @Status = 'Shipped'
-- Update single record without prior element
UPDATE #Sample
SET MemberData.modify('replace value of (/meta/status/text())[1] with sql:variable("@Status")')
WHERE MemberID = 1
-- Create new element for single record
UPDATE #Sample
SET MemberData.modify('insert <status>{sql:variable("@Status")}</status> as last into (/meta)[1]')
WHERE MemberID = 2
As you can see now, same rules apply. Member 1 did not get a new Status element since we wanted to update previous value. But member 2 did get a new element.
SELECT MemberID,
MemberData,
MemberData.value('/meta[1]/customergroup[1]', 'VARCHAR(8)') AS CustomerGroup,
MemberData.value('/meta[1]/mosaic[1]', 'VARCHAR(200)') AS Mosaic,
MemberData.value('/meta[1]/age[1]', 'TINYINT') AS Age,
MemberData.value('/meta[1]/zipcode[1]', 'VARCHAR(5)') AS ZipCode,
MemberData.value('/meta[1]/status[1]', 'VARCHAR(15)') AS [Status]
FROM #Sample
Don’t forget to do your housecleaning and drop the important table.
DROP TABLE #Sample
Besides doing update and insert, you can also use the delete syntax to remove elements if you jus don’t to clear them.
I hope you liked this post.
Cheers!