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>
>

Best design for a service that will monitor db

We have an existing database that is constantly receiving updated
information on the status and attributes of specific objects within the
application in batches. As these records come in, there is portions of the
table that they populate that are intentionally left empty, because the data
for these fields is retrieved from a seperate Java application through a
published web service (on same network). We are constructing a .Net service
which will handle the retrieval of records from the Java app and push the
new data into the relevant fields.
We are currently designing the .NET service to check the database on a
predefined interval, to see if any new records have appeared that need to be
looked up in the Java application. However, it would be preferable (at
least for testing) if this interaction could be designed so that the Sql
Server 2000 database could notify the .Net service that a new batch of
records has arrived (push instead of pull). Does anyone have any knowledge
if there is a means by which this can be accomplished?
Thanks.Hmmm ... There surely is a notificaiton service in SQL Server but as far as
I've read the documentation it maynot be suitable for this problem ...
But I think you can write a trigger that can call a DTS package or a Jobs
framework to do this notification ... I think this can also be one solution
...
--
HTH,
Vinod Kumar
MCSE, DBA, MCAD
http://www.extremeexperts.com
"nfalconer" <navid@.gci.net> wrote in message
news:vm4o27o29cu596@.corp.supernews.com...
> We have an existing database that is constantly receiving updated
> information on the status and attributes of specific objects within the
> application in batches. As these records come in, there is portions of
the
> table that they populate that are intentionally left empty, because the
data
> for these fields is retrieved from a seperate Java application through a
> published web service (on same network). We are constructing a .Net
service
> which will handle the retrieval of records from the Java app and push the
> new data into the relevant fields.
> We are currently designing the .NET service to check the database on a
> predefined interval, to see if any new records have appeared that need to
be
> looked up in the Java application. However, it would be preferable (at
> least for testing) if this interaction could be designed so that the Sql
> Server 2000 database could notify the .Net service that a new batch of
> records has arrived (push instead of pull). Does anyone have any
knowledge
> if there is a means by which this can be accomplished?
> Thanks.
>