Insert records from XML to SQL Server database table in SQL Server

How to insert records from XML to SQL Server database table?

To insert records from XML data into SQL Server database table, we use XML data type as input parameter.

Create a stored procedure like below with Xml as input parameter type.

CREATE PROCEDURE InsertPersonalDetailsFromXML
       -- Add the parameters for the stored procedure here
       @xmlData XML
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

    -- Insert statements for procedure here

       INSERT INTO PersonalDetails
       (FirstName, LastName, Age, Active)

       SELECT x.value('FirstName[1]', 'varchar(50)') AS FirstName,
           x.value('LastName[1]', 'varchar(50)') AS LastName,
                 x.value('Age[1]', 'int') AS Age,
                 x.value('Active[1]', 'bit') AS Active
       FROM @xmlData.nodes('//PersonalDetail') XmlData(x)
END

Notice that the input parameter named @xmlData is of XML type. Next, we are using INSERT statement and selecting the value of each node of “PersonalDetail” to insert into PersonalDetails table.

To execute above stored procedure, call following script

DECLARE @xmlData Xml
SET @xmlData = '<PersonalDetails>
  <PersonalDetail>
    <FirstName>XmlFirstName 11</FirstName>
    <LastName>XmlLastName 1</LastName>
    <Age>30</Age>
    <Active>1</Active>
  </PersonalDetail>
  <PersonalDetail>
    <FirstName>XmlFirstName 22</FirstName>
    <LastName>XmlLastName 2</LastName>
    <Age>25</Age>
    <Active>1</Active>
  </PersonalDetail>
</PersonalDetails>'

EXEC InsertPersonalDetailsFromXML @xmlData

Note that the @xmlData is of Xml type, here even if we change the variable type to varchar it will work as its string value is a valid Xml.

Above code snippet inserts two records into PersonalDetails database table.