CODEWITHSUNDEEP
sqlsql-server
Amit
Amit

Reputation: 167

XML Parsing in Stored procedure

I have xml as shown below 

<StudentData>
  <Student ID ="1" RollNo ="15" />
</StudentData>

One of my stored procedure accepts this xml as input as shown below

CREATE PROCEDURE [dbo].[FetchStudentData]
@xml XML    
AS
BEGIN
      SET NOCOUNT ON;     

      SELECT
      st.value('@ID','INT') AS Id, 
      st.value('@RollNo','INT') AS RollNo 
      @xml.nodes('/StudentData/Student')AS TEMPTABLE(st)

END

Here I want to save the Id and RollNo in some variable so that I can use them in further queries with in stored procedure. I don't know exact syntax to fetch the Id and RollNo from node and store in variable.

Can anybody suggest me the way to do it?

Upvotes: 0

Views: 498

Answers (3)

Saravana Kumar
Saravana Kumar

Reputation: 3729

This query will be helpful.

CREATE PROCEDURE [dbo].[FetchStudentData]
@xml XML    
AS
BEGIN
      SET NOCOUNT ON;    

      DECLARE @sID AS INT, @sRollNo AS INT 

       SELECT  @sID = xmlData.Student.value('@ID','INT'),  
            @sRollNo = xmlData.Student.value('@RollNo','INT')
    FROM @xml.nodes('//StudentData/Student') xmlData(Student)

   SELECT @sID AS ID, @sRollNo AS RollNo
END

Oupput:

enter image description here

Nore: For multiple students tags

CREATE PROCEDURE [dbo].[Test1]
    @xml XML    
    AS
    BEGIN
          SET NOCOUNT ON;    

          DECLARE @StudentTbl as TABLE
          (
            ID int,
            RollNo int
          )

        INSERT INTO @StudentTbl
        SELECT xmlData.Student.value('@ID','INT'),  
                xmlData.Student.value('@RollNo','INT')
        FROM @xml.nodes('//StudentData/Student') xmlData(Student)

       SELECT * FROM @StudentTbl
    END

Upvotes: 1

Aaron
Aaron

Reputation: 161

Here is another example of putting them into variables.

  Declare @ID int
  DECLARE @RollNo int

  SELECT
  @ID = st.value('@ID','INT'), 
  @RollNo = st.value('@RollNo','INT') 
  From
  @xml.nodes('/StudentData/Student')AS TEMPTABLE(st)

SELECT @ID AS ID, @RollNo AS  RollNo

Upvotes: 0

twrowsell
twrowsell

Reputation: 467

You can assign them to variables using the value function for the xml type and nvaigate to the attribute directly in xpath...

DECLARE @xml AS XML

SET @xml = '<StudentData>
              <Student ID ="1" RollNo ="15" />
            </StudentData>'

DECLARE @rollNo int , @ID int 

SET @rollNo = @xml.value('/StudentData[1]/Student[1]/@RollNo','int')
SET @ID =  @xml.value('/StudentData[1]/Student[1]/@ID','int')

SELECT @rollNo ,@ID

Upvotes: 0

Related Questions