Best Practice for Structuring XML?

I've created a large XML document from a relational database (using AUTO,
EXPLICIT, etc.) with many elements, attributes, and subelements but now
wonder if there is a "best practice" for designing the structure for going
the other way, XML -> relational. Since I have not yet worked on the data
extraction side, maybe what I've put together makes data extraction awkward
(requiring many lines of T-SQL vs. one or two). But I definitely cannot
stomach the 'all attribute' or 'all element' practices. Between the two
examples below, which is better/easier/more efficient/flexible for
retrieving information (e.g. with OPENXML). I like the first example
theoretically, but the second is REAL easy to generate (with FOR XML AUTO,
ELEMENTS). Thanks for any tips or insights.
<entities>
<entity>
<entityAttribute>nameOfThisEntity</entityAttribute>
<entityValue>valueOfThisEntity</entityValue>
</entity>
<entity>
<entityAttribute>nameOfNextEntity</entityAttribute>
<entityValue>valueOfNextEntity</entityValue>
</entity>
<entity>
...
</entity>
</entities>
vs.
<entities>
<nameOfThisEntity>valueOfThisEntity</nameOfThisEntity>
<nameOfNextEntity>valueOfNextEntity</nameOfNextEntity>
...
</entities>
Hi Don,
I preferred to the first one, although I do not think there will be much
performance difference between the following two XML structures. The fist
XML structure will be more readable and efficient for search. The following
article will tell you how to optimize SQLXML performance for databases,
including SQL Server 2000.
SQLXML best practice paper on MSDN
http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/sqlxml_optimperformance.asp?frame=true
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
|||If you plan on using OpenXML, then size and ability to query structure
instead of values will most likely make your second format perform better.
Best regards
Michael
"Don Miller" <nospam@.nospam.com> wrote in message
news:es8k9YfLEHA.2396@.TK2MSFTNGP12.phx.gbl...
> I've created a large XML document from a relational database (using AUTO,
> EXPLICIT, etc.) with many elements, attributes, and subelements but now
> wonder if there is a "best practice" for designing the structure for going
> the other way, XML -> relational. Since I have not yet worked on the data
> extraction side, maybe what I've put together makes data extraction
> awkward
> (requiring many lines of T-SQL vs. one or two). But I definitely cannot
> stomach the 'all attribute' or 'all element' practices. Between the two
> examples below, which is better/easier/more efficient/flexible for
> retrieving information (e.g. with OPENXML). I like the first example
> theoretically, but the second is REAL easy to generate (with FOR XML AUTO,
> ELEMENTS). Thanks for any tips or insights.
> <entities>
> <entity>
> <entityAttribute>nameOfThisEntity</entityAttribute>
> <entityValue>valueOfThisEntity</entityValue>
> </entity>
> <entity>
> <entityAttribute>nameOfNextEntity</entityAttribute>
> <entityValue>valueOfNextEntity</entityValue>
> </entity>
> <entity>
> ...
> </entity>
> </entities>
> vs.
> <entities>
> <nameOfThisEntity>valueOfThisEntity</nameOfThisEntity>
> <nameOfNextEntity>valueOfNextEntity</nameOfNextEntity>
> ...
> </entities>
>

Benchmarks for XML EXPLICIT vs. standard recordset?

Hello all.
I was wondering if anyone had links and/or information regarding SQL Server
2000 performance benchmarks when it comes to using FOR XML EXPLICIT
techniques versus using a standard recordset. I've built a number of stored
procs at work using FOR XML EXPLICIT and it's been a huge time-saver. But
alas, the DBAs are unfamiliar (and thus "uncomfortable") with my use of these
techniques.
The alternative, manually building an XML document from recordsets on the VB.
NET side, seems sloppy and cumbersome to me. I'm hoping I can garner some
ammunition that supports FOR XML EXPLICIT.I haven't seen any performance figures (but you might want to re-post in the
.sqlserver.xml group just to make sure). If I were you, I would run some
load tests on both the XML procedures and equivalent rowset procedures to
show whether or not the XML will cause a performance problem.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Frefaln via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:52BBD3DFC1848@.SQLMonster.com...
> Hello all.
> I was wondering if anyone had links and/or information regarding SQL
Server
> 2000 performance benchmarks when it comes to using FOR XML EXPLICIT
> techniques versus using a standard recordset. I've built a number of
stored
> procs at work using FOR XML EXPLICIT and it's been a huge time-saver. But
> alas, the DBAs are unfamiliar (and thus "uncomfortable") with my use of
these
> techniques.
> The alternative, manually building an XML document from recordsets on the
VB.
> NET side, seems sloppy and cumbersome to me. I'm hoping I can garner some
> ammunition that supports FOR XML EXPLICIT.

