Loading XML data into a table in SQL Server

Using XQuery in SQL Server we can Convert the XML Data into Table format.

The below Example shows how to convert the XML into relational table format using Xquery method.

Value() method is used to fetch the values of SQL Data Type. 

It accepts two parameters one is attribute name and second parameter is data type.

Nodes() method is used  to represent the xml data in XML node format. I will be used for shred the XML into Relational table.

Example :

DECLARE @Persondetails XML ;

SET @Persondetails = '
                                       
                                               mamidi                                               malls
                                              

                                  

                                  
                                               Reddy                                               Mamidi
                                              

                                  
                                  
                                               Naga                                               Eswara
                                              

                                  
                                  '

 The below query will Convert the above XML into Relational Database

       select  t.c.value('Surname[1]','VARCHAR(50)') As Surname,                      t.c.value('Givename[1]','VARCHAR(50)') As GivenName,                     t.c.value('./Address[1]/@Builiding','VARCHAR(50)') AS Building,                     t.c.value('./Address[1]/@Street','VARCHAR(50)') AS Street,                     t.c.value('./Address[1]/@State','VARCHAR(50)') AS State       From @Persondetails.nodes('/PersonDetails/Person') as T(c)

The Output of the query as
 

Surname	GivenName	Building	Street	State
mamidi	malls	131	Lonsdale street	VIC
Reddy	Mamidi	150	Queen street	VIC
Naga	Eswara	130	Elizabeth street	VIC