Solved

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

Posted on 2008-06-10
4
796 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
  • 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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now