Search

Handling XML data in SQL Server



XML :
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 -->

<Studentinfo>
  <Student>
    <StudentID>1</StudentID>
    <StudentName>Sathya</StudentName>
    <CourseID>1</CourseID>
  </Student>
  <Student>
    <StudentID>2</StudentID>
    <StudentName>Deepak</StudentName>
    <CourseID>2</CourseID>
  </Student>
  <Student>
    <StudentID>3</StudentID>
    <StudentName>sathish</StudentName>
    <CourseID>3</CourseID>
  </Student>
</Studentinfo>




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.
Similarly,
<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
  • FOR XML
  • FOR BROWSE

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 .