Solved

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 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

18 Experts available now in Live!

Get 1:1 Help Now