Update XML column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - SQL Server 2005/Yukon

Update XML column

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2006-08-07 : 09:49:52

I want to update an xml column - replacing one guid with another wherever I find it.
But all the samples I've looked at seem to indicate that you have to do this on a record-by-record basis. Am I right?
That's gonna be a bit slow and crappy...
If so, I might have to go give my two cents on one of the XML rant threads...

Here http://blogs.msdn.com/anthonybloesch/archive/2005/09/14/XML_Columns.aspx
it says that "The new internal representation of XML and XML indexes supports much faster queries and updates."
How? This documention says there is a singleton requirement.
http://blogs.msdn.com/denisruc/archive/2005/06/14/429055.aspx

And here is a real lame requirement:
"The base table (i.e. the table in which the XML column occurs) must have a clustered index on the primary key of the table"
What a waste of the clustered index! Why is the Primary Key declaration not good enough?
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/sql2k5xmloptions.asp)

I cant see how you can use "replace value of" to do something table-wide like "UPDATE Mytable SET MyCol=1 WHERE MyCol=2".
I can do the WHERE clause section, that's easy:
SELECT *
FROM MyXmlDataTable
WHERE MyXmlColumn.exist ('//MyGuidNode[. = "94567A0D-FAF1-4667-8681-7A934E011F56"]') = 1
But not to sure on how to do the SET...
And if you use this approach, the XML column indexes will need maintainance. I'm sure we are not talking about DBCC INDEXDEFRAG here anymore...

The modify() method seems to be the thing to go for. Just not sure how the XQuery syntax would look with the update keyword.
If I try the BOL example:
UPDATE MyXmlDataTable
SET MyXmlColumn.modify('
replace value of (/MyNode/MySubNode)[1]
with 2131242 cast as xs:decimal ?')
WHERE MyXmlColumn.exist ('/MyNode/MySubNode[. = "2131241"]') = 1

I get the error:
Server: Msg 2356, Level 16, State 1, Line 3
XQuery [MyXmlDataTable.MyXmlColumn.modify()]: The target of 'replace value of' must be a non-metadata attribute or an element with simple typed content, found 'element(MySubNode,xdt:untyped) ?'

So it sounds like it's not typed. How do you give it a type?

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2006-08-11 : 16:14:40

I know, I know: It's bad form to reply to your own thread. But I'm doing this to show you guys an answer I got which I don't quite know what to make of. It's from Bob Beauchemin @ www.SQLskills.com. I know this sounds ridiculous but, did he answer my question?
Here it is, quote:

I don't usually follow web forums because I like to read newsgroup headers
by scrolling, lots at a time, web forums seem clunky to me by comparison,
lots of navigation... This may just mean I'm old ;-) and that my ISP hasn't
cut port 119. So I'll you answer directly.

What you are saying in the rant (I don't consider "rant" a derogatory term
;-) is technically accurate:
1. XQuery modify with insert needs a single insertion point, with update
value-of it updates value of ONE text node at a time.
2. XML indexes need primary key. Because XML itself is not comparable in SQL
Server, it can't be the primary key.
3. XML primary index produces a node table that's bigger than the data
itself.
4. XML queries and updates have "sub-optimal" perf with large documents,
unless indexed and maybe even if they are indexed. Depends on query.

Looking at it a bit differently:
1. There is no XQuery DML standard. They implemented a MS-IBM proposal.
Proposal had 4 operations: (insert, update, replace value-of, and delete).
They didn't implement "update". It's version 1 (really) of a non-standard. I
does do sparse logging, depending of where in the tree the update (modify)
is.
1a. Modify in a loop, one element at a time, may not be as fast as
set-based, but XML isn't set-based either. How bad if the perf, when you try
it? ;-)
2. I consider XML indexes akin to full-text search indexes. They index the
structure as well as data elements. You can also do partial decomposition
pulling out frequently used columns.
3. XML/XQuery is fairly fast with a reasonable set of small docs (like
purchase orders, invoices, etc). They did the XQuery version of XMark by
decomposing the "XML file as database" into 4 tables with XML column apiece.

I'd submit my suggestions to the Connect website
(http://connect.microsoft.com) because they're taking suggestions for
feature improvements for next release.

Hope this was helpful,
Bob Beauchemin
SQLskills

SamC
White Water Yakist

3467 Posts

Posted - 2006-08-29 : 09:49:15

I just did a search to see if xQuery was a relevant topic these days. The number of threads should be in relation to the number of implementations in-progress, and I was curious to know just how much XML / xQuery was being adopted by the community.

This thread is the only thread referencing xQuery in the last 60 days here on SQLTeam. Maybe this topic is busy elsewhere on the net, but not here.

Regarding the prior post which was somewhat depressing about xQuery, I'd reserve judgement on the performance. You can INDEX XML nodes within SQL 2005. I haven't found any papers on SQL 2005 XML / xQuery performance. It would be *very* interesting to compare the relative speed of searching of some simple indexed XML data type to conventional indexed column. This is an opportunity for someone to write a relevant performance article and for SQLTeam.

Sam

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-29 : 10:07:24

are you volonteering sam?

search is of course faster for non xml column.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

SamC
White Water Yakist

3467 Posts

Posted - 2006-08-29 : 12:15:20

quote:
Originally posted by spirit1

search is of course faster for non xml column.

I'm sure it is, but at design-time it is important to know if it's it's faster by modest amount or a dramatic amount of time. It does me no good at all to know "it's faster".

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources