Relational Model and XML

[Doug Baroter]

Hi,

This question has been bothering me for some time. A lot of people
seem to "think" XML is the king of data problems, and I've heard that
next version of SQL Server is going to have a strong XML flavor,
meantime, I seem to get the impression that a large number of
hard-core relational model gurus do not seem to be that impressed with
XML (technical value of this extra layer seems to be limited while
business value might be substantial for instance, more software work,
more disk space requirement etc. etc.). What's your take on this?
Generality or specifics, all welcome. One specific question is, how
can XML supplement relational model?

Thanks.

[Erland Sommarskog]

Re: Relational Model and XML

Doug Baroter (qwert12345@box frog.com) writes:[color=blue]
> This question has been bothering me for some time. A lot of people
> seem to "think" XML is the king of data problems, and I've heard that
> next version of SQL Server is going to have a strong XML flavor,
> meantime, I seem to get the impression that a large number of
> hard-core relational model gurus do not seem to be that impressed with
> XML (technical value of this extra layer seems to be limited while
> business value might be substantial for instance, more software work,
> more disk space requirement etc. etc.). What's your take on this?
> Generality or specifics, all welcome. One specific question is, how
> can XML supplement relational model?[/color]

A broad question, but XML seems to have its place. The point where XML
is really meaningful is data exchange. A database is a bunch of
tables, but they just sit in one place, and don't travel around. But
the data in them do. While XML may be bulky and lot of overhead in
bytes, it has the nice property that it defines a standard framework
that you can put your data into.

A nice side benefit of this, is that with XML we suddenly have
gotten a method of inserting lots of data into SQL Server with
just one roundtrip on the network: send down the XML document,
and then say INSERT ... OPENXML.

Then in Yukon they are taking it even further with adding Xquery,
you have systemm functions that return XML data etc. Maybe MS is
taking it a bit too far, but XML is here to stay.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Anith Sen]

Re: Relational Model and XML

>> What's your take on this? Generality or specifics, all welcome. <<

There is lot of research work (esp. papers at SIGMOD, DBPL etc on XML
suggests ) going on in the field of XML, but I am not sure if anything
positive has been established to consider XML as a data model alternative.

Vendors provide the consumers what they ask for, not what is empirical
and/or principled. When competition and customer retention become deciding
factors, often technologies that are obsolete, gets revived with minor
changes, under new terminologies. That is how the all profit-based
industries, including the IT industry, work.
[color=blue][color=green]
>> One specific question is, how can XML supplement relational model? <<[/color][/color]

The fundamentals of data management clearly tell me that Relational model
needs no supplementation by "XML" for anything relevant. However, it is
confusing that so many press articles on the topic are filled with
exaggerations and techno-tyros, who cannot distinguish a data exchange
technology from a data model, opining about XML going to change the world
overnight.

--
- Anith
( Please reply to newsgroups only )

[Ellen K.]

Re: Relational Model and XML

Yes, this is really nice because the user still only has to have
permission to execute the stored procedure. :)

Actually I found a problem with OPENXML that I've been meaning to
post. We have legacy flat-file systems (which of course we want to
replace, but we can't just snap our fingers and bingo it's done) that
use the equivalent of a char field for various numeric-appearing
identifiers such as our contract numbers. So for example, the
contract number field in the legacy system is 10 characters, but right
now we're still in the 700000's so it shows up as four spaces followed
by six numeric characters. OPENXML eats all the leading spaces
(Before you ask, yes, I am enclosing each attribute in quotes), so
then afterwards I have to reinsert them with an UPDATE statement if I
want the systems to talk to each other.

Any thoughts?

On Sun, 28 Dec 2003 23:47:25 +0000 (UTC), Erland Sommarskog
<sommar@algonet .se> wrote:
[color=blue]
>A nice side benefit of this, is that with XML we suddenly have
>gotten a method of inserting lots of data into SQL Server with
>just one roundtrip on the network: send down the XML document,
>and then say INSERT ... OPENXML.[/color]

[Doug Baroter]

Re: Relational Model and XML

