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

Read the 2019 State of Database DevOps Report for the very latest insights

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

Read the 2019 State of Database DevOps Report for latest insights into DevOps adoption among SQL Server professionals, and the benefits and challenges of including the database in DevOps initiatives

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 }}