XML SQL Server
The Extensible Markup Language was introduced in the SQL server because of the clause to SELECT. Now XML has been well integrated into the Microsoft SQL server in the Relational Database Management System or the RDBMS which can help create futuristic web programs and databases.
Why introduce XML into a SQL server?
A business to business portal or B2B, business to consumer portal or B2C, or Intrabusiness networks need to exchange a lot of information and this exchange of information can be easily enabled with the help of XML. You could integrate ERP (Enterprise Resource Planning) and CRM (Customer Relationship Management) functionalities by using XML with the RDBMS.
Some of the features of XML in SQL server are OpenXML, HTTP, OLE DB, ADO access, XML modes, XML views, and SELECT statement options. Data can be accessed in three ways using HTTP and they are SQL Statements entered in the URL (Universal Resource Locator), Templates, and through HTML post event integration. Data is retrieved using the XML modes. There are three types of XML modes and they are RAW, Auto and Explicit.
The RAW uses the methodology of taking each row and converting the result into an XML document. The AUTO uses the method of returning the queries in a XML tree format. The Explicit mode simple defines the shape of the XML tree and specifies the way the queries can be written.
XML Data schema provides ML view on the database using the annotations derived from the SQL server. These annotations also appear within the XML schema to identify a two way mapping system for the XML data from tables to columns and then columns to tables back. The name of the annotations remain the same fro the XML data schema, to the database and the column name. These annotations are also used to define a hierarchical relationship between data.
XPath queries
XPath works with the XML view technology so that the data that is being retrieved can be in the form of an XML document.
OPENXML
OPENXML enables a way to access XML Data and present it in the relational database. It creates an environment for the database to interact with the XML data within the SQL by transferring the data to tables. Statements such as SELECT, UPDATE, DELETE and INSERT can be used along with OPENXML.
Ole DB AND ADO Access
The SQL Ole DB is an extension for the Xpath and XML .A new interface has been set up to pass the commands to OLE DB to be processed called the Icommandstream. The OLE DB has also been extended so that it can process queries so that it can return results in XML by using the XML views in XML schemas.
XML features mainly serve users for web and database development. While developing XML documents on a SQL platform it is not a must that the users have to learn or well versed in XML. One of the important factors to be considered is the compatibility or the backward-compatibility for XML in SQL server. Since the integration of XML data type the generation of data is directly in XML format.
Microsoft SQL server and the SQL XML are known to create excellent and efficient XML data management techniques. The XML centric approach has the capability of processing loads of XML data using the annotations that were defined using the XML view and XSD. The data can be divided into two divisions, data modeling and data usage.
Data Modeling:
Whether a user has to choose XML View technology or XML storage depends on lot of varied factors. For a highly structured for of data that has familiar schema to encode the relational data model will work the best for data storage purposes. When the data is unstructured or semi structured or flexible, it can be modeled to fit the needs. However SQL server provides a lot of tools to model data. When there is a need for a model which is platform independent and easily transferable then XML is the bets choice in such circumstances. When the data is stored in XML the engine checks for the authenticity of the data and if it is well structured to support fine grained data for queries and updating.
Why should data be stored in XML?
If there is a need for backup, retrieving, recovery or replication, any of the administrative functions for the database server to manage data in XML then data should be stored in XML. If there is a lot of fine grained data in your documents and you need them to be extracted into a XML document while creating a whole new document then data has to be stored in XML. When you have relational data on one hand and SQL data on the other then you need XML data to create a conversation in terms of data. If you want your data to be highly organized then it has to store in XML format. When you need accessibility to SOAP, OLE DB, ADO.NET, for the XML data then data has to be stored in XML format. If your data doesn’t have to be any of these and is of the non XML type then it is not needed to store data in XML format.
XML storage Options in SQL Server.
When storing within XML document, the data is stored within the XML document in a hierarchical form. Actually the info set for the XML data is stored. The mapping is done through AXSD, using annotated schema where the data in XML is broken down into tables preserving the data in a hierarchical format where as the schema of the remaining document is left unchanged. For large object storage an exact copy of the data is stored for either back up or recovery.
There are two technologies within XML that can be used. One is the XML and another XML view. A lot of factors decide what technology has to be used for data storage. The first among these is the storage option, which depends on whether the data is large or small. Then Query capabilities are the second factor which will determine the storage option. It all depends on the nature of query and the type of data which will decide. Indexing XML data also plays a pivotal role as the process speed improves when the data is indexed in XML. The data modification capabilities also are essential for certain types of data you ate dealing with. The language support for data modification should be feasible at the same time. Last and not the least is the schema support. The schema may be able to map the XML document but the Xml document being a schema document also matters. These choices function individually and it all depends on the data to decide which will suit the best.
The XML view technology can be used along with the Xpath technology to view SQL queries in the table. Updates can also be changed in the tables itself. XML view technology is useful when there is XML programming using XML view. There is a schema for an XML document and when the data doesn’t have to be organized. If you need to query the data using the XML Path and you need bulk data to be redistributed into tables for immediate access of the XML view?
How is XML data used for data modeling?
SQL server employs the same techniques as the XML to lock the XML and Non XML data. The XML data is stored in the XML documents. To achieve a design of good quality there should correlation and correspondence between the data model characteristics and the locking characteristics.
Another point to consider is the data type itself whether it is typed or untyped or it can also be constrained. Whichever type of data it is one thing is ensured that the data is well structured. Unstructured data is rejected and not stored. The data has to be well formed to enter the database. For different circumstances different types of data can be used, like for example when you do not have a schema then untyped data can be used or when there is no need for the server to validate the data then in this process also the untyped data can be used. But if you do have a schema and there is a need for the server to authenticate the data then it is a good idea to use the typed data. While these data types do have limitations like certain kinds of data cannot be expressed in any of these types for example business rules. In these cases the constrained data is used in combination with typed data and untyped data.
So different circumstances and different scenarios decide the storage of data. If all these factors collectively are well coordinated with each other then the right decision can be made for a XML SQL server to function at its utmost efficiency level in terms of performance.
[catlist id=166].