Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Manipulate XML data, continued

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]')
 
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
 
DROP TABLE #Sample

Print | posted on Tuesday, August 04, 2009 9:30 AM | Filed Under [ SQL Server 2008 SQL Server 2005 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET