SQL Query to Convert XML to a Table
SQL Query to Convert XML to a Table
Check out this article wherein the author shows you how to convert XML to a table in an SQL Server.
Join the DZone community and get the full member experience.
Join For FreeRavenDB vs MongoDB: Which is Better? This White Paper compares the two leading NoSQL Document Databases on 9 features to find out which is the best solution for your next project.
Sometimes a developer might get a certain task to convert XML data to a table in SQL Server. In such scenarios, this post would be helpful to achieve the goal.
We have this sample XML File which we will be converting to a table:
<?xml version="1.0" encoding="utf-8" ?>
<STUDENTS>
<STUDENT>
<StudentID>1</StudentID>
<Name>John Smith</Name>
<Marks>200</Marks>
</STUDENT>
<STUDENT>
<StudentID>2</StudentID>
<Name>Mark Johnson</Name>
<Marks>300</Marks>
</STUDENT>
<STUDENT>
<StudentID>3</StudentID>
<Name>Nitin Tyagi</Name>
<Marks>400</Marks>
</STUDENT>
</STUDENTS>
Let us write SQL Query to convert the preceding XML to a table:
DECLARE @XMLData XML
SET @XMLData ='
<STUDENTS>
<STUDENT>
<StudentID>1</StudentID>
<Name>John Smith</Name>
<Marks>200</Marks>
</STUDENT>
<STUDENT>
<StudentID>2</StudentID>
<Name>Mark Johnson</Name>
<Marks>300</Marks>
</STUDENT>
<STUDENT>
<StudentID>3</StudentID>
<Name>Nitin Tyagi</Name>
<Marks>400</Marks>
</STUDENT>
</STUDENTS>'
SELECT StudentID = Node.Data.value('(StudentID)[1]', 'INT')
, [Name] = Node.Data.value('(Name)[1]', 'VARCHAR(MAX)')
, [Marks] = Node.Data.value('(Marks)[1]', 'INT')
FROM @XMLData.nodes('/STUDENTS/STUDENT') Node(Data)
Execute the preceding query and check the output:
Aggregations provide vital intelligence to the success of a business. Crush the challenge of providing real time aggregations for daily, weekly, and monthly totals without having to tie up your servers.
Like This Article? Read More From DZone
Published at DZone with permission of Nitin Tyagi . See the original article here.
Opinions expressed by DZone contributors are their own.
{{ parent.title || parent.header.title}}
{{ parent.tldr }}
{{ parent.linkDescription }}
{{ parent.urlSource.name }}