OPENXML - XML SOAP

Why are you storing XML documents in your database?

Assuming your XML is less than 8000 characters, you can use OPENXML for
this, but you'll have to process a single row at a time (which shouldn't be
a problem, considering that you're doing this based on a client id). Read
up on the OPENXML syntax in Books Online and check out this site if you need
further help: http://www.sqlxml.org

--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--


"Robert Strickland" <strickra@rjsonline.net> wrote in message
news:uuCgvX1LFHA.1956@TK2MSFTNGP15.phx.gbl...
> I have a table with columns that contains client data (id, name, etc.) and
> one column that contains xml. I would like to bring back through a select
> the client name and certain attribute values from the xml document based
on
> a passed client id. Can someone get me started or direct me to a site with
> this information?
>
> Thanks
>
>

Thanks for responding.

The xml stream is not long, most are only 500-1000 bytes in length (nothing
bigger then 2000 bytes). We save the xml stream(and not to various tables
and rows) because of the sometimes deep heirarchy within the different
elements. This also helps with web pages and pdf reports because we can use
xsl transformations to display the data. Please direct me to any site that
discusses the pros and cons of saving Xml streams in SQL Server (vs. tables
and rows).

Thanks.

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:ub1J5R7LFHA.1156@TK2MSFTNGP09.phx.gbl...
> Why are you storing XML documents in your database?
>
> Assuming your XML is less than 8000 characters, you can use OPENXML for
> this, but you'll have to process a single row at a time (which shouldn't
> be
> a problem, considering that you're doing this based on a client id). Read
> up on the OPENXML syntax in Books Online and check out this site if you
> need
> further help: http://www.sqlxml.org
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>
> "Robert Strickland" <strickra@rjsonline.net> wrote in message
> news:uuCgvX1LFHA.1956@TK2MSFTNGP15.phx.gbl...
>> I have a table with columns that contains client data (id, name, etc.)
>> and
>> one column that contains xml. I would like to bring back through a select
>> the client name and certain attribute values from the xml document based
> on
>> a passed client id. Can someone get me started or direct me to a site
>> with
>> this information?
>>
>> Thanks
>>
>>
>
>

This article covers some of the pros and cons:

http://www.sql-server-performance.co...r1_excerpt.asp

But the really important thing to remember is that XML documents should be
stored in the database only if you have no need to use them as part of a
predicate (i.e., select all rows where my document's "age" node has a value
greater than 5) or return subsets of them in queries (i.e. return client
name, and the first product the client purchased, taken from the XML
document). When you get to that stage, you're wasting a lot of resources,
making queries incredibly difficult to write, and risking data integrity
when you continue to use XML documents. Tell me if you need clarification
on any of that...


--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--


"Robert Strickland" <bstrickland@comporium.net> wrote in message
news:%23sduxq7LFHA.1308@tk2msftngp13.phx.gbl...
> Thanks for responding.
>
> The xml stream is not long, most are only 500-1000 bytes in length
(nothing
> bigger then 2000 bytes). We save the xml stream(and not to various tables
> and rows) because of the sometimes deep heirarchy within the different
> elements. This also helps with web pages and pdf reports because we can
use
> xsl transformations to display the data. Please direct me to any site that
> discusses the pros and cons of saving Xml streams in SQL Server (vs.
tables
> and rows).
>
> Thanks.
>
> "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
> news:ub1J5R7LFHA.1156@TK2MSFTNGP09.phx.gbl...
> > Why are you storing XML documents in your database?
> >
> > Assuming your XML is less than 8000 characters, you can use OPENXML for
> > this, but you'll have to process a single row at a time (which shouldn't
> > be
> > a problem, considering that you're doing this based on a client id).
Read
> > up on the OPENXML syntax in Books Online and check out this site if you
> > need
> > further help: http://www.sqlxml.org
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.datamanipulation.net
> > --
> >
> >
> > "Robert Strickland" <strickra@rjsonline.net> wrote in message
> > news:uuCgvX1LFHA.1956@TK2MSFTNGP15.phx.gbl...
> >> I have a table with columns that contains client data (id, name, etc.)
> >> and
> >> one column that contains xml. I would like to bring back through a
select
> >> the client name and certain attribute values from the xml document
based
> > on
> >> a passed client id. Can someone get me started or direct me to a site
> >> with
> >> this information?
> >>
> >> Thanks
> >>
> >>
> >
> >
>
>

Understood.

Thanks

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:uukdbz7LFHA.508@TK2MSFTNGP12.phx.gbl...
> This article covers some of the pros and cons:
>
> http://www.sql-server-performance.co...r1_excerpt.asp
>
> But the really important thing to remember is that XML documents should be
> stored in the database only if you have no need to use them as part of a
> predicate (i.e., select all rows where my document's "age" node has a
> value
> greater than 5) or return subsets of them in queries (i.e. return client
> name, and the first product the client purchased, taken from the XML
> document). When you get to that stage, you're wasting a lot of resources,
> making queries incredibly difficult to write, and risking data integrity
> when you continue to use XML documents. Tell me if you need clarification
> on any of that...
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>
> "Robert Strickland" <bstrickland@comporium.net> wrote in message
> news:%23sduxq7LFHA.1308@tk2msftngp13.phx.gbl...
>> Thanks for responding.
>>
>> The xml stream is not long, most are only 500-1000 bytes in length
> (nothing
>> bigger then 2000 bytes). We save the xml stream(and not to various tables
>> and rows) because of the sometimes deep heirarchy within the different
>> elements. This also helps with web pages and pdf reports because we can
> use
>> xsl transformations to display the data. Please direct me to any site
>> that
>> discusses the pros and cons of saving Xml streams in SQL Server (vs.
> tables
>> and rows).
>>
>> Thanks.
>>
>> "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
>> news:ub1J5R7LFHA.1156@TK2MSFTNGP09.phx.gbl...
>> > Why are you storing XML documents in your database?
>> >
>> > Assuming your XML is less than 8000 characters, you can use OPENXML for
>> > this, but you'll have to process a single row at a time (which
>> > shouldn't
>> > be
>> > a problem, considering that you're doing this based on a client id).
> Read
>> > up on the OPENXML syntax in Books Online and check out this site if you
>> > need
>> > further help: http://www.sqlxml.org
>> >
>> > --
>> > Adam Machanic
>> > SQL Server MVP
>> > http://www.datamanipulation.net
>> > --
>> >
>> >
>> > "Robert Strickland" <strickra@rjsonline.net> wrote in message
>> > news:uuCgvX1LFHA.1956@TK2MSFTNGP15.phx.gbl...
>> >> I have a table with columns that contains client data (id, name, etc.)
>> >> and
>> >> one column that contains xml. I would like to bring back through a
> select
>> >> the client name and certain attribute values from the xml document
> based
>> > on
>> >> a passed client id. Can someone get me started or direct me to a site
>> >> with
>> >> this information?
>> >>
>> >> Thanks
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Let me correct this to some extend. While combining CLOB XML types with
OpenXML and predicates is less efficient and complex, the addition of native
XML datatypes with XQuery/XPath support will move the trade-off balance. If
you most often access the XML data, but you may need to run predicates of
parts of the document, you will want to either use computed columns that
promote the values from the XML doc using XQuery expressions or use the
XQuery directly on the XML datatype. If you often repurpose your data (e.g.,
summarize your order information without caring about their owning
customers), then shredding the data into relational form and operate on it
relationally is still the preferred approach from a perf point of view.

