Server-side Office Integration -5: Inserting data from XML Document
We are close to the end of these series. The procedure taking the XML Document as an input parameter, simply look for the nodes in the 'Root/Data' path then using its aliases inserts its values into PersonAddress table. Finally returns the recordsets, just inserted, in its simplest form. Here, we could probably return data as XML document as well.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ImportPersonAddressXml](
@XmlDoc xml
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @CurrentDt datetime;
SET @CurrentDt = getDate();
BEGIN TRANSACTION
INSERT INTO Person.Address(AddressLine1, AddressLine2, City, StateProvinceID,
PostalCode, rowguid, ModifiedDate)
SELECT
P.e.value('@AddressLine1','nvarchar(60)'),
P.e.value('@AddressLine2','nvarchar(60)'),
P.e.value('@City','nvarchar(30)'),
P.e.value('@StateProvinceID','int'),
P.e.value('@PostalCode','nvarchar(15)'),
P.e.value('@RowGuid','uniqueIdentifier'),
@CurrentDt
FROM @XmlDoc.nodes('/Root/Data') P(e)
COMMIT TRANSACTION
SELECT * FROM Person.Address WHERE ModifiedDate = @CurrentDt
END
|