Handling Simple XML Using T-SQL

This article describes how to traverse a simple XML file that has only 2 attributes (name and value, like a dictionary). And get the results in a single column in SQL SERVER result set

When dealing with interfaces to other applications that are not SQL SERVER based or even not even RDBMS based, the best (and simplest) media for communicating is the XML file.

When the data required can be arranged in a dictionary manner (name and value), the remote application can make an XML file in the following form at :

<ROOT>
<LINE>
        <NAME>NAME1</NAME>
        <VALUE>VALUE1</VALUE>
</LINE>
<LINE>
        <NAME>NAME2</NAME>
        <VALUE>VALUE2</VALUE>
</LINE>
.
.
.
.
.
</ROOT>

I wrote a procedure that gets the XML file full path and name and traverses the XML file and the output is a single column result set where the odd rows are the "name" tags value and the even rows are the "value" tags value.

I used the BULK INSERT t-sql Statement combined with dynamic sql execution in order to "bulk copy" the XML file Inside SQL Server. This is the code implementation of the XML traversing  process :

create  proc sp_translate_simple_xml
 (@XMLfile varchar(100))

as

begin

declare @idoc int
declare @doc varchar(8000)
declare @xml_line varchar(200)
declare @bulkinscmd varchar(500)

set @doc =''

-- insert XML file into temporary table

Create table #tempXML
  (line varchar(8000))

set @bulkinscmd = 'BULK INSERT #tempXML FROM ' +
        '''' + @XMLfile + ''''

exec (@bulkinscmd) 

DECLARE xml_cursor CURSOR
   FOR SELECT * FROM #tempXML

-- create XML string in SQL SERVER memory
OPEN xml_cursor 
FETCH NEXT FROM xml_cursor INTO @xml_line
WHILE @@FETCH_STATUS = 0
 BEGIN
   SET @doc = @doc + rtrim(ltrim(@xml_line))
   FETCH NEXT FROM xml_cursor INTO @xml_line
 END 

close xml_cursor

deallocate xml_cursor

drop table #tempXML

--Create an internal representation of the XML document.
exec sp_xml_preparedocument @idoc OUTPUT, @doc

-- SELECT statement using OPENXML rowset provider
SELECT  text 
 FROM  OPENXML (@idoc, '/ROOT/LINE')
 WHERE text is not null

EXEC sp_xml_removedocument @idoc
END 
Go

Example of a call to the procedure. Suppose an XML called dict.xml is in C:\sql. That looks like this:

<ROOT>
<LINE>
       <NAME>SUBJECT</NAME>
       <VALUE>ELI SECTION</VALUE>
</LINE>
<LINE>
       <NAME>THE PLANT</NAME>
       <VALUE>IEC factory</VALUE>
</LINE>
<LINE>
       <NAME>CREATE DATE</NAME>
       <VALUE>01/07/2004</VALUE>
</LINE>
</ROOT>

use master 
go

exec sp_translate_simple_xml 'c:\sql\dict.xml'

this will traverse the dict.xml file and produce a single column result set with name in odd row numbers and values in even row numbers. We will get the following results (using this file - dict.xml):

text
--------------------------
SUBJECT
ELI SECTION
THE PLANT
IEC factory
CREATE DATE
01/07/2004

(6 row(s) affected)

Conclusion

The procedure can be used as an ideal tool for communicating with applications that cannot link directly to SQL server , must use an intermediate media and a little amount of data is required for communication.

Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. (e-mail: iecdba@hotmail.com)