Importing XML File to Sql Server Table

 Here is the example on how to import an xml file in the sql server installed machine to sql server table. Steps to do this 
1. Save the xml file to a location on your drive eg.C:\xmlfiles\Students.xml

2. Create Table Students

3. Import the xml using Openrowset bulk option  and select the values in xml using xpath and xquery
    and Do a Insert into using select
4. Results
STEP 1
The contents of the saved file will be
<Students>

  <Student>

    <Id>1</Id>

    <Name>Madhan</Name>

  </Student>

  <Student>

    <Id>2</Id>

    <Name>Karthi</Name>

  </Student>

  <Student>

    <Id>3</Id>

    <Name>Bata</Name>

  </Student>

</Students>

STEP 2

The syntax to create a table

CREATE TABLE TblStudents

(

 Id INT PRIMARY KEY,

 Name VARCHAR(50)

);
STEP 3

Query to select the values from xml and to extract

INSERT INTO TblStudents (Id , Name )

SELECT students.student.query('Id').value('.', 'INT') as Id,

       students.student.query('Name').value('.', 'VARCHAR(30)') as Name

FROM (

SELECT CAST(b AS XML)

FROM OPENROWSET(

     BULK 'C:\xmlfiles\Students.xml',

     SINGLE_BLOB) AS a(b)

     ) AS a(b)

CROSS APPLY b.nodes('Students/Student') AS students(student);

STEP 4

SELECT Id, Name FROM TblStudents;

/*

Results:

Id        Name

----------- -------------

1           Madhan

2           Karthi

3           Bata

*/

xml to sql

Example1 on how to read xml in sql server

declare @xmlfullstring xml

set @xmlfullstring= '<root>

<name>madhan</name>

<name>kumar</name>

</root>'

select xfs.name.value('name[1]','varchar(50)') as namexml,

xfs.name.value('name[2]','varchar(50)') as namexml

from @XmlFullString.nodes('root')xfs(name)

Example2 on how to read xml in sql server

DECLARE @Details xml

    SET @Details = '<root>

    <SelectedCategory CategoryId="101">

        <ItemID>120</ItemID>

        <ItemID>256</ItemID>

      </SelectedCategory>

      <SelectedCategory CategoryId="125">

        <ItemID>158</ItemID>

        <ItemID>120</ItemID>

    </SelectedCategory>

    <SelectedCategory CategoryId="15">

            <ItemID>5986</ItemID>

            <ItemID>20</ItemID>

        <ItemID>268</ItemID>

      </SelectedCategory>

    </root>'

    SELECT  T.[CategoryID],

            T2.Loc.query('.').value('.', 'INT') Val

    FROM

        ( SELECT SelCat.CatDet.value('(@CategoryId)[1]', 'int') as CategoryID,

                 SelCat.CatDet.query('.') as ItemID

          FROM @Details.nodes('/root/SelectedCategory') as SelCat(CatDet)

         ) T  CROSS APPLY ItemID.nodes('/SelectedCategory/ItemID') as T2(Loc)

Example3 on how to read xml in sql server

declare @studentxml xml ='

<Students>

<Student>

<Id>1</Id>

<Name>Madhan</Name>

</Student>

      <Student>

<Id>2</Id>

<Name>Karthi</Name>

</Student>

      <Student>

<Id>3</Id>

<Name>Bata</Name>

</Student>

</Students>'

SELECT X.Student.query('Id').value('.', 'INT') as studentid,

       X.Student.query('Name').value('.', 'VARCHAR(50)') as studentname

FROM @studentxml.nodes('/') as a(b)

CROSS APPLY b.nodes('Students/Student') AS X(Student);