Best regards
Michael

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:uukdbz7LFHA.508@TK2MSFTNGP12.phx.gbl...
> This article covers some of the pros and cons:
>
> http://www.sql-server-performance.co...r1_excerpt.asp
>
> But the really important thing to remember is that XML documents should be
> stored in the database only if you have no need to use them as part of a
> predicate (i.e., select all rows where my document's "age" node has a
> value
> greater than 5) or return subsets of them in queries (i.e. return client
> name, and the first product the client purchased, taken from the XML
> document). When you get to that stage, you're wasting a lot of resources,
> making queries incredibly difficult to write, and risking data integrity
> when you continue to use XML documents. Tell me if you need clarification
> on any of that...
>
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>
> "Robert Strickland" <bstrickland@comporium.net> wrote in message
> news:%23sduxq7LFHA.1308@tk2msftngp13.phx.gbl...
>> Thanks for responding.
>>
>> The xml stream is not long, most are only 500-1000 bytes in length
> (nothing
>> bigger then 2000 bytes). We save the xml stream(and not to various tables
>> and rows) because of the sometimes deep heirarchy within the different
>> elements. This also helps with web pages and pdf reports because we can
> use
>> xsl transformations to display the data. Please direct me to any site
>> that
>> discusses the pros and cons of saving Xml streams in SQL Server (vs.
> tables
>> and rows).
>>
>> Thanks.
>>
>> "Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
>> news:ub1J5R7LFHA.1156@TK2MSFTNGP09.phx.gbl...
>> > Why are you storing XML documents in your database?
>> >
>> > Assuming your XML is less than 8000 characters, you can use OPENXML for
>> > this, but you'll have to process a single row at a time (which
>> > shouldn't
>> > be
>> > a problem, considering that you're doing this based on a client id).
> Read
>> > up on the OPENXML syntax in Books Online and check out this site if you
>> > need
>> > further help: http://www.sqlxml.org
>> >
>> > --
>> > Adam Machanic
>> > SQL Server MVP
>> > http://www.datamanipulation.net
>> > --
>> >
>> >
>> > "Robert Strickland" <strickra@rjsonline.net> wrote in message
>> > news:uuCgvX1LFHA.1956@TK2MSFTNGP15.phx.gbl...
>> >> I have a table with columns that contains client data (id, name, etc.)
>> >> and
>> >> one column that contains xml. I would like to bring back through a
> select
>> >> the client name and certain attribute values from the xml document
> based
>> > on
>> >> a passed client id. Can someone get me started or direct me to a site
>> >> with
>> >> this information?
>> >>
>> >> Thanks
>> >>
>> >>
>> >
>> >
>>
>>
>
>

"Michael Rys [MSFT]" <mrys@online.microsoft.com> wrote in message
news:uz4FEQdMFHA.3852@tk2msftngp13.phx.gbl...
> Let me correct this to some extend. While combining CLOB XML types with
> OpenXML and predicates is less efficient and complex, the addition of
native
> XML datatypes with XQuery/XPath support will move the trade-off balance.
If
> you most often access the XML data, but you may need to run predicates of
> parts of the document, you will want to either use computed columns that
> promote the values from the XML doc using XQuery expressions or use the

Can you define "most often"? I'm not trying to wordsmith you here -- I
really want to know, when does the tradeoff start making sense, and more
importantly, how should that be evaulated? I've read the XML Best Practices
white paper, but it is also very vague in this area. I would be very
interested in you or MS as a whole providing some suggestions on how to make
this determination rationally.

The computed columns are something I hadn't thought of! That's quite
interesting, especially given the persisted computed columns in SS2005...
Thanks for the tip!

--
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--

Making the trade-off needs to be evaluated based on the given data and query
load as well as what your access characteristics are. In general however,
the recommendation is to process data that is relational in form and is
being reshaped and aggregated independent of their hierarchical relationship
should be "shredded" on storage (potentially keeping the original document
around as well) while strucutured documents that are retrieved in toto
should be kept together to avoid the overhead of recomposition.

But in the end, we can give you only some guidelines. In the end it is the
programmers/system designers trade-off between complexity of programming
model, data evolution preparedness, update and retrieval characteristics,
performance requirements, form of the data (structured vs semistructured vs
markup) that will determine the final approach. Over time, good pattern and
practices will emerge and I am sure that we will provide more guidance. Some
of these are available in the performance and best practices whitepapers
that you mention below. Some will be added over time.

Best regards
Michael

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:e$oIsXhMFHA.3928@TK2MSFTNGP09.phx.gbl...
> "Michael Rys [MSFT]" <mrys@online.microsoft.com> wrote in message
> news:uz4FEQdMFHA.3852@tk2msftngp13.phx.gbl...
>> Let me correct this to some extend. While combining CLOB XML types with
>> OpenXML and predicates is less efficient and complex, the addition of
> native
>> XML datatypes with XQuery/XPath support will move the trade-off balance.
> If
>> you most often access the XML data, but you may need to run predicates of
>> parts of the document, you will want to either use computed columns that
>> promote the values from the XML doc using XQuery expressions or use the
>
> Can you define "most often"? I'm not trying to wordsmith you here -- I
> really want to know, when does the tradeoff start making sense, and more
> importantly, how should that be evaulated? I've read the XML Best
> Practices
> white paper, but it is also very vague in this area. I would be very
> interested in you or MS as a whole providing some suggestions on how to
> make
> this determination rationally.
>
> The computed columns are something I hadn't thought of! That's quite
> interesting, especially given the persisted computed columns in SS2005...
> Thanks for the tip!
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
>
>