View XML nodes in SQL Server as separate column

Hi, today i was asked by someone to view Xml file nodes in SQL Server as separated columns to check the consistency of data available in nodes.

I already knew that because i have done those kinds of stuff in beginning of my developer carrier.

Below Query will give you exact out but its little bit judicious because you have to give nodes name manually. I am working out to get the simple and easy solution when i will achieve my goal i will update this blog.

Demo xml data='<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>

<Root source="C:\Data.csv" datetime="2015-05-12T16:16:55+06:00" >

     <record num="1">

          <ID>131383</ID>

          <EmpNum>51023</EmpNum>

          <Login>Rty</Login>

          <First_name>Amit</First_name>        

     </record>

    '

SELECT @XmlFile = BulkColumn

FROM  OPENROWSET(BULK 'path of xml', SINGLE_BLOB) x;

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XmlFile

SELECT ID, EmpNum, [Login],First_name

FROM OPENXML(@hDoc, 'Root/record')

WITH

(

ID varchar(50) '(ID)[1]',

EmpNum varchar(50) '(EmpNum)[1]',

[Login] varchar(100) '(Login)[1]',

First_name varchar(100) '(First_name)[1]'

)

EXEC sp_xml_removedocument @hDoc

GO