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