SQL and XML

Hello Friends,

Today, we are discussing some of the XML options used in SQL. These methods will take XML  string as input and converts into tabular data. This tabular data, you may insert into the SQL table or you may use as per the requirement.

1. Option 1:
1. XML Sample:

1. <root>
   	<Employee>
   		<FirstName>Sachin</FirstName>
   		<LastName>Jain</LastName>
   		<City>Delhi</City>
   		<Country>India</Country>
   	</Employee>
   	<Employee>
   		<FirstName>Lokesh</FirstName>
   		<LastName>Kumar</LastName>
   		<City>Alwar</City>
   		<Country>India</Country>
   	</Employee>
   	<Employee>
   		<FirstName>Ram</FirstName>
   		<LastName>Vinay</LastName>
   		<City>Varanasi</City>
   		<Country>India</Country>
   	</Employee>
   </root>
   

2. SQL Script:

1. DECLARE @varXMLString XML = CAST('
   <root>
   	<Employee>
   		<FirstName>Sachin</FirstName>
   		<LastName>Jain</LastName>
   		<City>Delhi</City>
   		<Country>India</Country>
   	</Employee>
   	<Employee>
   		<FirstName>Lokesh</FirstName>
   		<LastName>Kumar</LastName>
   		<City>Alwar</City>
   		<Country>India</Country>
   	</Employee>
   	<Employee>
   		<FirstName>Ram</FirstName>
   		<LastName>Vinay</LastName>
   		<City>Varanasi</City>
   		<Country>India</Country>
   	</Employee>
   </root>' AS XML)
   
   
   SELECT  
   	Tbl.Col.value('FirstName[1]', 'NVARCHAR(MAX)') AS 'First Name',  
   	Tbl.Col.value('LastName[1]', 'NVARCHAR(MAX)') AS 'Last Name',  
   	Tbl.Col.value('City[1]', 'NVARCHAR(MAX)') AS 'City',
   	Tbl.Col.value('Country[1]', 'NVARCHAR(MAX)') AS 'Country'
   FROM @varXMLString.nodes('root/Employee') Tbl(Col)
   

3. Output:
1. 
2. Option 2:
1. XML Sample:

1. <Employee FirstName="Sachin" LastName="Jain" City="Delhi" Country="India" />
   <Employee FirstName="Lokesh" LastName="Kumar" City="Alwar" Country="India" />
   <Employee FirstName="Ram" LastName="Vinay" City="Varanasi" Country="India" />
   

2. SQL Script:

1. DECLARE @varXMLString XML = CAST('
   <Employee FirstName="Sachin" LastName="Jain" City="Delhi" Country="India" />
   <Employee FirstName="Lokesh" LastName="Kumar" City="Alwar" Country="India" />
   <Employee FirstName="Ram" LastName="Vinay" City="Varanasi" Country="India" />
   ' AS XML)
   
   SELECT  
          Tbl.Col.value('@FirstName', 'NVARCHAR(MAX)') AS 'First Name',
          Tbl.Col.value('@LastName', 'NVARCHAR(MAX)') AS 'Last Name',
          Tbl.Col.value('@City', 'NVARCHAR(MAX)') AS 'City',
          Tbl.Col.value('@Country', 'NVARCHAR(MAX)') AS 'Country'
   
   FROM   @varXMLString.nodes('Employee') Tbl(Col)
   

3. Output:
1. 
3. Option 3:
1. XML Sample:

1. <root>
   	<Employee>
   		<FirstName>Sachin</FirstName>
   		<LastName>Jain</LastName>
   		<City>Delhi</City>
   		<Country>India</Country>
   	</Employee>
   	<Employee>
   		<FirstName>Lokesh</FirstName>
   		<LastName>Kumar</LastName>
   		<City>Alwar</City>
   		<Country>India</Country>
   	</Employee>
   	<Employee>
   		<FirstName>Ram</FirstName>
   		<LastName>Vinay</LastName>
   		<City>Varanasi</City>
   		<Country>India</Country>
   	</Employee>
   </root>
   

2. SQL Script:

1. DECLARE @varXMLString XML = 
   '<root>
   	<Employee>
   		<FirstName>Sachin</FirstName>
   		<LastName>Jain</LastName>
   		<City>Delhi</City>
   		<Country>India</Country>
   	</Employee>
   	<Employee>
   		<FirstName>Lokesh</FirstName>
   		<LastName>Kumar</LastName>
   		<City>Alwar</City>
   		<Country>India</Country>
   	</Employee>
   	<Employee>
   		<FirstName>Ram</FirstName>
   		<LastName>Vinay</LastName>
   		<City>Varanasi</City>
   		<Country>India</Country>
   	</Employee>
   </root>'
   
   DECLARE @handle INT  
   
   EXEC sp_xml_preparedocument @handle OUTPUT, @varXMLString  
   
   SELECT FirstName AS 'First Name', LastName AS 'Last Name', City, Country
   FROM OPENXML(@handle, '/root/Employee', 2)  
   WITH (
   	FirstName NVARCHAR(MAX),
   	LastName NVARCHAR(MAX),
   	City NVARCHAR(MAX),
   	Country NVARCHAR(MAX)
   )
   

3. Output:
1. 
4. Option 4:
1. XML Sample:

1. <root>
   	<FirstName>Sachin</FirstName>
   	<FirstName>Lokesh</FirstName>
   	<FirstName>Ram</FirstName>
   </root>
   

2. SQL Script:

1. DECLARE @varXMLString XML = 
   '<root>
   	<FirstName>Sachin</FirstName>
   	<FirstName>Lokesh</FirstName>
   	<FirstName>Ram</FirstName>
   </root>'
   
   SELECT x.y.value('.','NVARCHAR(MAX)') AS 'First Name'
   FROM @varXMLString.nodes('/root/FirstName') AS x(y)
   

3. Output:
1. 
5. Option 5:
1. XML Sample:

1. <root>
   	<Employee FirstName="Sachin" />
   	<Employee FirstName="Lokesh" />
   	<Employee FirstName="Ram" />
   </root>
   

2. SQL Script:

1. DECLARE @varXMLString XML = 
   '<root>
   	<Employee FirstName="Sachin" />
   	<Employee FirstName="Lokesh" />
   	<Employee FirstName="Ram" />
   </root>'
   
   SELECT x.y.value('@FirstName','NVARCHAR(MAX)') AS 'First Name'
   FROM @varXMLString.nodes('/root/Employee') AS x(y)
   

3. Output:
1. 
6. Option 6:
1. XML Sample:

1. <root>
   	<Employee FirstName="Sachin" />
   	<Employee FirstName="Lokesh" />
   	<Employee FirstName="Ram" />
   </root>
   

2. SQL Script:

1. DECLARE @varXMLString XML = 
   '<root>
   	<Employee FirstName="Sachin" />
   	<Employee FirstName="Lokesh" />
   	<Employee FirstName="Ram" />
   </root>'
   
   SELECT x.y.value('.','NVARCHAR(MAX)') AS 'First Name'
   FROM @varXMLString.nodes('/root/Employee/@FirstName') AS x(y)

3. Output:
1. 

With this, I am concluding this post.

Happy Coding !!!

Will see you again with some new topics.

Stay Safe !

Stay Healthy !