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 FreeMariaDB TX, proven in production and driven by the community, is a complete database solution for any and every enterprise — a modern database for modern applications.
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:
MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.
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 }}