XML IN SQL SERVER

1) MASTER

   CONTAINS THE META DATA (DATA ABOUT DATA) OF SQL SERVER  

2) MODEL (IT IS ACTS A LIKE A TEMPLATE FOR NEWLY CREATED DATABASES)
   WHATEVER YOU PLACE IN MODEL DATABASE IT IS REPLICATED TO NEWLY CREATED DATABSES

3) MSDB
   USED BY ADMINS (WHICH CONTAINS BACKUP AND RESTORE INFORAMTION
   SCHEDULED JOBS INFORMATION)

4) TEMPDB (TEMPLORARY DB) CONTAINS ALL TEMP TABLES, VARIABLES AND STORED PROCEDURES
   IF YOU RESTART SQL SERVER EVERY TIME IT CREATES A NEW INSTANCE OF TEMPDB



hOW TO FILL A TABLE FROM AN EXISTING TABLE
INSERT INTO DUMMY SELECT SNO FROM SALES

========================================================================

XML is the new data type introduced in SQL Server 2005, upto sql server 2000 varchar data type itself is used for storing XML data.

Drawbacks of this are once, xml data is stored into database, it will be treated as plain text and not as xml. To overcome this problem, in sql server 2005 xml data types is introduced. When a column is declared of type xml then it will automatically get some functions to manipulate XML data.

OPENXML provides a row set view over an XML document. Because OPENXML is a row set provider, OPENXML can be used in Transact-SQL statements in which row set providers such as a table, view, or the OPENROWSET function can appear.

COMMON XML TYPES

ATTRIBUTE SPECIFIC

          <Employees>

                   <Employee Eno="101" Ename ="Ravi" Salary="45000"/>

                   <Employee Eno="102" Ename ="Gopi" Salary="35000"/>

                   <Employee Eno="103" Ename ="Afroz" Salary="30000"/>

          </Employees>

ELEMENT SPECIFIC

          <Employees>

                   <Employee>

                             <Eno>101</Eno>

                             <Ename>Ravi</Ename>

                             <Salary>45000</Salary>

                   </Employee>

                   <Employee>

                             <Eno>102</Eno>

                             <Ename>Gopi</Ename>

                             <Salary>35000</Salary>

                   </Employee>

                   <Employee>

                             <Eno>103</Eno>

                             <Ename>Afroz</Ename>

                             <Salary>30000</Salary>

                   </Employee>

          </Employees>

DECLARE @xmlHandler int  

exec sp_xml_preparedocument @xmlHandler output, @MyData

INSERT INTO dummy

SELECT * FROM OPENXML(@xmlHandler,'Employees/Employee',2) WITH (Eno int, Ename varchar(100), Salary int)

exec sp_xml_removedocument @xmlHandler

OPENXML(): Converts xml data into rowset view.

Function Related to XML Data Types

Modify()

Is used to insert New elements into xml data, modify existing elements and delete the elements from xml data.

Examples:

create table employe1 (rowid int primary key identity(1001,1) ,empdata xml)

insert employe1(empdata) values ('<employee id="1">

<firstname>Ramesh </firstname>

<lastname>fasfs</lastname>

</employee>')

select * from employe1

Functions related to xml data types

Modify()

Modify function is used to insert new elements into xml data, modify existing elements and delete the elements from xml data.

Example:

The following example inserts another employee element next to the first employee

update employe1 set empdata.modify

('insert <employee id="2">

<firstname>sunil</firstname>

<lastname>kanth</lastname>

</employee>

after(employee)[1]') where rowid=1001

The following example inserts the job element next to last name of the first employee.

update employe1 set empdata.modify

('insert

<job>manager</job>

after(employee)[lastname][1]') where rowid=1001