Manipulate XML data, continued

Tue Aug 4, 2009 by Peter Larsson in sql-server-2008, sql-server-2005

In my previous blog post about how to manipulate data in XML columns, http://weblogs.sqlteam.com/peterl/archive/2009/07/03/Manipulate-XML-data-with-non-xml-columns-and-not-using-variable.aspx, I didn't have time to include how to delete elements.
Now I have and here you can see how to delete elements from a XML column. I am using the same sample data for easier understanding.

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

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

-- Delete an element

UPDATE #Sample

SET MemberData.modify('delete /meta[1]/customergroup[1]')