Erland Sommarskog <sommar@algonet .se> wrote in message[color=blue]
> The point where XML is really meaningful is data exchange.[/color]
My point as well or agree whole-heartedly.
[color=blue]
>
> A nice side benefit of this, is that with XML we suddenly have
> gotten a method of inserting lots of data into SQL Server with
> just one roundtrip on the network: send down the XML document,
> and then say INSERT ... OPENXML.[/color]
Wouldn't that require a lot of parsing especially considering
one-to-many relationships, binary objects etc. So, in that sense, not
necessarily "nice", IMHO.
[color=blue]
>
> Then in Yukon they are taking it even further with adding Xquery,
> you have systemm functions that return XML data etc.[/color]
That's my sentiment as well.
[color=blue]
>Maybe MS is
> taking it a bit too far, but XML is here to stay.[/color]
Thanks for your opinion. BTW, I forgot to add quotes for "business
value" in my original posting which could be misleading.

[Doug Baroter]

Re: Relational Model and XML

I did not phrase my question correctly, which should have been "Would
XML add substantial value to data management?" I guess you would say
"No" while many other so-called forward-looking people may say "Yes".
I share your sentiment.
[color=blue]
> The fundamentals of data management clearly tell me that Relational model
> needs no supplementation by "XML" for anything relevant. However, it is
> confusing that so many press articles on the topic are filled with
> exaggerations and techno-tyros, who cannot distinguish a data exchange
> technology from a data model, opining about XML going to change the world
> overnight.[/color]

[Erland Sommarskog]

Re: Relational Model and XML

Ellen K. (72322.enno.ess peeayem.1016@co mpuserve.com) writes:[color=blue]
> So for example, the contract number field in the legacy system is 10
> characters, but right now we're still in the 700000's so it shows up as
> four spaces followed by six numeric characters. OPENXML eats all the
> leading spaces (Before you ask, yes, I am enclosing each attribute in
> quotes), so then afterwards I have to reinsert them with an UPDATE
> statement if I want the systems to talk to each other.[/color]

I would guess that is part of the XML specification, but I'm not very
well versed in XML. However, this little snippet may help you to restore
the spaces directly:

create table #h (g char(10) NOT NULL)
go
declare @xml nvarchar(4000), @d int
select @xml = '<H><B g=" 123456"/><B g="1234567890 "/></H>'
exec sp_xml_prepared ocument @d output, @xml
insert #h(g)
SELECT replicate(' ', 10 - len(g)) + g
FROM OPENXML(@d, '/H/B', 1) WITH (g char(10)) AS x
exec sp_xml_removedo cument @d
select '<' + g + '>' FROM #h
go
drop table #h

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Erland Sommarskog]

Re: Relational Model and XML

Doug Baroter (qwert12345@box frog.com) writes:[color=blue]
> Wouldn't that require a lot of parsing especially considering
> one-to-many relationships, binary objects etc. So, in that sense, not
> necessarily "nice", IMHO.[/color]

Of course the parsing requires some CPU. But with today's CPU's I don't
think is a major issue. Network round-trips can easily be more expensive,
not the least on a busy network. And not the least 5000 individual
INSERT statements for each row rather than one for each table.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Doug Baroter]

Re: Relational Model and XML

Erland Sommarskog <sommar@algonet .se> wrote in message news:<Xns9460F2 5E5D928Yazorman @127.0.0.1>...[color=blue]
> Of course the parsing requires some CPU. But with today's CPU's I don't
> think is a major issue. Network round-trips can easily be more expensive,
> not the least on a busy network. And not the least 5000 individual
> INSERT statements for each row rather than one for each table.[/color]

But with today's network speed, it used to be 10 MBPS, nowdays
standard 100 MBPS, in some environment, much greater than 100 MBPS;
simultaneous 1000 INSERTs into the same table at a given second would
seem to be rare even for a Fortune 100 company, hence, transaction
speed improvement does not seem to be that a big deal. Having said
that, I agree your arguement has a valid point.
Now, a totally separate question if you don't mind, probably you've
designed a stock trading system of your own, and I would think you've
been quite sucessful in doing that, do you take a student or two
occasionally?

[Ellen K.]

Re: Relational Model and XML

