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 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


Cheers!

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 just don’t to clear them.
I hope you liked this post.
Delete is described here http://weblogs.sqlteam.com/peterl/archive/2009/08/04/Manipulate-XML-data-continued.aspx

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

Feedback

Gravatar

# question

Hi
I am using sybus database in asp.net i have view of database and my job that on the database there is time field which have store data in int form so, i need sql which could convert int datatype to time HH:MM:SS
plz do needful
7/4/2009 11:54 AM | naveed
Gravatar

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

Well, this is a little bit off-topic but the seconds are most probably based on some "ground date". Could be a fixed date like 19700101, or number of seconds since last midnight.

In either case, use DATEADD

DATEADD(SECOND, Col1, '19700101')
DATEADD(SECOND, Col1, DATEDIFF(DAY, 0, GETDATE())

or, if date is stored separetely in another columns

DATEADD(SECOND, secCol, dateCol)
7/4/2009 6:38 PM | Peso
Gravatar

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

Hey,
A very good post! I too liked your both ttutorials, how to work with XML data when searching for data stored in a XML column and how to insert and update XML columns with data from other columns and not using variables:) your examples are very clear too. will start applying it from now.... thanks man!
12/29/2009 12:36 PM | Rapid Share
Gravatar

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

I need your help please. I don't work with xml normally.

I have a SQL 2008 database that has an XML column. This column contains sizes for a product, eg s,m,l etc. I need to extract the information and put it into a string for further manipulation. Each segment of the string would be 3 chars, so the string for the s,m,l would be 's m l '. I can then scan the string and manipulate in SQL.

An extract of the xml could be like this:
<?xml version="1.0" encoding="utf-16"?>
<StdSegment>
<StdSegVal>
<StdValCde>s</StdValCde>
<StdValDsc>small</StdValDsc>
</StdSegVal>
<StdSegVal>
<StdValCde>m</StdValCde>
<StdValDsc>medium</StdValDsc>
</StdSegVal>

etc.


Problem is I don't know how to read the xml column and create a string.

Please can you help me?

Regards
Clive
9/19/2010 11:24 AM | Clive Dembo
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET