Handling XML data in SQL Server

Extensible Markup Language (XML) has been widely adopted as a platform-independent format for data representation.

Before getting deep into XML, a little introduction to the structure of XML.

Sample XML fragment:
<?xml version = "1.0" encoding = "UTF-16"?>
<!—Student Information -->


In the above sample XML fragment , 

<Studentinfo>        is the root node
<Student>       is the element node,so in the above XML fragment,we havee 3 element nodes.

 <StudentID>   ,
 <StudentName> ,
 <CourseID> ..  are the attribute nodes

1,sathya,1,.. are the attribute values.

<Student> is the start tag &   </Student> is the end tag of the element node.
<Studentinfo> is the start tag & </Studentinfo> is the end tag of root node.

To put it simple in database terminologies,consider
root node as the database name,element node as the table name,attribute node as column name & attribute values as column values.

<!—Student Information -->  are XML comments denoted by <!-- and --> delimiters

<?xml version = "1.0" encoding = "UTF-16"?> are XML processing instructions  marked by <? and ?> delimiters.

A processing instruction is a means to provide additional metadata to a processing application.

 FOR XML clause:

For getting relational data in the form of XML, FOR XML clause was introduced.

Using FOR XML clause,we can represent relational data in the form of XML in two ways:
 1.)    Attribute Centric
2.)    Element Centric

/*******************************Sample data ***********************************/

Let us create sample datas for all below XML examples:

SSIS - Merge Transformation

As stated on MSDN BOL :

The Merge transformation combines two sorted datasets into a single dataset .

By including the Merge transformation in a data flow, you can perform the following tasks:
  • Merge data from two data sources, such as tables and files.
  • Create complex datasets by nesting Merge transformations.
  • Remerge rows after correcting errors in the data.

In this article , let us see an example for Merge Transformation .

SSIS - WMI Event Watcher Task

As Stated here , we can use the WMI Event Watcher task for the following purposes:

  • Wait for notification that files have been added to a folder and then initiate the processing of the file.
  • Run a package that deletes files when the available memory on a server drops lower than a specified percentage.
  • Watch for installation of an application, and then run a package that uses the application.

Getting Started with SQL Server Analysis Services

What is a Data warehouse ?

What is Dimensional Fact Model ?

Star schema :

SQL Server - FOR XML clause can be used within CTE

In SQL Server  - FOR XML clause can be used within CTE . But it is stated in MSDN BOL as

The following clauses cannot be used in the CTE_query_definition:
  • ORDER BY (except when a TOP clause is specified)
  • INTO
  • OPTION clause with query hints

To make it more clear , I raised a question on MSDN T-SQL forum - http://social.technet.microsoft.com/Forums/en-US/1b5d3c1a-0083-4f47-b6b0-35f86cdc2291/for-xml-clause-with-cte?forum=transactsql

Here is the Connect case [Feedback]  for change in MSDN BOL documentation .