SQL Server > Working with XML

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
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.

    -- 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)

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>
    <FirstName>XmlFirstName 11</FirstName>
    <LastName>XmlLastName 1</LastName>
    <FirstName>XmlFirstName 22</FirstName>
    <LastName>XmlLastName 2</LastName>

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.

 Views: 2510 | Post Order: 109

Write for us