SQL
Server 2000 XML Features - Part II
Host: Srinivas Sampath (MVP) & Vinod Kumar (MSDN India Community
Star)
August
01, 2003
Deepak_MS: Hi Friends...
Deepak_MS: Welcome to the MSDN India Expert chat...
Deepak_MS: We have Vinod and Srinivas with us again to discuss XML
features of SQL Server 2000
Deepak_MS: Since we don't have any questions so far, why don't we
start with introduction - just where we left last time
SriSamp_MVP:Hi Folks,
SriSamp_MVP:Welcome to Part-II of SQL Server XML Features.
SriSamp_MVP:In the first part, we saw the following XML
features of SQL Server:
SriSamp_MVP:(1) Accessing SQL Server via an URL.
SriSamp_MVP:(2) Executing templates queries from a URL.
SriSamp_MVP:(3) Reading and Writing XML using OPENXML,
sp_xml_preparedocument and sp_xml_removedocument.
SriSamp_MVP:(4) FOR XML extensions to SQL Server SELECT
clause.
SriSamp_MVP:Just to recap a bit here, the URL access feature
allows you to access SQL Server via HTTP and submit queries which
are executed.
SriSamp_MVP:The output of these queries are returned as
XML documents on which XSL stylesheets can be applied to format the
output.
SriSamp_MVP:Using OPENXML, you can convert an XML document
into a relational view that can then be used to update other tables.
SriSamp_MVP:Note that however, SQL Server does not allow
you to update the XML back in-place. The only option is to reconstruct
the XML again.
SriSamp_MVP:Finally, the FOR XML extensions allow you
to get the output of a SELECT statement as XML. The options supported
are RAW, AUTO and EXPLICIT.
SriSamp_MVP:For a list of XML articles based on what was
discussed in Part-I, visit http://extremeexperts.com/ExpertChat/SQL/XMLCapabilities.aspx
and http://www32.brinkster.com/srisamp.
SriSamp_MVP:Bangloreans had a bit of an insight into these
features in the BDOTNET UG meeting held on July 25th.
SriSamp_MVP:Today, we are going to continue on the XML
feature exploration of SQL Server and will talk about creating XML
views off relational tables.
SriSamp_MVP:This feature is made possible by SQLXML and
its integration with XSD schemas.
SriSamp_MVP:We will also see how to use the .NET managed
classes for SQLXML to work with XML data from SQL Server.
Vinodk_CommStar: Moving ahead with the next set of features
we have in queue is the support for XML Schema Definition (XSD) language.
Vinodk_CommStar: In SQLXML 3.0, XSDs have been also supported.
i.e. You can create XML views of relational data by using the XML
XSDs.
Vinodk_CommStar: These views can then be queried by using
XML Path language (XPath) queries.
Vinodk_CommStar: This is similar to creating views by
using CREATE VIEW statements and then specifying SQL queries where
clause against the view.
Vinodk_CommStar: Hence these are also called as XMLViews.
Vinodk_CommStar: An XML schema describes the structure
of an XML document and the various constraints.
Vinodk_CommStar: You specify XPath queries against the
schema for filtering and getting data from SQL Server.
Vinodk_CommStar: In the XPath Template document we would
refer to the XSD schema which would apply filter to retrieve restricted
data.
Vinodk_CommStar: Apart from XPath queries the XQuery method
of access is also given ...
Vinodk_CommStar: This feature is to be released and bundled
with the next major version of SQLXML.
Vinodk_CommStar: To summarize, you can use an XSD schema
with annotations that describe the mapping to a database, query the
database, and return the results in the form of an XML document.
Vinodk_CommStar: Some useful references in this context
are:
Vinodk_CommStar: XML Schema Part0: http://www.w3.org/TR/xmlschema-0/
Vinodk_CommStar: Using Simple XSDs: http://www.ExtremeExperts.com/sql/articles/UsingXSDs.aspx
Vinodk_CommStar: and Generating Simple XSDs using VS .NET:
http://www.ExtremeExperts.com/sql/articles/GeneratingXSDs.aspx
Vinodk_CommStar: Well moving to the next feature. Updategrams
...
Vinodk_CommStar: You can modify (insert, update, or delete)
data in a database in SQL Server 2000 from an existing XML document
by using an updategram or the OPENXML Transact-SQL function.
Vinodk_CommStar: Since OPENXML has been dealt in detail
earlier, we move to Updategrams.
Vinodk_CommStar: Updategrams give you the capability to
perform DML operation with SQL Server using simple XML schema directives.
Vinodk_CommStar: You can do simple to complex operations
using updategrams. This can be considered synonymous to Diffgrams
in DataSets.
Vinodk_CommStar: The keywords in an updategram, such as
, , and ,
Vinodk_CommStar: exist in the (urn: schemas - microsoft
- com: xml - updategram) namespace.
Vinodk_CommStar: The following definitions describe the
role of various blocks in updategram:
Vinodk_CommStar::Identifies the existing state
(also referred to as "the before state") of the record instance.
Vinodk_CommStar::Identifies the new state to which
data is to be changed.
Vinodk_CommStar:: To synchronize the operation.
Can be considered as a logical transaction boundary.
Vinodk_CommStar: It can contain multiple before and after
blocks.
Vinodk_CommStar: For instance ...
Vinodk_CommStar: If you do not have an "before state"
and define the after block alone it means you plan to Insert this
data.
Vinodk_CommStar: And if you have definitions of before
and after blocks you plan to Update the record.
Vinodk_CommStar: And if there is a before block with no
After block you intend to delete the records.
Vinodk_CommStar: And then leave the rest to the SQLXML
engine to generate the DML statements to execute for you.
Vinodk_CommStar: For a simple use of Updategrams refer
to: http://www.ExtremeExperts.com/sql/articles/UsingUpdateGrams.aspx
Deepak_MS: (mpm): Can you give some examples for the application of
the XML features?
Vinodk_CommStar: XML can be a very effective way to represent
business data for transfer between different systems.
Vinodk_CommStar: Take for instance B2C solutions ...
Vinodk_CommStar: If I were to sell online retailers selling
goods and services through a Web site.
Vinodk_CommStar: XML can be an extremely effective way
to pass data from the database to the Web application.
Vinodk_CommStar: Use XSL to easily transform the XML data
into HTML for display.
Vinodk_CommStar: This approach is usually more efficient
than retrieving data as a rowset and writing presentation logic
Vinodk_CommStar: in a Web page script or component to
render the data.
Vinodk_CommStar: Or take for instance B2B Solutions ...
Vinodk_CommStar: I would like to integration business
processes across my various trading partners.
Vinodk_CommStar: XML provides an ideal way to describe
these business documents for exchange across the Internet.
Vinodk_CommStar: And using XML schemas I can define the
XML representation of the business documents,
Vinodk_CommStar: allowing trading partners to agree on
a format for the data being exchanged.
Vinodk_CommStar: And as above I can use XSL for transformation
in the presentation layer.
Vinodk_CommStar: Hence you can see that the advantages
of XML as a transport mechanism are numerous!
Vinodk_CommStar: Done.
Deepak_MS: (AndyC): Can updategrams be used to execute stored procedures?
SriSamp_MVP:No, since stored procedures execute as a single
unit, you cannot specify "before" and "after" states.
Deepak_MS: (Shreekant): Can we use store procedure with schema and
template?
SriSamp_MVP:Yes, you can execute stored procedures with
templates.
Deepak_MS: (AndyC): How does the performance of an updategram compare
to things like ADO commands, BCP and DTS?
Vinodk_CommStar: We cannot make a one to one comparison
with using BCP and DTS with updategrams ...
Vinodk_CommStar: One is used for bulk uploading feature
...
Vinodk_CommStar: while other is used for short insert/update/delete
operation ...
Vinodk_CommStar: But SQLXML does have a bulk uploading
...
Vinodk_CommStar: feature which we will take next as our
feature ...
Deepak_MS: (Shreekant): as we are using quesries "select * from employee
for auto xml node=node", so we can have use store procedure like "exec
sp_test () for xml auto node=node"
SriSamp_MVP:If you are using URL queries, you can specify
FOR XML AUTO in the URL.
SriSamp_MVP:But, if you are executing stored procedures
in the URL, then you cannot specify FOR XML in the URL.
SriSamp_MVP:It has to be specfied in the stored procedure
itself.
SriSamp_MVP:Or in the latest version of SQLXML (3.0),
you can specify this in the template, but with the client-side formatting
turned on.
Vinodk_CommStar: Moving to the next feature ...
Vinodk_CommStar: Bulk loading data from different sources
into a SQL Server 2000 database is a common programming and application
requirement.
Vinodk_CommStar: The bcp utility can be used to bulk load
data from text files,
Vinodk_CommStar: and SQLXML Bulk Load is pretty much an
equivalent to bulk load data into a SQL Server database from a source
XML document.
Vinodk_CommStar: SQLXML Bulk Load is actually a programmable
COM component that you can use in your
Vinodk_CommStar: COM-enabled programming languages to
write code that can take data from an XML document and bulk load the
data
Vinodk_CommStar: into a SQL Server 2000 database.
Vinodk_CommStar: You can use an annotated XDR mapping
schema to map the XML elements and attributes contained in the document.
Vinodk_CommStar: Hence Bulk Load utility ...
Vinodk_CommStar: picks up the XML document, and then it's
going to use the information contained
Vinodk_CommStar: in the annotated mapping schema to identify
which SQL Server tables and columns the elements and attributes correspond
to
Vinodk_CommStar: The Bulk Load component is going to generate
the T-SQL statements.
Vinodk_CommStar: Related links on this feature ...
Vinodk_CommStar: Using XML Bulk Upload in SQLXML: http://www.ExtremeExperts.com/sql/articles/BulkUpload.aspx
Vinodk_CommStar: Examples of Bulk Loading XML Documents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp
Vinodk_CommStar: Guidelines and Limitations of XML Bulk
Load: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp
Vinodk_CommStar: Any questions ?
Deepak_MS: (mpm): Does BizTalk integrate with SQLXML?
SriSamp_MVP:No, not in the current version.
SriSamp_MVP:But you can write a custom component executed
via BizTalk orchestration that uses the SQLXML objects inside it.
SriSamp_MVP:For example, a COM component can execute a
URL query against SQL Server and get the result. This COM component
can be part of a BizTalk orchestration.
SriSamp_MVP:Done.
Deepak_MS: (AndyC): How does the record for record performance compair
to BCP and DTS?
Vinodk_CommStar : Well, this is a nice question ...
Vinodk_CommStar : Understand that BCP and DTS donot read XML files
directly ...
Vinodk_CommStar : Hence if you were to use DTS you have to write an
ActiveX object ...
Vinodk_CommStar : and invoke the DOM document and load the file to
start the uploading ...
Vinodk_CommStar : But in SQLXML this is done behind the scenes for
you ...
Vinodk_CommStar : And adding to this ...
Vinodk_CommStar : in the VB script you can use the SQLXML Bulk load
...
Vinodk_CommStar : to do the uploading process ...
Vinodk_CommStar : hence you can see that XML uploading is made easy
using the SQLXML Bulk loading capabilities ...
Vinodk_CommStar: The logging mechanism is also good and
you can infer the schema automatically while you are uploading dynamically
...
Vinodk_CommStar: The next feature of SQLXML is the provision
of the managed class. This was introduced in 2.0 version itself.
Vinodk_CommStar: SQLXML Managed Classes exposes the functionality
of SQLXML 3.0 inside the Microsoft .NET Framework.
Vinodk_CommStar: You have the SQLXML Command, parameter
and the adapter object exposed.
Vinodk_CommStar: Support for using SOAP calls to create
a Webservice from a Stored Procedure was also introduced.
Vinodk_CommStar: Look at: http://www.microsoft.com/india/msdn/articles/ExposingSPs.aspx
for how we can achieve this.
Vinodk_CommStar: You can take a look at http://www.ExtremeExperts.com/sql/articles/ManagedSQLXML.aspx
for some examples to use managed class and managed providers...
Vinodk_CommStar: Examples of Bulk Loading XML Documents
are covered extensively at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp
Deepak_MS: (mpm): Exposing SQL queries through URL - Isn't it a security
issue?
SriSamp_MVP:Yes, it is, since you can execute any query.
SriSamp_MVP:But you do have some level of security in
URL based queries since it depends on the context of the user connecting
to SQL Server.
SriSamp_MVP:Whatever this context can do, the query can
do.
SriSamp_MVP:The best way to protect malicious queries
is to allow only template based queries.
SriSamp_MVP:Since all that is exposed is the name of the
template, you cannot execute any ad-hoc SQL.
SriSamp_MVP:Here to, the template is restricted to the
permissions of the context connecting to SQL Server.
Vinodk_CommStar: Giving you some guidelines on using SQLXML...
Vinodk_CommStar: Avoid URL based query access as they
raise security concerns as said above ...
Vinodk_CommStar: For Virtual directory access as Windows
authentication rather than Basic authentication as it can pass
Vinodk_CommStar: the password as clear text on the network
wire.
Vinodk_CommStar: Use template based and Schema based approaches.
Vinodk_CommStar: These are safe and can be cached to maximize
performance.
Vinodk_CommStar: 2. Avoid using default mappings as this
can potentially allow
Vinodk_CommStar: the end-user to understand your data
structure.
Vinodk_CommStar: 3. Do not create your updategrams at
the ASP dynamically. This can easily be hacked.
Vinodk_CommStar: Hence use template based updategrams.
Vinodk_CommStar: 4. Try using aliases for the table names
when using FORXML clause,
Vinodk_CommStar: as they do not expose the underlying
data structures.
Vinodk_CommStar: When you add stored procedures, user-defined
functions (UDFs), and templates
Vinodk_CommStar: to the configuration of the soap virtual
name, it is recommended that you provide SOAP method names different
from
Vinodk_CommStar: their corresponding stored procedure,
UDF, or template names. This prevents users from knowing the actual
stored procedure,
Vinodk_CommStar: UDF, or template names.
Deepak_MS: (Shreekant): Is bulkload is store data in string or array?
Vinodk_CommStar: The bulk load feature is stored as corresponding
datatypes as we can infer the schema during the upload process or
we can give an XSD schema to do this.
Vinodk_CommStar: mapping explicitly during the bulk upload
process
Vinodk_CommStar: Just to summarize the features of SQLXML
3.0 ...
Vinodk_CommStar: URL Access: Removed the quotation marks
to denote a parameter in the URL.
Vinodk_CommStar: Can define the character set reaching
the server like _charset_=UTF-16
Vinodk_CommStar: Client-Side-XML attribute added.
Vinodk_CommStar: This enables using to form the XML at
the client side ...
Vinodk_CommStar: NullValue Attribute added to define NULL
getting passed.
Vinodk_CommStar: Is-xml attribute introduced to denote
a XML fragment getting passed. this is while using the XSD schemas
and the XML templates ...
Vinodk_CommStar: Templates, XSL can be cached at the Web
Server level. Another cool feature for performance.
Vinodk_CommStar: More data types have got added to XDR
schemas even thought they are present for backward compatibility only.
Vinodk_CommStar: Specify a mapping schema for the XPath
Queries.
Vinodk_CommStar: Web Service SOAP support from this version
... ...
Vinodk_CommStar: Convertor from XDR to XSD using (cvtschema)
utility ...
Vinodk_CommStar: Lets look at some of the useful references
for this presentation ...
Vinodk_CommStar: SQL Server 2000 XML Overview: http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/evaluate/featfunc/xmlsql.asp
Vinodk_CommStar: Examples of Bulk Loading XML Documents:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_6bos.asp
Vinodk_CommStar: SQLXML and XML Mapping Technologies:
http://msdn.microsoft.com/nhp/Default.asp?contentid=28001300
Vinodk_CommStar: this includes all the SQLXML documentation
online ...
Vinodk_CommStar: Guidelines for Using the FOR XML Clause:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/xmlsql/ac_openxml_0alh.asp
