How to OPEN & READ xml document in SQL 2008 R2:

In this post we are going to look into open and read an XMLdocument in SQL. In sql, we have some predefined stored procedures which hepls to prepare and remove XML files those are:

sp_xml_preparedocument,
sp_xml_removedocument

And OPENXML command executes XML handler to read and display the content. Find the below script to read and insert the XML data into a table.

CREATE  PROCEDURE [dbo].[xmlProducts]   (  @XmlOverView  NVARCHAR(MAX)  = null  )  AS    BEGIN      /***********************************************************************************    ** Desc                                      : SP to save Products details with XML documents ** Author          : Ramakrishna K ** Date                                      : 06/08/2012 ***********************************************************************************/         /***************************************    * Standard SET statements.    ***************************************/    SET NOCOUNT ON    SET ANSI_PADDING ON    SET ANSI_NULLS ON    SET ANSI_WARNINGS ON    SET CONCAT_NULL_YIELDS_NULL ON    SET QUOTED_IDENTIFIER OFF           /***************************************    * Genric variables declaration ***************************************/    DECLARE     @Msg  VARCHAR(1000),   -- To display message text     @Error  INT           -- Captuer errors                SELECT     @Error = 0,      @Msg = ''      /***************************************  *    SP declared variables  ***************************************/  DECLARE  @TransactionExists BIT,  @PID   INT, @PName NVARCHAR(100), @iDoc    INT                         BEGIN TRY                                                                                   --The number of BEGIN TRANSACTION statements that have occurred on the current connection.                                                  IF @@TRANCOUNT > 0                                                                            SET @TransactionExists = 1                                                     ELSE                                                                            SET @TransactionExists = 0                                                                                  IF @TransactionExists = 0                                                     BEGIN                                                                              --Initialise transaction                                                                                 BEGIN TRANSACTION                                                     END                                                                               ---------------Section to save the product details into a table--------------------------                                                                             IF(@XmlOverView != NULL OR @XmlOverView != '')                                                  BEGIN                                                                                EXECUTE sp_xml_preparedocument @iDoc OUTPUT, @XmlOverView                                                                                                                          INSERT INTO Production.xmlTest                                                                                                                                                 (PID                                                                                                                                                                         ,PName                                                                                                                         )                                                                                                                                                                                                                                                   SELECT * FROM OPENXML(@iDoc, '/NewDataSet/Table',2)                                                                                                                                                                          WITH (ProductID  INT                                                                                                                                                                                                                          ,Name  NVARCHAR(500)                                                                                                                                                                          )                                                                                                                             EXECUTE sp_xml_removedocument @iDoc                                                                             IF @TransactionExists = 0                                                                           BEGIN                                                                                                     COMMIT TRANSACTION                                                                                                     RETURN (0)                                                                           END                                                 END                         END TRY                           BEGIN CATCH                                                 IF @TransactionExists = 0                               ROLLBACK TRANSACTION                                                     SET @msg = Error_Message()                            RAISERROR (@msg,16,1) WITH NOWAIT                                                         IF @Error = 0                               SET @Error = 1                              RETURN (@Error)                         END CATCH     END