[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Pros and Cons of using xml data type to perform database maintenance with

Posted on 2008-06-10
4
Medium Priority
?
886 Views
Last Modified: 2010-05-18
I was trying to find the pros and cons of using this approach, but thought I would also ask everyone on here as well.

In Sql Server 2000 and 2005 I have used xml to perform multiple updates across many tables within one stored procedure, depending on what project I have worked on, and I have never seen any real performance issues with this. However, someone today brought it up that it could be an issue, and was wondering if anyone has any opinions on the pros and cons of it.

Basically I build the xml and then pass it through the stored procedure, and from there I insert/update records, rather than making multiple calls to the database. Would be interesting what people think about using xml like this.
0
Comment
Question by:JDEE8297
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 8

Expert Comment

by:srnar
ID: 21757362
You are going with main stream. Lets look into Microsoft Analysis Services: there is a special configuration language called XMLA. Almost every configuration/maintanance operation has its XMLA form. You can see XMLA even in the SQL server profiler when tracing Analysis Services engine.

With relational database there is strong user base and thus also SQL standards - so for maintanance there are basicaly used DDL statemens.

Using XML data type instead of VARCHAR has one important pros: you can specify schema definition (and therfore use strong interface) ergo constraint possible errors.
0
 

Author Comment

by:JDEE8297
ID: 21758887
Is there any performance issues with the use of OpenXML, I guess like everything in SQL SERVER or for that matter any development environment, everything has a reason for when it should be used and when it shouldn't be used. I don't want to get in to arguement with my fellow developer over we should use this and shouldn't use it.

Life is too short to get into those kind of arguements or disagreements, kind of like....I read this in a book and we shouldn't do that, and there fore we will not type attitude is what I am trying to avoid. :)
0
 

Accepted Solution

by:
JDEE8297 earned 0 total points
ID: 21760599
I was reading some more about OpenXML and the pros and cons of it and came across this thing called Xquery, which is a Sql Server 2005 feature.

DECLARE @xml XML

SET @xml = '<DATA><xmlRow id="1" price="10.00" parent="0" body_text="jjjj" /><xmlRow id="4" price="10.00" parent="1" body_text="jjfdsfsdjj" /><xmlRow id="2" price="122.00" parent="4" body_text="jaasjjj" /><xmlRow id="3" price="10.00" parent="1" body_text="jdfsjjj" /></DATA>'


SELECT @xml.query('/DATA/xmlRow')
SELECT data.datacolumn.value('@id','INT'), data.datacolumn.value('@parent','int') AS parentid
FROM @xml.nodes('/DATA/xmlRow') AS data(datacolumn)

works very similar to what you can do with OpenXML, only less overhead.

The only part that I am not sure on how to make it work, is if I only wanted records where parent = 4, I know I could take the following data.datacolumn.value('@parent','int') and add that to the where clause, but was wondering if there was a better way of doing it.
0
 
LVL 8

Expert Comment

by:srnar
ID: 21760645
Thanks for investigation.
0

Featured Post

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question