SQL Server To XML

SQL have a lot of  functionality to handle XML file. SQL server can 
generate , parse XML data.A wide range of XML handling is possible in Sql Server.

--For example purpose , we have created a table and inserted data---
 

CREATE TABLE STUDENT

(

    ID INT,

    Roll INT,

    RegNo INT,

    Name VARCHAR(500)

)

INSERT INTO STUDENT

(

ID,

Roll,

RegNo,

Name

)

SELECT 1,10,123,'Student 1'

UNION

SELECT 1,11,124,'Student 2'

UNION

SELECT 1,12,125,'Student 3'

UNION

SELECT 1,13,126,'Student 4'

UNION

SELECT 1,14,127,'Student 5'

UNION

SELECT 1,15,128,'Student 6'

UNION

SELECT 1,16,129,'Student 7'

UNION

SELECT 1,17,130,'Student 8'

---------------------------------------------------------------------


1)Generate XML
   To generate XML from query, for that you have to just write "FOR  XML RAW"  in your query. Here is the syntax of  for XML. 

SELECT COL1,COL2,COL3.. FROM TABLE_NAME FOR XML RAW;

Now we are executing a query with "FOR XML RAW", that will return 
a hyperlink

SELECT * FROM STUDENT FOR XML RAW;

Now click on the output ,a new sql window will open with XML Data.

<row ID="1" Roll="10" RegNo="123" Name="Student 1" />

<row ID="1" Roll="11" RegNo="124" Name="Student 2" />

<row ID="1" Roll="12" RegNo="125" Name="Student 3" />

<row ID="1" Roll="13" RegNo="126" Name="Student 4" />

<row ID="1" Roll="14" RegNo="127" Name="Student 5" />

<row ID="1" Roll="15" RegNo="128" Name="Student 6" />

<row ID="1" Roll="16" RegNo="129" Name="Student 7" />

<row ID="1" Roll="17" RegNo="130" Name="Student 8" />

1)Parse XML 
  SQL server have also functionality to pass XML, SQL server can parse XML to and return a result set.You can insert ,update or delete your data accordingly with this result set. There are various technique to parse XML data, you can use "@XMLData.nodes" , this is the simple most technique.

DECLARE @XMLData XML

SET @XMLData ='

                 

                                   

                                             1

                                            10

                                           123

                                            Student 1

                                 

                                 

                                            1

                                            11

                                            124

                                            Student 2

                               

                                

                                             1

                                             12

                                             125

                                             Student 3

                             

                            

                                      1

                                      12

                                      126

                                      Student 4

                          

                         

                                      1

                                      14

                                      127

                                      Student 5

                      

                       

                                     1

                                     15

                                     128

                                     Student 6

                    

'

SELECT ID = Node.Data.value('(ID)[1]', 'INT'),

       Roll = Node.Data.value('(Roll)[1]', 'INT'),

       RegNo = Node.Data.value('(RegNo)[1]', 'INT'),

       Name = Node.Data.value('(Name)[1]', 'VARCHAR(500)')

FROM @XMLData.nodes('/STUDENT/STUDENT') Node(Data)

Output

ID        Roll       RegNo      Name

1    10    123    Student 1
1    11    124    Student 2
1    12    125    Student 3
1    12    126    Student 4
1    14    127    Student 5
1    15    128    Student 6

 

1)Parse XML Using OpenXML 

The Openxml function provides a rowset view over an XML document.The OPENXML function allows the data in a XML document to be treated just like the columns and rows of your database table.

DECLARE @XMLData XML

DECLARE @hdoc int;

SET @XMLData ='

           

                   1

                   10

                   123

                    Student 1

           

          

                    1

                   11

                 124

                 Student 2

        

       

                1

                12

                125

                Student 3

     

     

            1

            12

            126

            Student 4

    

    

          1

          14

          127

          Student 5

   

      

              1

             15

             128

              Student 6

    

'

EXEC sp_xml_preparedocument @hdoc OUTPUT, @XMLData;

SELECT * FROM OPENXML (@hdoc, '/STUDENT/STUDENT', 2) WITH

(

ID int,

Roll int,

RegNo int,

Name VARCHAR(50)

)

OUTPUT

ID        Roll       RegNo      Name

1    10    123    Student 1
1    11    124    Student 2
1    12    125    Student 3
1    12    126    Student 4
1    14    127    Student 5
1    15    128    Student 6

 

SQL Server Split function

          Sometimes we come across data with delimiter. Delimiter be anything , any specific symbol, any character a series of symbol to identify different data. It is obvious that we will process this data, before doing that  , you need to separate the data. 


          As this kind of situation may happen multiple places in a project, database developer need it to create a function related to this.
            The function will receive the total data, and the denominator, then the function will return tables after splitting the data. 

Here is the example how the function can be written and how to use this functions.

Example 1

CREATE FUNCTION dbo.fnSplit

(

    @InputData VARCHAR(8000)

, @Delimiter VARCHAR(8000)

)

RETURNS @List TABLE (element VARCHAR(8000))

BEGIN

       DECLARE @data VARCHAR(8000)

--

         WHILE CHARINDEX(@Delimiter,@InputData,0) <> 0

                      BEGIN

                         SELECT          @data=RTRIM(LTRIM(SUBSTRING(@InputData,1,CHARINDEX(@Delimiter,@InputData,0)-1))),

@InputData=RTRIM(LTRIM(SUBSTRING(@InputData,CHARINDEX(@Delimiter,@InputData,0)+LEN(@Delimiter),LEN(@InputData))))

                            IF LEN(@data) > 0

                                     INSERT INTO @List SELECT @data

          END

              IF LEN(@InputData) > 0

                               INSERT INTO @List SELECT @InputData -- Put the last item in

          RETURN

END

Query

 

select * from dbo.fnSplit('1*2*2*3*4','*')

Output

element

1
2
2
3
4

Example 2

DECLARE @myData VARCHAR(500)

DECLARE @Deliminator VARCHAR(500)

SELECT @myData='DATA1/**/DATA2/**/DATA3/**/DATA4/**/DATA5/**/DATA6/**/DATA7/**/DATA8/**/DATA9/**/DATA10/**/DATA11/**/'

SELECT @Deliminator='/**/'

Query

select element from dbo.fnSplit(@myData,@Deliminator)

Output 

element

DATA1
DATA2
DATA3
DATA4
DATA5
DATA6
DATA7
DATA8
DATA9
DATA10
DATA11

SQL Server Execution Plan

cccc               And execution plan is a technique to determine how to access data and complete the query , then represent it in a graphical format of statistics and process tree. 

                 There are many ways to execute query, SQL server choose the best beneficial way to reduce time and to improve performance of the query . 

                  The query plan describe the detail method data used by query optimizer. That way and SQL server developer can see the query execution plan and estimated time for the query.That help SQL server developer to assist query optimization because the execute query plan of stored in procedure cache,and if similar execution is done , the execution plan is retrieve and reuse. 


Below is the example of how query estimation can can be seen


1)Open an SQL server management Studio and select new query window.


2)Write your query on the new query window.

3)From Menu , select display estimated execution plan
 you will see the execution plan will be displayed as below


 

SQL Server Execution plan are two type

Actual Execution plan (Ctrl+M) : It is created after the execution of query, display the steps that was performed during the execution.

Estimated Execution plan (Ctrl+L): It is created without a executing the query , approximate execution plan is shown.