Benchmarks for XML EXPLICIT vs. standard recordset?

Hello all.
I was wondering if anyone had links and/or information regarding SQL Server
2000 performance benchmarks when it comes to using FOR XML EXPLICIT
techniques versus using a standard recordset. I've built a number of stored
procs at work using FOR XML EXPLICIT and it's been a huge time-saver. But
alas, the DBAs are unfamiliar (and thus "uncomfortable") with my use of these
techniques.
The alternative, manually building an XML document from recordsets on the VB.
NET side, seems sloppy and cumbersome to me. I'm hoping I can garner some
ammunition that supports FOR XML EXPLICIT.
I haven't seen any performance figures (but you might want to re-post in the
..sqlserver.xml group just to make sure). If I were you, I would run some
load tests on both the XML procedures and equivalent rowset procedures to
show whether or not the XML will cause a performance problem.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Frefaln via droptable.com" <forum@.droptable.com> wrote in message
news:52BBD3DFC1848@.droptable.com...
> Hello all.
> I was wondering if anyone had links and/or information regarding SQL
Server
> 2000 performance benchmarks when it comes to using FOR XML EXPLICIT
> techniques versus using a standard recordset. I've built a number of
stored
> procs at work using FOR XML EXPLICIT and it's been a huge time-saver. But
> alas, the DBAs are unfamiliar (and thus "uncomfortable") with my use of
these
> techniques.
> The alternative, manually building an XML document from recordsets on the
VB.
> NET side, seems sloppy and cumbersome to me. I'm hoping I can garner some
> ammunition that supports FOR XML EXPLICIT.

Benchmarks for XML EXPLICIT vs. standard recordset?

Hello all.
I was wondering if anyone had links and/or information regarding SQL Server
2000 performance benchmarks when it comes to using FOR XML EXPLICIT
techniques versus using a standard recordset. I've built a number of stored
procs at work using FOR XML EXPLICIT and it's been a huge time-saver. But
alas, the DBAs are unfamiliar (and thus "uncomfortable") with my use of thes
e
techniques.
The alternative, manually building an XML document from recordsets on the VB
.
NET side, seems sloppy and cumbersome to me. I'm hoping I can garner some
ammunition that supports FOR XML EXPLICIT.I haven't seen any performance figures (but you might want to re-post in the
.sqlserver.xml group just to make sure). If I were you, I would run some
load tests on both the XML procedures and equivalent rowset procedures to
show whether or not the XML will cause a performance problem.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Frefaln via droptable.com" <forum@.droptable.com> wrote in message
news:52BBD3DFC1848@.droptable.com...
> Hello all.
> I was wondering if anyone had links and/or information regarding SQL
Server
> 2000 performance benchmarks when it comes to using FOR XML EXPLICIT
> techniques versus using a standard recordset. I've built a number of
stored
> procs at work using FOR XML EXPLICIT and it's been a huge time-saver. But
> alas, the DBAs are unfamiliar (and thus "uncomfortable") with my use of
these
> techniques.
> The alternative, manually building an XML document from recordsets on the
VB.
> NET side, seems sloppy and cumbersome to me. I'm hoping I can garner some
> ammunition that supports FOR XML EXPLICIT.

Begin Trans

If you are set up for AutoCommit why would you or should you set a explicit transaction? I have noticed that in some called stored procudures from a "container" stored procedure. (Hope I got that right) that in the called stored procedure a Begin tran is used. Can anyone help with the why and what fors? It seems to me that you want to let SQL Server handle this becuase of the danger of leaving out a Commit or Rollback? But thats me. I may be very wrong? Thanks.

TomWell...there's a lot of reading you can do in books online...BUT, just as a good general coding practice you should...

I like to follow this template...I'd say most of my sprocs are this way...like 90%

http://weblogs.sqlteam.com/brettk/archive/2004/05/25/1378.aspx