?
Solved

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

Posted on 2008-06-10
4
Medium Priority
?
914 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

862 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