guyS's WebLog

Using OpenXml within SQL Server enable u to query XML text string using XPath. This could be handy when your front end classes can supply their inner state as Xml and u want to move this state to the DB for data updates. This way u can minimize your DAL size by moving DAL responsibility to your DB. This could be another alternative for CRUD (Create, Retrieve, Update, Delete) operations.

The data within the DB will be save in rational tables format and not as Xml NText string in order to get a better and easy access to it.

I've just show a short article by Robbe D. Morris that can be use as a starting point for implementing CRUD using OpenXml. The article also show how to gather the data from the client using XmlSerialization. I like it!!

Here u can see the store procedure from the mentioned article:

CREATE PROCEDURE dbo.SaveDataInBulk
(
    @PresidentXML ntext,
    @FirstLadyXML ntext
)
AS

  declare @XmlHandle int
  declare @rc int

  select @rc=0

  begin transaction  SavePresidents

/*
    Use the OPENXML method to grab all President XmlNodes and
     map its child nodes to variables for use in our SQL.  I like to
     use the prefix "x" for these variables and name them the same
     as the property name from the .NET class.  This sample will
     update all of the presidents passed in by using the PresidentID
     as the unique key.  The update statement below this handles
     all of the first ladies.
*/

  EXEC sp_xml_preparedocument @XmlHandle output,@PresidentXML

   UPDATE President
      Set FirstName = xFirstName,
             LastName = xLastName
      FROM OPENXML (@XmlHandle, '/root/President',1)
      WITH (xPresidentID int './PresidentID',
                  xFirstName nvarchar(100) './FirstName',
                  xLastName nvarchar(100) './LastName')
    WHERE PresidentID = xPresidentID

      if (@@ERROR <> 0)
       BEGIN
         select @rc = @@Error  
         EXEC sp_xml_removedocument @XmlHandle
         Goto OnExit
       END             

  EXEC sp_xml_removedocument @XmlHandle

  EXEC sp_xml_preparedocument @XmlHandle output,@FirstLadyXML

   UPDATE FirstLady
      Set FirstName = xFirstName,
             LastName = xLastName,
             PresidentID = xPresidentID
      FROM OPENXML (@XmlHandle, '/root/FirstLady',1)
      WITH (xFirstLadyID int './FirstLadyID',
                  xPresidentID int './PresidentID',
                  xFirstName nvarchar(100) './FirstName',
                  xLastName nvarchar(100) './LastName')
    WHERE FirstLadyID = xFirstLadyID

      if (@@ERROR <> 0)
       BEGIN
         select @rc = @@Error  
         EXEC sp_xml_removedocument @XmlHandle
         Goto OnExit
       END             

  EXEC sp_xml_removedocument @XmlHandle

 

OnExit:


     if (@rc = 0)
    BEGIN
       COMMIT TRANSACTION   SavePresidents
    END
   ELSE
    BEGIN
         ROLLBACK TRANSACTION  SavePresidents
    END

    RETURN @rc
GO

Do u know how this procedure logic can be done using Oracle PL/SQL??