sql - XML Parsing in Stored procedure -


i have xml shown below

<studentdata>   <student id ="1" rollno ="15" /> </studentdata> 

one of stored procedure accepts xml input shown below

create procedure [dbo].[fetchstudentdata] @xml xml     begin       set nocount on;             select       st.value('@id','int') id,        st.value('@rollno','int') rollno        @xml.nodes('/studentdata/student')as temptable(st)  end 

here want save id , rollno in variable can use them in further queries in stored procedure. don't know exact syntax fetch id , rollno node , store in variable.

can suggest me way it?

this query helpful.

create procedure [dbo].[fetchstudentdata] @xml xml     begin       set nocount on;            declare @sid int, @srollno int          select  @sid = xmldata.student.value('@id','int'),               @srollno = xmldata.student.value('@rollno','int')     @xml.nodes('//studentdata/student') xmldata(student)     select @sid id, @srollno rollno end 

oupput:

enter image description here

nore: multiple students tags

create procedure [dbo].[test1]     @xml xml             begin           set nocount on;                declare @studenttbl table           (             id int,             rollno int           )          insert @studenttbl         select xmldata.student.value('@id','int'),                   xmldata.student.value('@rollno','int')         @xml.nodes('//studentdata/student') xmldata(student)         select * @studenttbl     end 

Comments

Popular posts from this blog

matlab - "Contour not rendered for non-finite ZData" -

delphi - Indy UDP Read Contents of Adata -

javascript - Any ideas when Firefox is likely to implement lengthAdjust and textLength? -