Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to convert XML data to SQL database table

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 900
    Comment on it

    How to convert XML data to SQL database table

     

    Step 1 : Create a table named "tblStudent" in database by following query in which xml data is to be inserted

     

    CREATE TABLE tblStudent
    (
      StudentId INT,
      StudentName VARCHAR(100),
      Gender VARCHAR(10),
      Course VARCHAR(10)
    )

     

    Step 2 : Assuming xml data is in following format

     

    DECLARE @xml XML='<ROOT>
    <STUDENT Id="1" Name="Deepak" Gender="Male" Course="Btech"/>
    <STUDENT Id="2" Name="Anita" Gender="Female" Course="MCA"/>
    <STUDENT Id="3" Name="Suraj" Gender="Male" Course="HM"/>
    </ROOT>'

     

    Step 3 : The query to be fired to read xml and insert data into table "tblStudent" 

     

    INSERT INTO tblStudent(StudentId,StudentName,Gender,Course)
    SELECT col.value('@Id','INT'),col.value('@Name','VARCHAR(100)'),
    col.value('@Gender','VARCHAR(15)'),col.value('@Course','VARCHAR(10)')
    FROM @xml.nodes('/ROOT/STUDENT') tab(col)

     

    "tblStudent" data can be checked by executing following query

     

    select * from  tblStudent
    

     

    Output of above query

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: