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>
after running the procedure :
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)
