SQL
Server 2000 XML Features
Host: Srinivas Sampath (MVP) & Vinod Kumar (MSDN India Community
Star)
July
25, 2003
Deepak_MS: Hi Everyone!
Deepak_MS: Good Evening and Welcome to the MSDN India Expert Chat
Deepak_MS: Today we have with us, Srinivas Sampath who is a Microsoft
SQL Server MVP and Vinod Kumar who is a Microsoft India Community
Star
Deepak_MS: and together they'll answer your queries regarding XML
features of SQL Server
Deepak_MS: The would also share valuable tips and tricks
Deepak_MS: So on to you guys..
Deepak_MS: Since we don't have any Qs yet, why don't u start with
a brief intro to sql
SriSamp_MVP: Hi Folks,
SriSamp_MVP: Welcome to the chat on SQL Server XML Features
jointly hosted by me (Srinivas Sampath) and Vinod Kumar.
SriSamp_MVP: In this chat, we will talk about the new
XML features of SQL Server that enable applications to work directly
with XML documents.
SriSamp_MVP: Earlier versions of SQL Server provided the
output of SELECT queries as relational tables (rows and columns).
SriSamp_MVP: But as the intenet expanded in its reach
and applications started talking to each other over the net and organizations
wanted to exchange data, XML became the defacto mode of communication.
SriSamp_MVP: Thus, it was required for applications to
transform the relational structure to an XML representation.
SriSamp_MVP: This presented some overhead since the translation
was to be done each time.
SriSamp_MVP: With SQL Server 2000, the T-SQL function
set has been expanded to include XML support, which now enables you
to fetch information from teh database directly as XML.
SriSamp_MVP: The new XML features in SQL Server are:
SriSamp_MVP: (1) The ability to access SQL Server through
a URL.
SriSamp_MVP: (2) The ability to retrieve and write XML
data: using SELECT ... FOR XML and OPENXML
SriSamp_MVP: (3) Support for XML-Data schemas and the
ability to specify XPath queries against these schemas.
SriSamp_MVP: (4) Bulk uploading of XML documents.
SriSamp_MVP: (5) Support for XML managed classes.
SriSamp_MVP: We will now walk through each of these features.
SriSamp_MVP: Note that we cannot provide complete code
samples or URL syntaxes to the various examples.
SriSamp_MVP: Thus, what we have done, written a set of
XML articles to which we will point to during the course of this chat
(as and when required). Wherever it is possible to type down samples,
we will...
SriSamp_MVP: This utility instructs IIS to create an association
between the new virtual directory and an instance of Microsoft SQL
Server.
SriSamp_MVP: Once defined, the URL can be used to:
SriSamp_MVP: (1) Directly access the database objects,
such as tables.
SriSamp_MVP: (2) Execute template files.
SriSamp_MVP: (3) Execute XPath queries.
SriSamp_MVP: To allow a template file, mapping schema
file, or a database object (such as a table or view) as part of the
URL, virtual names of type "template", "schema", and "dbobject" must
be created.
SriSamp_MVP: These virtual names allows the identification
of the file-type specified in the URL.
SriSamp_MVP: To see an example for the pubs database,
refer: http://www32.brinkster.com/srisamp/sqlArticles/article_2.htm
SriSamp_MVP: To execute a SELECT statement use: http://yourServer/vname?sql=SELECT%20*%20FROM%20authors%20FOR%20XML%20AUTO&root=root
SriSamp_MVP: The above statement will get the authors
table (pubs database) in an XML format surrounded by a tag called
"root"
SriSamp_MVP: You can also pass parameters in URL queries.
refer: http://www32.brinkster.com/srisamp/sqlArticles/article_17.htm
for an example.
SriSamp_MVP: Any questions here on URL queries?
Deepak_MS: (Shreekant): What is the function of MSDTC?
Vinod: It is Microsoft Distributed Transaction Coordinator
...
Vinod: In short it allows you to talk to multiple data
sources and co-ordinate the transaction for you in a single logical
unit.
Deepak_MS: (sqlserver): what about the performance, if you use pull
data as xml?
Vinod: There is not much performance hit in getting as
XML data from SQL Server.
Vinod: It does a pretty good job ...
Vinod: Try using RAW format as it is the fastest ...
Vinod: followed by the Auto format ...
Vinod: And the explicit format ...
SriSamp_MVP: Remember that the only performance might
be because of transferring large amounts of day (surrounded by tags)
over the wire...
Deepak_MS: (SND): Can we give batch commands in URLs?
SriSamp_MVP: If you mean multiple statements, yes, its
possible.
Deepak_MS: (Shreekant): How to generate XML from SQL for a Particular
DTT?
Vinod: If you mean to generate XML from a schema, then
it is possible to map the XML to a XSD mapping schema and get data
from SQL Server.
Deepak_MS: Ok folks, lets resume the intro, since I don't see more
questions..
SriSamp_MVP: One obvious problem that you see here is
that the URL exposes the SELECT statement and its quite easy for someone
to hack the URL.
SriSamp_MVP: A more secure way of accessing SQL Server
via URL is to use templates.
SriSamp_MVP: A template is an XML file that is stored
on the web server. This XML file contains the SQL query to execute
(along with parameters it any).
SriSamp_MVP: The URL now, only specifies the XML file
to execute like: http://yourServer/template/File.xml, which is more
secure.
SriSamp_MVP: Note that we use a virtual name of template
(created during the virtual directory creation) to indicate that the
XML file contains a SQL query to be executed.
SriSamp_MVP: The template XML contains a structure similar
to: SELECT
* FROM authors FOR XML AUTO
SriSamp_MVP: Sorry some special characters got changed
to smileys, but that not the syntax
SriSamp_MVP: The above is only the basic syntax. For an
example see: http://www32.brinkster.com/srisamp/sqlArticles/article_18.htm
and http://www32.brinkster.com/sqlvinod/articles/GeneratingSQLXMLTemplates.htm
SriSamp_MVP: - When you want to pass parameters, the template
file will have a
structure. For each parameter, you will have a node.
SriSamp_MVP: Note that the space is not there (between
sql and param). I've just put it there to avoid the smileys...
SriSamp_MVP: Now, your URL will become: http://yourServer/template/File.xml?paramName=value
SriSamp_MVP: For an example of using templates see: http://www32.brinkster.com/srisamp/sqlArticles/article_18.htm
SriSamp_MVP: Templates give you greater flexibility in
executing queries and returning results.
SriSamp_MVP: Any questions on URL and template queries
before we proceed to the next feature??
Deepak_MS: (Shreekant): suppose we are mapping database from IIS then
is still connected to database or connection is broken at the time
of execution of query?
SriSamp_MVP: The connection is cut the minute the query
finishes executing. It works very much like as ASP page requesting
data and then sending the results back.
Deepak_MS: (SND): does it work like connection pooling?
SriSamp_MVP: Connection pooling will be taken care of
by the SQLXML provider.
Deepak_MS: (binary_b4u): I would like to know how do I rollback in
SQL server from an webpage?
SriSamp_MVP: Remember that a web page represents a disconnected
scenario.
SriSamp_MVP: Thus, if you want to rollback your changes,
you need to be either connected, or store your data in temporary tables
and then update your database.
SriSamp_MVP: Done.
Deepak_MS: (talisma): is the communication between SQL Client and
server is made in XML lang?
SriSamp_MVP: No. Between a SQL Serve client and the server,
we use a native protocol called TDS (tabular data stream).
SriSamp_MVP: Hence the format is not XML, but the fastest
way to access SQL Server.
Deepak_MS: (SND): Can we generate templates dynamically to take care
of dynamic parameters or dynamic SQLs?
SriSamp_MVP: Incidentally, the .NET SQLClient provider
communicates via the TDS protocol.
SriSamp_MVP: Done.
Vinod: Yes we can generate a template dynamically ...
Vinod: But you would need to form the template before
making your connection to the SQL Server and then use the SQLXML command
object to pass the template and execute the same.
Deepak_MS: (Govind22): Most of the Time I am getting the Timeout Expired
Message from SQL server when running application , What will be the
Problem ?
Vinod: when does this happen? Is this when using the SQLXML
provider or when any XML data is retrieved?
Vinod: It would be more clear if you can explain about
the problem in detail ...
SriSamp_MVP: If you are getting timeout expired even when
connecting through SQLEM, you might be using the wrong net-library
for the client. Change it to TCP/IP.
Deepak_MS: (SND): Would you prefer this URL mechanism for handling
huge data? Or any alternate mechanism?
Vinod: URL based queries also run into a limitation of
1KB size limit when accessed.
Vinod: Hence HTTP POST has to be used to send the data.
Vinod: Restrict the POST data from the SQLXML 3.0 IIS
support configuration.
Vinod: If left unnoticed there can be malicious code that
can pump large data and cause DOS (Denial of service) in case of webservices.
Vinod: I think the concept is clear now ...
Deepak_MS: (SND): Will URL mechanism work when multiple database servers
are involved in the query?
Vinod: Yes they will access, provided you give the 3 part
name, and the user who is accessing the database has proper rights
...
Vinod: Done.
SriSamp_MVP: If its multiple servers, you need to use
a 4 part name (using linked servers).
Deepak_MS: (manisankar): XML is used to mark up the data from where
we can parse and displayed in the devices desired format. So what
is the similarity between sqlserver 2000 and xml in this respect?
SriSamp_MVP: There is no similarity here.
SriSamp_MVP: XML is used to markup data in a meaningful
format and you usually apply style-sheets to render them on various
devices.
SriSamp_MVP: What SQL Server only does is, to provide
you with facilities to render the data as XML.
SriSamp_MVP: The URL extensions to SQL Server will let
you apply stylesheets on this data.
Deepak_MS: (SND): But what will happen if another database server
is Oracle?
SriSamp_MVP: For Oracle databases, you need to create
a linked server and then specify the linked server name in the 4 part
name.
Deepak_MS: SriSamp the answer for the rollback is not justified ,
suppose i insert into one table and then in second table and now try
to rollback the process in the same connection will it possible?
SriSamp_MVP: This question is now different from the earlier
one (which talked about web pages).
SriSamp_MVP: Depending on where you are inserting your
data from, the rollback can be achieved in multiple ways.
SriSamp_MVP: First, if you are doing this logic from an
ASP page, you can use ASP page-level transactions to control the overall
behavior.
SriSamp_MVP: Second, if you are using COM+, the COM+ infrastructure
itself allows you to manage transactions.
SriSamp_MVP: Third, if you are using stored procedures,
you can use nested transactions to control the behavior.
Deepak_MS: (Shreekant): Can we store XML format Data in SQL? ?
Vinod: Yes ... Sort of. You can store it in a text column,
and then retrieve the same from SQL Server.
Vinod: You cannot treat this as an XML document at the
backend, but you have methods that convert XML data to relational
data at the SQL Server level using the OPENXML format.
Deepak_MS: (maisankar): so, I can store the entire content of xml
file(including tag and data) in database sqlserver200. Am I correct?
SriSamp_MVP: Yes, you can. TEXT columns have a limit of
2GB.
Deepak_MS: (Shreekant): My one question on XSL: can we use more than
one schema in one xsl file like Transform, xlink,xqueries
Vinod: You can map just one schema to the template file
and use the same.
Vinod: This is in context to using XML with schemas.
Vinod: Else you need to have different XSL templates for
a separate file.
Vinod: Get the XML file to the client and then use different
XSLs of your need the ASP layer, so that you can customize to your
needs ...
Deepak_MS: (manisankar): So if I have more data than 2gb,what I have
to do then?
SriSamp_MVP: Split it up into multiple TEXT fields, which
is a manual operation. Remember that posting such a huge data can
clog your network and the application.
SriSamp_MVP: The SELECT statement in SQL Server has been
expanded with a FOR XML clause that allows you to return query results
as XML documents.
SriSamp_MVP: FOR XML has various options. These are FOR
XML RAW | AUTO |EXPLICIT [,ELEMENTS] [,XMLDATA] [,BINARY BASE64]
SriSamp_MVP: FOR XML RAW gets you the data as attributse
enlosed within a standard "row" tag. This is the fastest option.
SriSamp_MVP: FOR XML AUTO gets you the data as attributes,
but enclosed with the table name as the tag. Also, joins are represented
as nested XML's.
SriSamp_MVP: FOR XML EXPLICIT gives you the greatest control
over the XML and is also the most complex to write and performance
intensive (compared to the other methods).
SriSamp_MVP: Instead of getting the output as attributes
specifying ELEMENTS gets you the data as as XML nodes. For example:
FOR XML AUTO, ELEMENTS. Each element tag will be the name of the column
(or alias)
SriSamp_MVP: Specifying XMLDATA prepends an XML schema
along with the data, which is used for XPath queries.
SriSamp_MVP: Refer to http://www32.brinkster.com/srisamp/sqlArticles/article_20.htm
and http://www32.brinkster.com/sqlvinod/articles/ForXML.htm for an
indepth treatment of FOR XML features.
SriSamp_MVP: EXPLICIT mode is used when you want to have
a mix of attributes and elements. This mode uses the concept of an
"universal table" that outlines the format of the XML that is to be
generated.
SriSamp_MVP: You can explicitly specify the tag name,
the parent-child relationships and whether you need an attribute or
an element.
SriSamp_MVP: The syntax of the EXPLICIT mode is quite
lengthy and involved. Thus, you can refer to http://www32.brinkster.com/srisamp/sqlArticles/article_21.htm
and http://www32.brinkster.com/sqlvinod/articles/ForXML.htm for an
insight into EXPLICIT.
SriSamp_MVP: Its quite interesting to work in this
SriSamp_MVP: SQL Server also allows you to load an XML
document and treat it as a relational table. This is a very powerful
feature...
SriSamp_MVP: To enable this, SQL Server has introduced
3 commands: sp_xml_preparedocument, sp_xml_removedocument and OPENXML.
SriSamp_MVP: sp_xml_preparedocument is used to load an
XML document into memory (using the MSXML parser) and return a handle
to it.
SriSamp_MVP: OPENXML is used to "transform" the XML into
a relational view. This command works by using the handle returned
by sp_xml_preparedocument.
SriSamp_MVP: To transform the XML into a relational view,
you need to specify an XPath to OPENXML, using which it creates the
view.
SriSamp_MVP: This is similar to using the selectSingleNodes
call of MSXML.
SriSamp_MVP: For example, consider this XML: Test
Author - ATest Author - B
SriSamp_MVP: If we want to get the two nodes, your
OPENXML will look like the following:
SriSamp_MVP: SELECT * FROM OPENXML (@hDoc, '/books/book',
2) WITH (author VARCHAR(100) './author')
SriSamp_MVP: This command will get the two rows. Note
that the WITH clause allows you to specify the structure of the relational
table.
SriSamp_MVP: Each column specified in this clause also
specifies the XML node that will supply data to it.
SriSamp_MVP: Its very important to provide the correct
XPath to OPENXML, otherwise your relational table might be wrong.
SriSamp_MVP: For a full treatment of OPENXML, refer to:
http://www32.brinkster.com/srisamp/sqlArticles/article_22.htm
SriSamp_MVP: Any questions here??
Deepak_MS: (manisankar) In the backend I can retrievethis xml data
and can render stylesheet to tha data.So what are facilities here
I am getting using Sql server as I can store this same data in other
database and perform the same functionality?
Vinod: I am not sure of other data sources and how they
function.
Vinod: In SQL Server we can store the same in a text field
and use the OPENXML feature discussed above, to transform the data
to a relational format and use the same as in your queries.
Vinod: You cannot use XML features extensively at the
database levels.
Deepak_MS: (SND): Can you give us real life examples of using XML
features in real world applications and how it was useful?
SriSamp_MVP: BizTalk is a great example.
SriSamp_MVP: It works with input XML's and transforms
them into other formats and is very useful for B2B scenarios.
SriSamp_MVP: Web services are another great example that
allows applications to work together over the net, and exchange data
in a seamless manner.
Deepak_MS: (manisankar): Suppose I store xml data in the database.Now
say me what facililities will sqlserver2000 will give me to handle
that data?If you clarify once more I wll oblige
SriSamp_MVP: Refer to the OPENXML features that were discussed
above along with the 2 new SP's.
SriSamp_MVP: OPENXML will let you treat the XML data as
a relational table and perform operations with it.
Deepak_MS: Hi Folks, unfortunately we are out of time for this week's
chat..
SriSamp_MVP: Folks, it was great experience to be on this
chat
SriSamp_MVP: Just to close off, you can post the unanswered
questions in the newsgroups or visit the site http://extremeexperts.com/expertchat/default.aspx
and http://www32.brinkster.com/srisamp for a list of XML articles.
