Welcome to weblogs.com.pk Sign in | Join | Help

Nasir Ali Khan

Believing is easier than thinking. Hence so many more believers than thinkers.
An introduction to SQL Server’s OPENXML
Intended Readers: Persons having expertise in SQL Server, T-SQL, stored procedures and XML
Level: Expert
 
Introduction
OpenXML is basically a function of SQL Server which allows XML document to be viewed as rowset without any involvement of high level programming code. This rowset can be used in SQL statements such as Insert, Delete … etc.
 
Declaration of OPENXML is
 
OPENXML (idoc, int [in], rowPattern nvarchar [in], [flags byte [in]])
                        [WITH (SchemaDeclration | TableName)]
 
  • idoc: a document handle to be parsed as XML. This handle is created by calling sp_xml_preparedocument stored procedure.
  • rowPattern: as XPath pattern specifying the node of XML document to be processed as a rowset. i.e. N ’/Top/Region’ referes to Region node to process.
  • Flag: indicates how XML node is to be interpreted. 1 indicates that attributes in the document becomes columns etc.
Examples
Following T-SQL code inserts two records NorthWind.Region table using OPENXML function
 
Declare @xmlDoc nvarchar(4000)
Declare @docIndex int
 
-- Text to be parsed as XML
set @xmlDoc = N'
<Top>
          <Region RegionID="11" RegionDescription="Uptown" />
          <Region RegionID="22" RegionDescription="Downtown" />          
</Top>'
 
-- Prepare XML document in memory
execute sp_xml_preparedocument @docIndex OUTPUT, @xmlDoc
 
Insert Region
Select RegionID, RegionDescription
            FROM OPENXML(@docIndex, N'/Top/Region', 1) With Region
 
-- Release XML document from memory
execute sp_xml_removedocument @docIndex
 
Let’s look at another example but this time for deleting the record from NorthWind.Region Table
 
Declare @xmlDoc nvarchar(4000)
Declare @docIndex int
 
set @xmlDoc = N'
<Top>
          <Region RegionID="11" RegionDescription="Uptown" />
</Top>'
 
execute sp_xml_preparedocument @docIndex OUTPUT, @xmlDoc
 
Delete Region
FROM OPENXML(@docIndex, N'/Top/Region', 1) With Region as XMLRegion
Where Region.RegionID = XMLRegion.RegionID
 
execute sp_xml_removedocument @docIndex
Following T-SQL code is for updating the record with regionID=22 with the new Description value
 
Declare @xmlDoc nvarchar(4000)
Declare @docIndex int
 
set @xmlDoc = N'
<Top>
          <Region RegionID="22" RegionDescription="NewRegionValue" />
</Top>'
 
execute sp_xml_preparedocument @docIndex OUTPUT, @xmlDoc
 
Update Region
Set Region.RegionDescription = XMLRegion.RegionDescription
FROM OPENXML(@docIndex, N'/Top/Region', 1) With Region as XMLRegion
Where Region.RegionID = XMLRegion.RegionID
 
execute sp_xml_removedocument @docIndex
 
Benefits of OPENXML
  • Rich support for XML processing.
  • No need for object to database mapping, since objects can be serialized in XML and can directly manipulate by the Database.
  • Runs in database process, results improved performance
  • Use of XML removes the language dependency
Reference: Professional ADO.NET, Wrox Press, ISBN (1-861007-33-7)
 
Your comments and feedbacks are always welcome
 
Posted: Wednesday, May 25, 2005 1:04 AM by nasir

Comments

khurram said:

Nice stuff...
Thanks for sharing it.
# May 28, 2005 8:33 PM
Anonymous comments are disabled