Over a million developers have joined DZone.

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

Sign up for the Couchbase Community Newsletter to stay ahead of the curve on the latest NoSQL news, events, and webinars. Brought to you in partnership with Coucbase.

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

The Getting Started with NoSQL Guide will get you hands-on with NoSQL in minutes with no coding needed. Brought to you in partnership with Couchbase.

Topics:
sql server 2014 ,xml ,sql ,table

Published at DZone with permission of Nitin Tyagi. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}