Thinking outside the box

Manipulate XML data with non-xml columns and not using variable

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

Print | posted on Friday, July 03, 2009 3:57 PM | Filed Under [ SQL Server 2008 Administration SQL Server 2005 ]