Solved

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

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

Independent Software Vendors: 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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

740 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