Logo

Navigation
  • Home
  • Services
    • ERP Solutions
    • Implementation Solutions
    • Support and Maintenance Solutions
    • Custom Solutions
    • Upgrade Solutions
    • Training and Mentoring
    • Web Solutions
    • Production Support
    • Architecture Designing
    • Independent Validation and Testing Services
    • Infrastructure Management
  • Expertise
    • Microsoft Development Expertise
    • Mobile Development
    • SQL Server Database and BI
    • SAP BI, SAP Hana, SAP BO
    • Oracle and BI
    • Oracle RAC
  • Technical Training
    • Learn Data Management
      • Business Intelligence
      • Data Mining
      • Data Modeling
      • Data Warehousing
      • Disaster Recovery
    • Learn Concepts
      • Application Development
      • Client Server
      • Cloud Computing Tutorials
      • Cluster Computing
      • CRM Tutorial
      • EDI Tutorials
      • ERP Tutorials
      • NLP
      • OOPS
      • Concepts
      • SOA Tutorial
      • Supply Chain
      • Technology Trends
      • UML
      • Virtualization
      • Web 2.0
    • Learn Java
      • JavaScript Tutorial
      • JSP Tutorials
      • J2EE
    • Learn Microsoft
      • MSAS
      • ASP.NET
      • ASP.NET 2.0
      • C Sharp
      • MS Project Training
      • Silverlight
      • SQL Server 2005
      • VB.NET 2005
    • Learn Networking
      • Networking
      • Wireless
    • Learn Oracle
      • Oracle 10g
      • PL/SQL
      • Oracle 11g Tutorials
      • Oracle 9i
      • Oracle Apps
    • Learn Programming
      • Ajax Tutorial
      • C Language
      • C++ Tutorials
      • CSS Tutorial
      • CSS3 Tutorial
      • JavaScript Tutorial
      • jQuery Tutorial
      • MainFrame
      • PHP Tutorial
      • VBScript Tutorial
      • XML Tutorial
    • Learn Software Testing
      • Software Testing Types
      • SQA
      • Testing
  • Career Training
    • Career Improvement
      • Career Articles
      • Certification Articles
      • Conflict Management
      • Core Skills
      • Decision Making
      • Entrepreneurship
      • Goal Setting
      • Life Skills
      • Performance Development
      • Personal Excellence
      • Personality Development
      • Problem Solving
      • Relationship Management
      • Self Confidence
      • Self Supervision
      • Social Networking
      • Strategic Planning
      • Time Management
    • Education Help
      • Career Tracks
      • Essay Writing
      • Internship Tips
      • Online Education
      • Scholarships
      • Student Loans
    • Managerial Skills
      • Business Communication
      • Business Networking
      • Facilitator Skills
      • Managing Change
      • Marketing Management
      • Meeting Management
      • Process Management
      • Project Management
      • Project Management Life Cycle
      • Project Management Process
      • Project Risk Management
      • Relationship Management
      • Task Management
      • Team Building
      • Virtual Team Management
    • Essential Life Skills
      • Anger Management
      • Anxiety Management
      • Attitude Development
      • Coaching and Mentoring
      • Emotional Intelligence
      • Stress Management
      • Positive Thinking
    • Communication Skills
      • Conversation Skills
      • Cross Culture Competence
      • English Vocabulary
      • Listening Skills
      • Public Speaking Skills
      • Questioning Skills
    • Soft Skills
      • Assertive Skills
      • Influence Skills
      • Leadership Skills
      • Memory Skills
      • People Skills
      • Presentation Skills
    • Finding a Job
      • Etiquette Tips
      • Group Discussions
      • HR Interviews
      • Interview Notes
      • Job Search Tips
      • Resume Tips
      • Sample Resumes
 

XML SQL Server

By Exforsys | on July 23, 2007 |
XML Tutorial

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.

« « NLP Metaphors
Using NLP for Team Building » »

Author Description

Avatar

Editorial Team at Exforsys is a team of IT Consulting and Training team led by Chandra Vennapoosa.

Free Training

RSSSubscribe 394 Followers
  • Popular
  • Recent
  • XML Remote Calling Procedure

    July 19, 2007 - 0 Comment
  • XML – Elements in Document Type Definitions (DTD)

    June 14, 2006 - 0 Comment
  • XML Security

    July 21, 2007 - 0 Comment
  • Working with XML in Flash

    August 30, 2007 - 0 Comment
  • XML and Service Oriented Architecture

    July 25, 2007 - 0 Comment
  • Working with XML in Python

    September 2, 2007 - 0 Comment
  • XML Spy

    August 24, 2007 - 0 Comment
  • Working with XML in Perl

    September 6, 2007 - 0 Comment
  • Using XML with Microsoft Excel

    August 24, 2007 - 0 Comment
  • Working with XML in C

    September 7, 2007 - 0 Comment
  • Working with XML in C

    September 7, 2007 - 0 Comment
  • Working with XML in Perl

    September 6, 2007 - 0 Comment
  • Working with XML in Python

    September 2, 2007 - 0 Comment
  • Working with XML in Flash

    August 30, 2007 - 0 Comment
  • Working with XML in Oracle

    August 30, 2007 - 0 Comment
  • Working with XML in Visual Basic

    August 28, 2007 - 0 Comment
  • Using XML with Microsoft Excel

    August 24, 2007 - 0 Comment
  • XML Spy

    August 24, 2007 - 0 Comment
  • XML and Service Oriented Architecture

    July 25, 2007 - 0 Comment
  • XML Security

    July 21, 2007 - 0 Comment

Exforsys e-Newsletter

ebook
 

Related Articles

  • Working with XML in C
  • Working with XML in Perl
  • Working with XML in Python
  • Working with XML in Flash
  • Working with XML in Oracle

Latest Articles

  • Project Management Techniques
  • Product Development Best Practices
  • Importance of Quality Data Management
  • How to Maximize Quality Assurance
  • Utilizing Effective Quality Assurance Strategies
  • Sitemap
  • Privacy Policy
  • DMCA
  • Trademark Information
  • Contact Us
© 2023. All Rights Reserved.IT Training and Consulting
This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.AcceptReject Read More
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT