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 :
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)
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: email@example.com)