Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

SQL Query to Convert XML to a Table

DZone's Guide to

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.

· Database Zone ·
Free Resource

MariaDB 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:

SQL Query to convert XML to table

MariaDB AX is an open source database for modern analytics: distributed, columnar and easy to use.

Topics:
sql server 2014 ,xml ,sql ,table

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}