I have a batch process that runs every night which creates and then
inserts between 2000 and 5000 rows to a particular table, and I do it
with XML for exactly the reason Erland states, I don't want to make
all those roundtrips. We are nowhere NEAR Fortune 100, revenues are
less than $100MM.

On 29 Dec 2003 19:13:42 -0800, qwert12345@boxf rog.com (Doug Baroter)
wrote:
[color=blue]
>simultaneous 1000 INSERTs into the same table at a given second would
>seem to be rare even for a Fortune 100 company, hence, transaction
>speed improvement does not seem to be that a big deal.[/color]

[Ellen K.]

Re: Relational Model and XML

I'm not well-versed either, in fact I create the XML document by
<red-face emoticon> building a giant string. However, I sure like
being able to get 5000 rows across the network and into my table in
ONE SECOND. :)

I'm going to send myself the below and experiment with it at work,
thanks very much. :)

On Mon, 29 Dec 2003 22:48:36 +0000 (UTC), Erland Sommarskog
<sommar@algonet .se> wrote:
[color=blue]
>Ellen K. (72322.enno.ess peeayem.1016@co mpuserve.com) writes:[color=green]
>> So for example, the contract number field in the legacy system is 10
>> characters, but right now we're still in the 700000's so it shows up as
>> four spaces followed by six numeric characters. OPENXML eats all the
>> leading spaces (Before you ask, yes, I am enclosing each attribute in
>> quotes), so then afterwards I have to reinsert them with an UPDATE
>> statement if I want the systems to talk to each other.[/color]
>
>I would guess that is part of the XML specification, but I'm not very
>well versed in XML. However, this little snippet may help you to restore
>the spaces directly:
>
> create table #h (g char(10) NOT NULL)
> go
> declare @xml nvarchar(4000), @d int
> select @xml = '<H><B g=" 123456"/><B g="1234567890 "/></H>'
> exec sp_xml_prepared ocument @d output, @xml
> insert #h(g)
> SELECT replicate(' ', 10 - len(g)) + g
> FROM OPENXML(@d, '/H/B', 1) WITH (g char(10)) AS x
> exec sp_xml_removedo cument @d
> select '<' + g + '>' FROM #h
> go
> drop table #h[/color]

[Erland Sommarskog]

Re: Relational Model and XML

Doug Baroter (qwert12345@box frog.com) writes:[color=blue]
> But with today's network speed, it used to be 10 MBPS, nowdays
> standard 100 MBPS, in some environment, much greater than 100 MBPS;
> simultaneous 1000 INSERTs into the same table at a given second would
> seem to be rare even for a Fortune 100 company, hence, transaction
> speed improvement does not seem to be that a big deal.[/color]

Oh, there is a lot more to it that you imagine!

First of all, network delays comes in two flavours: transfer speed
and latency. Even a if powerful network can have significant latency.
To take an extreme example: for a while, at least, the fastest Internet
connection that was an option for private persons was satellite links.
They can give you great download speeds. But the latency is longer than
on a 9600 modem line. A crowded network can also have significant
latency. If you send many INSERT operations, then you make many routing
requests. And since communication is synchrounous, you have to wait
for respnnse too.

But there are also things to consider on the SQL Server side. If you
send singular INSERT statements, you really lose. Here is a case where
you win big with stored procedures; SQL Server does not have to parse
each statement, but can resue the cached plan.
[color=blue]
> Now, a totally separate question if you don't mind, probably you've
> designed a stock trading system of your own, and I would think you've
> been quite sucessful in doing that, do you take a student or two
> occasionally?[/color]

I have not designed any stock-trading system on my own, but I work
for a company that provides such a product. (And which is really the
work of many knowledgable persons, not only me.) Currently, we do
not have any openings, I'm sorry.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Doug Baroter]

Re: Relational Model and XML

Erland Sommarskog <sommar@algonet .se> wrote in message >[color=blue]
> I have not designed any stock-trading system on my own, but I work
> for a company that provides such a product. (And which is really the
> work of many knowledgable persons, not only me.)[/color]
Thanks for the info. BTW, I was introduced to a CS professor who
designed such a system on his own, and the "word" goes around, he's
doing well with that and he himself indicates so as well, and implied
to help me get started with that too. Too bad, later on for some
weird reason he was offended.